How to Find the Estimation Cost for a Query

In the processes of executing a query the database optimizer calculates an estimate at the cost to run the query. This cost is determined based on statistics. The statistics help the optimizer guess how much CPU and IO will be used when running the query. The database optimizer calls the calculated cost as the Estimated Cost of a query. This Estimated code is only an estimate of the execution cost, it is not the actual cost of the query. The database optimizer uses the Estimated Cost of a query to determine the appropriate plan to use to process the query. One of those executions options is whether or not the optimizer should pick a plan that goes parallel or not.

If you want to know the Estimated Cost of a query you can find it stored in the cached plan for a query. Here is an example of how to extract the Estimated Cost for execution plans stored in the procedure cache.

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,PlanHandles AS (
    SELECT  plan_handle
            ,SUM(total_elapsed_time) AS total_elapsed_time
            ,SUM(execution_count) AS total_execution_count
    FROM  sys.dm_exec_query_stats
    GROUP  BY plan_handle
)
,Plans AS (
    SELECT  ph.plan_handle
           ,qp.query_plan
           ,ph.total_elapsed_time
           ,ph.total_execution_count
      FROM  PlanHandles ph
         OUTER APPLY sys.dm_exec_query_plan(ph.plan_handle) qp
)
SELECT p.plan_handle
      ,p.query_plan
      ,p.total_elapsed_time
      ,p.total_execution_count
      ,q.n.value(N'(@EstimateRows)[1]', N'FLOAT') AS EstimatedRows 
      ,q.n.value(N'@EstimatedTotalSubtreeCost', N'float') AS EstimatedCost 
FROM  Plans p CROSS APPLY
      query_plan.nodes(N'//RelOp')q(n)
WHERE q.n.value(N'@NodeId', N'INT') = 0 
ORDER BY total_elapsed_time DESC;

# # #

» See All Articles by Columnist Gregory A. Larsen