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.