Earlier this year I had the honor of co-presenting a session on index tuning with Brent Ozar. During that session I stressed the importance of observation when it comes to any kind of performance tuning. All too often I see people make the mistake of focusing on the duration of their queries as the only observation of any importance. They have a tendency to disregard other factors which is perplexing because the other factors can be much more telling than duration alone.
Which is worse? One statement that takes one minute to execute, or one statement that takes one second to run but is called 100 times simultaneously? Now, what if that first statement that takes one minute to run is also called 100 times? Now you think you have a problem but the reality is that your problem already existed. It’s like having termites in your foundation; you do not know it is a problem until it becomes a bigger problem, resulting in a more expensive fix than what should have been necessary.
Even when you recognize the importance of observation and measurements, a few questions will remain. What will you observe? What will you measure? And how?
Before you even start to answer those questions you should understand some basic concepts when it comes to measurements. Most people never consider these items but if you do you will find that you can become much more efficient in your tuning efforts.
Repeatability: the ability for a person using one tool to measure one thing multiple times with minimal variability. This could be as simple as using the SSMS client on your desktop for each and every execution of a particular batch. This is not the same as using SSMS on your desktop for the first dozen runs, then using RDP to go to the server and using SSMS there a few times, and finally by putting your code into a SQL Agent job and letting it run a few hundred times automatically. Pick one and stick with it.
Reproducibility: the ability of multiple people to produce similar average measures for multiple things with minimal variability in the average measured values. This means that if the statement takes ten seconds to run in SSMS from your desktop you want to see if you can reproduce that from SSMS on someone else’s desktop. Or, if you prefer, running something local on the server, from your desktop, and from SQL Agent in an effort to make certain the results are consistent everywhere.
Your measurements will only be good as the tools and the people you have doing the actual measuring. Your first step in any performance tuning situation is to make certain that your measuring system is reliable, and then establish if your issue is with either reproducibility or repeatability. I always advocate making certain you have shown the issue to be reproducible first, and then establish your ability to repeat the issue using one particular tool. Many times we have been able to show the issue is not able to be reproduced everywhere which then leads to a quick fix. This leads to a time savings and we all know that time is money.
Once you have established that the issue is able to be reproduced, and that you have a repeatable test, you can get started on your real tuning efforts. I like to performance tune by examining execution plans and dynamic management views. I also like to make small changes and work in iterations, making it easy to revert to a previous version if necessary. I also like to track everything possible in Excel; the code I am running, the execution plans generated, statisticsio data, and time data. That is usually enough information for me to get started. I know what I want to observe, I have a way to track my measurements, and I can also take the time for some scalability testing.
Contrast that with someone whose idea of performance tuning is to keep running the queries, fire up profiler, filter through the results by looking for long duration times (and possible low read/write values if they have some experience with profiler), and then make some changes to the code in an effort to get faster times when they start their tests all over again.
Instead of spending time guessing where the issues might be, you are better off establishing if the issue is really an issue at all to begin with. And those skills are what will begin to establish you as someone more than just a junior administrator, and someone with performance tuning skillz.
And wait stats. You should learn about those as well.
Nice post. The other thing that’s deceiving about duration is that users with slower computers or slower network connections can skew duration artificially high. If you’ve got an application whose end users are all on slow VPN connections and they’re pulling down big recordsets with, oh, I dunno, Microsoft Access (true story) then the duration will seem high. The queries can’t be optimized any faster, though, because SQL Server is waiting to send the results over the wire. Another good reason to check out wait stats to see the real problem.