Actual Query Plan Estimated Cost and dm_exec_query_stats worker time do not gel

Question

I cleared my stats and ran my query. The actual execution plan has a total estimated cost of 0.61. I used the total_worker_time column from the dm_exec_query_stats dmv to calculate an average of 20858 microseconds of CPU time:

(SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count))

The plan recommended an index and I created that index. I cleared my stats and I ran the query again. This time the plan has a total estimated cost of 0.37 at the top level. I checked the dm_exec_query_state dmv again and now the average CPU time is 51536 microseconds.

I was expecting the worker time to be around half, not over double! Am I missing something here? Why is the improvement to the query plan not reflected in the exec query stats? The two plans are uploaded here:

Plan1

Plan2

Answer 1

The estimated cost is a unit-less measurement for the query cost as estimated by the SQL Server query optimizer. Just because query A has a lower estimated cost than query B does not mean that query A will be more efficient than query B. The query optimizer makes assumptions about your hardware, current workload, and data that may not exactly match your system. There may be model limitations or SQL Server may not have access to perfect information. Even on a well-configured server with well-written queries you will not almost certainly not see the estimated query cost correlate exactly with CPU time. Keep in mind that the query cost includes more factors than just CPU time as well.

The estimated cost that you see in your actual query plan is actually still an estimate. It will have the same value as the estimated cost from an estimated plan if the plan shape does not change due to a recompile. Consider the following simple query:

SELECT t1.high AS high, t2.high AS high2
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (FAST 1);

For the estimated plan I get a total estimated cost of 0.0065704. For the actual plan I still have an estimated cost of 0.0065704 even 6431296 rows were returned instead of the single estimated row. The actual plan was uploaded to Paste the Plan if you want to take a look.

If I had to guess why your query was slower I would start with the index spool at node 42:

It's odd that your query now has an index spool after you added an index. You may have created an index with the key columns in the wrong order. Look at the code in fn_get_samples and evaluate if your index is truly the best one. Sometimes the indexes suggested to you by SSMS may suggest a poor ordering for the key columns. You could also consider adding the NO_PERFORMANCE_SPOOL hint to your query but that's probably not the right corrective action to take.

Answer 2

To add to Joe's answer:

If:

• The cardinality estimates for each plan operator were perfect; and
• The (invisible) derived statistics at each operator exactly reflected the data distribution; and
• The optimizer's cost model for CPU and I/O cost exactly matched your hardware; and
• All the simplifying modelling assumptions made by the optimizer were valid on your data
• The query encountered no waits during execution
• & .c

...then you might find some correlation between estimated plan cost and actual execution time.

In fact, the optimizer's cost model is exactly that: a model; one which has proved to be reasonably good at choosing a reasonable execution plan quickly given arbitrary input SQL, on hardware with varying performance characteristics, running against a range of database schemas. Which is quite an achievement if you really think about it.

Anyway, none of the above considerations are particularly true for your query, so it's no great surprise that they perform contra expectations. Moreover, both queries run relatively quickly (14ms and 46ms), which is well within the bounds that the query optimizer would be very happy with.

Put another way: both plans are 'good enough'. If you need to get closer to the ideal, you will have to perform a detailed manual analysis to determine what changes in the query or physical database design are needed to make it go faster (not that the query would really be running in production on pre-release SQL Server vNext).