Hello everyone!  I recently had to do an analysis of a client’s database workload using the Azure DTU Calculator(DTU Calculator) and thought it might be interesting to share just how I did that.  I have run this tool numerous times on other clients via the PowerShell method and the Command Line method, however this client’s environment was: Windows Server 2008R2, and SQL Server 2008R2 SP3 and had to be done differently.

Now, from the DTU Calculator page itself, it tells you how the process works.  It essentially runs a perfmon trace for an hour with the following counters:

• Processor – % Processor Time
• Logical Disk – Disk Reads/sec
• Logical Disk – Disk Writes/sec
• Database – Log Bytes Flushed/sec

My client did not have PowerShell accessible for me to use unfortunately.  I normally prefer the PowerShell script, however in this case I had to use the Command Line Interface, they both return the same results. So, I downloaded the utility, and extracted its contents to a directory off of the C:\ drive.  I knew, from doing this before,  I would need to change the directory to where the results would be recorded, so I opened the “SqlDtuPerfmon.exe.config” with Notepad and found the variable “CsvPath”.  I updated that to the location where I wanted the file to be saved, then saved the file.  I ran the SqlDtuPerfmon.exe as an Administrator, and was shocked when I got the following error: “SQLServer:Databases doesn’t exist. Try running perfmon.exe to identify the correct SQLServer:Databases category.”

So, I did exactly what the error said, I clicked start, and did a search for perfmon.  Once I located the exe, I right-clicked and ran as an Administrator.  I clicked on Performance Monitor, then clicked the green + button which loads the “Add Counters” snap-in.  I looked for “SQLServer” and I wasn’t able to locate it, which meant the error was right!  So now I had to figure out where the counter was instead. I found it shortly after, it was named: MSSQL$SQL2008R2:Databases.  Now that I know what to update that config file with I was able to make the change.  I went back and reopened the config file and updated the following section: “<add key=”SqlCategory” value=”SQLServer:Databases”/>”, and changed it to “<add key=”SqlCategory” value=”MSSQL$SQL2008R2:Databases”/>”.  Once I saved the file, I then reran the utility and this time it did not error out!

When I initially began troubleshooting this, I did the first thing I normally do, I googled the error and looked for anything that people have done to fix and to my surprise, I did not find a single article/blog post!  I found posts about reloading performance counters which I tried to no avail as well, but when I did this method I was able to get the information I needed.  See the below walkthrough with images to see how I accomplished the tasks.

WALKTHROUGH

1. Go to the Azure DTU Calculator website found here: DTU Calculator.

2. Download the Command Line Utility by clicking the link labeled: Download Command Line Utility

3. Extract the files from the sql-perfmon-cl.zip to a location on your local machine

4. Open the SqlDtuPerfmon.exe.config file with Notepad.

5. Edit the section labeled:  DISK LOCATION OF THE OUTPUT FILE

6. Change the value from “C:\temp\sql-perfmon-log.csv” to a location that exists on your system.  Ex: “I:\DTUCalc\sqlperfmon-log.csv”.

7. Save the config file.

8. Edit the section labeled: SQL COUNTER

9. Change the value for SQLCategory from “value=”SQLServer:Databases” to “value=”MSSQL$yourinstancenamehere:Databases”

10. Save the file

11. Right-Click on SqlDtuPerfmon.exe and run it as an Administrator.