dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 4, 2020

How to Find the Estimation Cost for a Query

By Greg Larsen

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



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM