Actual Execution Plan Costs

Why don’t “actual execution plans” have “actual execution plan costs”?

This is a question and a myth I have to fight against all the time. It’s so hard to convince people that all execution plans are estimated plans in the first place (by the way, all execution plans are estimated plans). If we execute a query at the same time we capture a plan, we have enabled SQL Server to also capture run-time metrics with that plan. So we end up with what is known as an actual plan, but it’s still just an estimated plan plus those run-time metrics.

Execution Plan Costs

When you look at a given operator within an estimated plan, it’s going to show you four numbers related to cost:

• Estimated CPU Cost
• Estimated I/O Cost
• Estimated Operator Cost
• Estimated Subtree Cost

The values in the CPU and I/O cost represent calculations, usually based on the row counts supplied by statistics, but they can come from other sources as well. It’s all very internal to the query optimizer, although, with some experimentation and testing, you can derive some of the calculations that arrive at the costs. Those costs are also not reflective of your machine. Yeah, it says CPU and I/O, but it’s not measuring your computer to determine it’s bandwidth and then reporting back to you. These are just estimated values. I recommend that you don’t really think of it as CPU or I/O.

Focus instead on the overall Estimated Operator Cost (which, usually, consists of adding the other two costs). When you think about that cost, just think about it as Cost Units, not a literal interpretation of CPU and I/O. It’ll help you keep your sanity.

The final value, the Estimated Subtree Cost, is the cumulative (added up in NodeID order) costs of each individual operator.

Everyone can agree. On an estimated plan, you work with estimated values.

Actual Execution Plan Costs

Then, we get the actual execution plan. The actual execution plan has performance metrics. In Azure SQL Database and in SQL Server 2017, the plan captures Actual Time Statistics, literally, elapsed time and elapsed CPU time if it’s greater than 1ms. The plan captures this information for each operator as well as for the overall plan.

Each plan also captures the Actual I/O Statistics. These represent the number of reads, broken down by type.

• Actual Lob Logical Reads
• Actual Lob Physical Reads
• Actual Lob Read Aheads
• Actual Logical Reads
• Actual Physical Reads
• Actual Read Aheads
• Actual Scans

Therefore, with all this information, clearly, I’m wrong and the costs shown on the plan are the actual execution plan costs, not a repeat of the estimated plan costs.

No.

Let’s do an experiment. Let’s take a query:

SELECT  c.CustomerID,
        a.City,
        s.Name,
        st.Name
FROM    Sales.Customer AS c
JOIN    Sales.Store AS s
        ON c.StoreID = s.BusinessEntityID
JOIN    Sales.SalesTerritory AS st
        ON c.TerritoryID = st.TerritoryID
JOIN    Person.BusinessEntityAddress AS bea
        ON c.CustomerID = bea.BusinessEntityID
JOIN    Person.Address AS a
        ON bea.AddressID = a.AddressID
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   st.Name = 'Northeast'
        AND sp.Name = 'New York';

We’ll capture the actual plan for this query and save it to a file. Then, we’ll capture the actual plan. Finally, we compare the plans using that functionality within SSMS 17. The results showing the Operator Cost and the Subtree Cost are right here:

You see that these are the same value. Despite the fact that the estimated number of rows is 141 and the actual number is 3. While the information may be available for an actual execution plan cost to be generated, it is not.

Conclusion

Why doesn’t Microsoft show us different costs for different plans? The information exists. We see it in the plans. The next step should be simple. However, as you can see, they are not taking that step. Not yet anyway.

Want to learn a lot more about reading execution plans? I have an all-day seminar available at the following times and locations:

I’ll be at SQLSaturday Sioux Falls (and my first time speaking in South Dakota) on August 18, 2018. Please sign up here.

For SQLSaturday Oslo on August 31, 2018. Click here right now to register.

I’ll be at my local event too, SQLSaturday Boston, on September 21st. You can go here to register.

I’m going to be presenting at SQLSaturday Munich on October 26, 2018. Go here now to join the class.