Let’s say you have an assistant. (I know, unlikely, but bear with me.)
And say you give your assistant a task – hey, go fetch me a coffee. Your assistant would nod obediently, go head out to the neighborhood coffee shop, get your preferred Americano, and bring it back to you. It might take them 10 minutes to go achieve that task – and then when they return, they’ll sit patiently waiting for their next assignment.
If fetching coffee is the only task you have for your assistant, they’re mostly going to be sitting around bored. You just don’t drink coffee fast enough to keep them busy.
When you ask them for coffee,
you might not be satisfied with your average wait time.
But hiring more assistants isn’t going to get you coffee faster.
Say you give them more tasks.
You ask them to book you flights for Intersection and Summit, summarize our latest blog posts, and file your expense reports. It’s not a lot of work, and they can context-switch between tasks, and none of the individual tasks will be hard to perform.
If, while they’re working on your expense reports, you ask for another coffee, they can switch tasks and go fetch it for you. Fetching coffee might take a little longer, but it won’t be terrible.
The busier your assistant is,
the longer the average task will take to complete.
And if you give them a truly overwhelming amount of hard work to do – like take your car to the dealer for service, pick up your dry cleaning, design indexes for a query, tune a thousand-line stored procedure, and comprehend a Joe Obbish blog post – then they might not be able to context-switch as quickly or as effectively.
When your assistant is really busy,
then it may take them hours to get you coffee,
and it may make sense to hire another assistant.
SQL Server workloads are measured with wait times.
In my How to Measure Your SQL Server video, I explain how to use Wait Time Ratio to gauge how busy your SQL Server is. Track how many hours of wait time SQL Server piles up in a given hour, and you can get a pretty good idea of its workload intensity. To take an extreme example:
If you only run one query per hour,
and that query only waits on storage for 30 seconds,
your server is bored.
That doesn’t mean the query finishes instantly, nor does it mean users aren’t complaining. The CEO might be running that 30-second query, and she might want it to finish instantly. However, on a bored server, you stop looking at wait stats and overall performance, and start looking at query-level performance.
When your server is bored, tune queries.
If users are complaining about performance, stop looking at the server and start looking at queries. My favorite way is our open source sp_BlitzCache, which shows you your top most resource-intensive queries lately.
To find which queries have taken the longest time overall:
EXEC sp_BlitzCache @SortOrder = 'duration';
That sorts by total duration, mind you – a query that ran 1,000 times for 1 second each will float higher in the results than a query that only ran once for 30 seconds. If you want to find the longest-running individual statements, run:
EXEC sp_BlitzCache @SortOrder = 'avg duration';
Then look at the Warnings columns on where to start your tuning efforts. If you hit a wall, check out how to get help with a slow query.