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