Actual Query Plan Estimated Cost and dm_exec_query_stats worker time do not gel 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
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.