When I ask you how fast your car is, there’s a two common metrics for answers:
- How fast it can go
- How much horsepower or torque its engine can deliver
If I ask you how hard your car is working right now, you’d probably answer with:
- Its current speed (in MPH or KPH)
- Its current engine workload (in RPM)
Conveniently, these two numbers are shown front and center on most car dashboards:
For SQL Server, those numbers are:
Current speed: Batch Requests per Second – the number of queries your server is currently handling. It’s available via Perfmon counter, and it’s on the dashboard of most monitoring software.
Wait Time per Core per Second – wait stats is the technique of measuring how much SQL Server is waiting on. You can’t tune wait stats using percentages – instead, you have to look at the total amount of time involved.
Compare these two five-second moments in the life of a SQL Server:
It’s clear that in Workload A, nothing is going on. Sure, 100% of our waits are on storage, but we don’t really have a storage problem. If Workload A was a car, it would be rolling along at 1mph, waiting for the driver to hit the gas pedal.
In workload B, there’s much more going on – but do we have a performance problem yet? That’s not quite as clear. To get the real picture, I have to tell you how many cores the server has.
Let’s Use Wait Time Per Core Per Second
Waits over time don’t really give you a clear picture – you have to divide the workload by the number of cores you have. Let’s say they’re both running on a VM with 8 virtual cores:
The VM on the left isn’t doing any work. It’s coasting.
On the VM on the right, for every second on the clock, each of its cores is spending .4 seconds (400 milliseconds) waiting on something (disk, memory, locks, etc.) While that might sound significant at first, it’s still really not doing any hard work. It’s not unusual for each core to spend several seconds per second waiting on different resources – and that’s fine, because SQL Server’s schedulers are good at switching over and running other queries that don’t need to wait.
For example, say you fire off a big SELECT query that scans a huge table, and we have to go get a lot of data from disk to accomplish it. That query’s tasks can wait for dozens (or hundreds or thousands) of milliseconds while other queries get their work done.
That’s why to measure SQL Server workloads, I’m proposing a new metric: Wait Time per Core per Second. That one metric, by itself, is a lot like the tachometer on a car’s dashboard. It’s not a measurement of how powerful your car is, but it does tell you how hard your car is working at this moment in time.
sp_BlitzFirst® now gives you this metric.
We’ve added new key metrics to the default output, including Batch Requests per Second, and Wait Time per Core per Second:
Note the new output down around priority 250-251 – it helps you get a fast idea of whether the SQL Server is working hard right now, or hardly working.
You can download our First Responder Kit with sp_BlitzFirst® now, and in our training classes, I show you more details on how I use it to triage real-world performance emergencies.