Check Every Metric

Recently, a person asked about the costs differences in an execution plan, referencing them as if they were performance measures. The key to understanding performance is to check every metric. When it comes to execution plans, I’m sure I’ve said this before, so please allow me to repeat myself.

The cost numbers shown in an execution plan, which, barring a recompile, will be the same for an execution plan or an execution plan with runtime metrics (aka, estimated and actual plans), are not measures of performance. They do not represent actual metrics. Instead, they are calculations of a theoretical actual performance measurement. So, you can’t look at two plans, with two costs, and say, “this plan will perform better.” Instead, you can say, “this plan has a lower estimated cost.” To really see performance metrics, you must measure performance.

However, make darned sure you check every metric, because I missed this one. Thanks to Erik Darling for pointing it out for me.

Measuring Performance

Let’s say you have two queries that return identical data sets but have different execution plans:

SELECT
    SOH.CustomerID,
    SOH.SalesOrderID,
    SOH.OrderDate,
    C.TerritoryID,
    ROW_NUMBER() OVER ( PARTITION BY SOH.CustomerID
                        ORDER BY SOH.OrderDate ) AS Row_Num
FROM Sales.SalesOrderHeader AS SOH
JOIN Sales.Customer AS C
    ON SOH.CustomerID = C.CustomerID;
GO --50

WITH Sales
AS
(
    SELECT
        CustomerID,
        OrderDate,
        SalesOrderID,
        ROW_NUMBER() OVER ( PARTITION BY CustomerID
                            ORDER BY OrderDate ) AS Row_Num
    FROM Sales.SalesOrderHeader
)
SELECT
    Sales.CustomerID,
    Sales.SalesOrderID,
    Sales.OrderDate,
    C.TerritoryID,
    Sales.Row_Num
FROM Sales
JOIN Sales.Customer AS C
    ON C.CustomerID = Sales.CustomerID;
GO --50

You can look at the execution plans. You can execute the query and capture the runtime metrics with the execution plan (aka Actual Plan). You will get some performance metrics in waits and runtimes. However, trying to compare these is going to be tough.

Note the “–50” I have next to each GO above. What I do is turn on an Extended Event session and capture the execution of each query, fifty times (with execution plans turned off, that negatively impacts performance, so isn’t helpful when trying to understand how the query works). Using the Data Explorer window I can quickly get average executions, reads, both logical and physical, to understand how these queries performed.

If you look at both queries plans, the second, with the CTE, has a lower overall cost. However, when you measure performance, it actually runs longer on average and uses many more reads. We’re done then, the first query is better. Not quite.

Check Every Metric

You’ll notice above I’ve described how much time each query took to execute and how many reads there were. Often, this is all you need to know. However, there are several metrics you should be using when tuning queries, not just duration & reads. Sure, duration can be flat out the single most important metric. And yes, reads are principally the number one bottleneck, so focusing there is important. Just the same, we should care about CPU and memory (it was the memory that Erik reminded me to check).

The Extended Events will show the CPU. There, we have a slightly different tale to tell. The second query, with the lower overall estimated costs, indeed uses just slightly less CPU. If our machine was CPU bound, despite the fact that it runs longer on average and uses more reads, we may want to use that query to deal with our CPU problems.

Similarly, as I was reminded, we should also look at the memory use. Now, that metric isn’t captured in the standard Extended Event for performance metrics. However, it is right there in the execution plan with runtime metrics. In this case, again, the second query uses less memory. Here again, if we’re bottlenecked on memory, that may make that query more attractive.

Conclusion

You really are most likely going to look at overall execution most of the time. After all, you take a query from running in 15 minutes and make it run in 15 seconds, I’ll bet you it’s also using radically fewer resources. However, when you get down to more granular levels of tuning, especially trying to decide between two query patterns that result in the same results, you need to ensure that you use all the necessary metrics. It’s not enough to just look at execution time. You should be adding in reads, CPU, memory, number of executions, waits, and all the rest in order to make informed decisions about which query is truly “performing” better in your situation.