Several weeks ago I added support for a command line utility (CLU) to help collect performance metrics from SQL Server, prior to using the Azure SQL Database DTU Calculator, as an alternative to the PowerShell script. I apologize for not adding a blog post sooner. If you are unfamiliar with the Azure SQL Database DTU Calculator, I encourage you to start with the first post in the series.
I added the CLU for a couple of reasons. The first being a result of the sheer volume of questions I received regarding the PowerShell script not working for some reason or another; the second reason being that I have a suspicion many of the people using the DTU Calculator would feel comfortable with a tool written in C#.
When I created the PowerShell script, I tried to keep it as simple as possible and I figured that any engineer could open it up and modify as needed. In hindsight, I should have updated the script to include more validation and error handling but I felt that adding too much script would do more harm than good. I may test that theory by adding validation and error handling in the future – feel free to help me out, update it yourself, and send me the script – but for now I’ve created an alternative C# based utility.
The C# code is hosted on GitHub so you can review/modify as needed. If you’ve reviewed the code, you can see it’s pretty simple so I won’t spend much time discussing. However, there are a couple of points worth mentioning:
- The utility collects CPU and IOPS exactly the same as the PowerShell script. However, the “Database – Log Bytes Flushed” counter is optional. The utility uses that counter if it exists, and proceeds without it if it doesn’t.*
- The app.config contains all the settings for the utility. You can change the counter category, instance, and/or name; the collection interval and duration; as well as the CSV file location. If you need more than that, you can download the source code and modify as needed.
*Note: Since the PowerShell script and command line utility measure CPU and IOPS at the server level and Log Bytes Flushed is measured at the database level, I decided to make the database counter optional.
As always, if you have questions and/or feedback, don’t hesitate to reach out.
Justin Henriksen 
I have a general question about the DTU calculator tool: as far as I can tell the counters you look at don’t specifically measure memory usage. According to Microsoft’s definition a DTU is a blended measure of CPU, memory and IO so I’m wondering: if I’m trying to estimate DTU needs for a server where most of the db data is loaded in memory already (because I have enough RAM to cover that database) then I won’t see much IO activity but if we’re not directly measuring memory usage for the calculator are we missing that critical component of the DTU?
Thank you for making this tool available.
You are correct that DTU is a blend of CPU, IO, and memory but the counters I use don’t measure memory. It’s a very complicated answer but the main reason memory isn’t measured is due to the way memory is used by Azure SQL when compared to SQL Server. It’d be like comparing apples to oranges. CPU and IO end up being better predictors of DTU for SQL Server to Azure SQL migrations.
The command line utility writes values with wrong decimal separator in my culture (tr-TR) so I had to change the code on the line in the link so that the values are always written to the file in the format “123.45”. Unless this change made I wasn’t able to use the calculator.
https://github.com/jhenriks79/Dtu-Calculator-Perf-Collector/blob/master/SqlDtuPerfmon/Program.cs#L173
Thx!
Hey Justin I am having an issue with running the command line utility on our SQL Server. I am getting the following error message when the script starts.
Processor doesn’t exist. Try running perfmon.exe to identify the correct Processor category.
Any suggestions ?
Thank you,
Thore
That means the name of the processor counter on my machine is different than yours. You can use Perfmon.exe to figure out the names of your counters.
Hi Justin, when using the command line utility, we get a CSV with the field separator as , as well as the decimal separator being , as well. As this server is very important, we don’t want to change that setting as it is correct for our region. How can we configure CSV settings, like encapsulating the field in quotes or using a different field separator? Obviously the current file gives us DTU calculations with “out of range” errors.
The command line utility is on GitHub. You could modify to use ‘|’ for field separation and run the utility on the server. Before uploading, open the file in Notepad or something and replace all the ‘,’ to ‘.’ and all the ‘|’ to ‘,’ and save the file.
https://github.com/jhenriks79/Dtu-Calculator-Perf-Collector
Just to add on the issue encountered, i have tried a simple “Get-Counter “\Process(*)\Working Set – Private”” but it fails too.
Have you tried Perfmon.exe?