Using Idera to capture data for the DTU Calculator (estimates for moving to Azure)

by Oct 18, 2017

Microsoft has a tool that they recommend you run on your servers to capture some metrics about your workload. There is a powershell script or an executable that can be ran. These metrics are captured and then get written to a csv file. After you have run the tool long enough to capture a normal workload you can take the csv file and upload it to the DTU calculator (http://dtucalculator.azurewebsites.net)  to get a good estimate on what tier you would need to use in Azure. This gives you a strong starting point to work with to analyze how much performance you need and the cost.

So I dug into what there tool was doing to see if I could recreate it and integrate into Idera SQLdm. This keeps us from having to manageschedule another tool.

Basically, there tool is capturing data from the following perfmon counters –

Processor —  % Processor Time —  _Total

Logical Disk —  Disk Reads / sec — _Total

Logical Disk — Disk Writes / sec — _Total

SQL Server:Databases — Log Bytes Flushed/sec — _Total

I tried looking through SQLdm to see if there were places where these metrics where already being captured but was unable to determine if they were. They may be I just was not able to find it. Since you can create custom counters for perfmon counters, I went ahead and created them so I knew I had the same thing.

 

After the counters were created, be sure to apply them to the servers you want to start capturing data for.

I looked through the SQLdmRepository database and found the tables that were storing the information. I wrote code that put the results into the same format that the DTU calculator was looking for. The code is listed below.

After getting it into the right format, I just wrote a really simple extraction in SSIS to just extract the data to a csv. I had issues doing the extraction directly from SMS when I tried to upload the CSV to the DTU calculator (maybe an issues with the code page). But I did not have any issues doing it in SSIS. Several other ways to do this as well, this is just what I did

Once I got the csv file, I was able to upload it to the DTU calculator (http://dtucalculator.azurewebsites.net) and analyze the results. Good thing with using this over the Microsoft approach is that SQLdm runs 24×7 so I can easily analyze any workload from any point in time for any SQL server in our environment. 

One difference to note is that Microsoft's tool pulls data every 1 second, whereas, the custom counters will pull based on the time settings you have configured. I currently have mine set to 1 minute, but you can change this if you want more. 

 

 A couple of notes on the script. 

1. You will need to find the MetricID's for your custom counters. These are found in the CustomCounterDefinition table in the SQLdm database

2. I created 3 parameters (@BeginTime, @EndTime and @InstanceName) created although I have them listed as variables in the script posted below

3. For the Datetime parameters, I set them to pass through local time and then in the script convert them to UTC. Converted them to UTC since that is what the Microsoft solution uses

 

Here is that SQL Script that puts the data into the correct format

— Define variables (I use these as parameters in the process I am using)

DECLARE @InstanceName nvarchar(256)

DECLARE @BeginTime Datetime

DECLARE @EndTime Datetime

SET @InstanceName = 'ServerA'

SET @BeginTime = '2017-10-13 05:47:46.250' — In local time

SET @EndTime = '2017-10-13 09:54:46.250' — In Local time

 

— Convert local time to UTC time

 

DECLARE @BeginUTCTime Datetime

DECLARE @EndUTCTime Datetime

SET @BeginUTCTime = DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), @BeginTime)

SET @EndUTCTime = DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), @EndTime)

 

— Pull information from SQLdmRepository and put it in the correct format for the DTU calculator

 

SELECT CONVERT(VARCHAR(10),UTCCollectionDateTime,101) + ' ' + LTRIM(RIGHT(CONVERT(CHAR(20), UTCCollectionDateTime, 22), 11)) as 'Interval',

[PercentProcessorTime] as '% Processor Time',

[DiskReadsPersec] as 'Disk Reads/sec',

[DiskWritesPersec] as 'Disk Writes/sec',

[LogBytesFlushedPersec] as 'Log Bytes Flushed/sec'

FROM (

SELECT UTCCollectionDateTime , [DiskReadsPersec], [DiskWritesPersec],[LogBytesFlushedPersec],[PercentProcessorTime]

FROM ( Select UTCCollectionDateTime

         ,CCD.Counter

      ,CCS.RawValue

  FROM [SQLdmRepository].[dbo].[CustomCounterStatistics] CCS

  INNER JOIN dbo.MonitoredSQLServers MS on MS.SQLServerID = CCS.SQLServerID

  INNER JOIN dbo.CustomCounterDefinition CCD ON CCD.Metric = CCS.MetricID

  Where MetricID in (1001,1002,1003,1004)

  and MS.InstanceName = @InstanceName

  and UTCCollectionDateTime between @BeginUTCTime and @EndUTCTime

) D

PIVOT (MAX(RawValue) FOR [Counter] in (DiskReadsPersec,DiskWritesPersec,LogBytesFlushedPersec,PercentProcessorTime)) AS pvt

) A

Order by UTCCollectionDateTime desc