In my free How to Think Like the Engine class, we start out by measuring query performance using logical reads. That’s the number of 8KB data pages that SQL Server has to read in order to find your query’s results. That’s the measure I use the most because generally speaking, the less data your server has to read, the faster your query will finish.

However, sometimes you have to choose between a couple of different queries or plans that produce roughly similar numbers of logical reads. Other times, you need to focus on reducing a different bottleneck on a server. In these cases, here are some other popular ways to measure your query.

1. TempDB spills – some query plan operations require memory in order to run. Ideally, SQL Server grants enough memory for the query’s operations to happen completely in RAM. When that doesn’t happen, operators like sorts will have a yellow bang on them, and when you hover your mouse over the operator, you’ll see a warning about the operator spilling to disk. In cases like this, I may need to tune the query to get SQL Server to allocate more memory, or change the way the operations get done so that they require less memory – like building indexes to remove sorts. To measure TempDB spills, start by looking at the actual (not estimated) execution plans, or run sp_BlitzCache @SortOrder = ‘spills’.

2. Unused memory grants – I just got done talking about what happens when SQL Server doesn’t grant enough memory to running queries, but what happens when it grants too much? A single query can grab 25% of the available memory, and it doesn’t take a whole lot of 25% grants before you’re fresh out of memory. In these cases, I need to look at the properties of the actual (not estimated) execution plan, figure out why SQL Server is granting too much memory, and reduce those requirements. To find the queries involved, run sp_BlitzCache @SortOrder = ‘unused grant’.

3. Parallelism – when your query has a lot of CPU-intensive work to do, it might go faster if that work is split across multiple CPU cores. However, there are drawbacks with spinning up multiple worker threads for a query, like having the same memory grant split across multiple cores, too. If only one core ends up processing data, you can end up with a parallel query that spills to disk, but a single-threaded query that does all its work in memory. We discuss this in more detail in the Mastering Query Tuning class.

4. CPU time – visible in the Messages tab of SSMS by running SET STATISTICS TIME ON. I discuss this one only briefly in How to Think Like the Engine because it varies so doggone much: run the same query ten times in a row, and you’ll get ten different amounts of CPU time consumed. I focus on this when I’m tuning a server facing high CPU usage, especially when the top wait type is SOS_SCHEDULER_YIELD. When I’m comparing query plans, I’m less worried about 5-10% differences that can happen every time a query runs, and more worried about order-of-magnitude differences.

5. Elapsed time (duration) – similar to CPU time, this one can be all over the map, jumping up and down dramatically each time a query runs. For details about why, see my post 15 Reasons Your Query Was Fast Yesterday, But Slow Today. This one is my absolute last resort because of how wildly unreliable and unrepeatable it is, and like CPU time, I really only focus on order-of-magnitude differences.

To learn more, join me here in a totally free webcast today & tomorrow of my Fundamentals of Query Tuning class:

• Tuesday, Europe-friendly times, 8:00-16:00 UTC: Add to Calendar
• Wednesday, mericas-friendly times, 9AM-5PM Eastern: Add to Calendar

To follow along with the demos and ask questions during class, read these prerequisites. If you can’t make it live, check out the Black Friday bundle deals on my recorded classes.