THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Benjamin Nevarez

The Query Optimizer and Cost Estimation

Last year when I presented my session regarding the Query Optimizer at the PASS Summit and at a couple of local user groups, I was asked how the estimated CPU and I/O costs in an execution plan are calculated. Since Microsoft does not publish how these costs are calculated all I could say was that each operator cost depends on the operator algorithm and the cardinality estimation.

 

This time I am working on a related project and I thought that perhaps I could look into this again to show as an example. But since there are dozens of operators I decided to start with a simple one: the Clustered Index Scan operator. So I captured dozens of XML plans, used XQuery to extract their cost information and after some analysis I was able to obtain a basic formula for this specific operator.

 

To show this in an example, let us look at the largest table in AdventureWorks. Run the following query and look at the estimated CPU and I/O costs for the Clustered Index Scan operator

 

select * from Sales.SalesOrderDetail

where LineTotal = 35

 

clip_image002

 

For a Clustered Index Scan operator I observed that the CPU cost is 0.0001581 for the first record plus 0.0000011 for any additional record after that. In this specific case we have an estimated number of records of 121,317 so we can use 0.0001581 + 0.0000011 * (121317 – 1) or 0.133606 which is the value shown as Estimated CPU Cost. In a similar way, I noticed that the minimum I/O cost is 0.003125 for the first database page and then it grows in increments of 0.00074074 for every additional page. Since this operator scans the entire table I can use the following query to find the number of database pages, which returns 1,234.

 

select in_row_data_page_count, row_count

from sys.dm_db_partition_stats

where object_id = object_id('Sales.SalesOrderDetail')

and index_id = 1

 

In this case I have 0.003125 + 0.00074074 * (1234 – 1) or 0.916458 which is the value shown as Estimated I/O Cost.

 

Finally, we add both costs, 0.133606 + 0.916458 to get 1.05006 which is the total estimated cost of the operator. In the same way, adding the cost of all the operators will give the total cost of the plan. In this case, the cost of the Clustered Index Scan, 1.05006, plus the cost of the first Compute Scalar operator, 0.01214, the second Compute Scalar operator, 0.01213, and the cost of the Filter operator, 0.0582322, will give the total cost of the plan, 1.13256, as shown next.

 

clip_image004

 

Finally, you can combine this with my previous post, Fooling the Query Optimizer at http://sqlblog.com/blogs/ben_nevarez/archive/2010/01/13/fooling-the-query-optimizer.aspx, to test with “bigger” tables by changing the number of records and pages of a table for testing purposes.

Published Wednesday, February 17, 2010 9:23 PM by Ben Nevarez
New Comments to this post are disabled

About Ben Nevarez

Benjamin Nevarez is a SQL Server MVP and independent consultant based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of "SQL Server 2014 Query Tuning & Optimization" and "Inside the SQL Server Query Optimizer" and co-author of "SQL Server 2012 Internals". With more than 20 years of experience in relational databases, Benjamin has also been a speaker at many SQL Server conferences, including the PASS Summit, SQL Server Connections and SQLBits. Benjamin’s blog can be found at http://www.benjaminnevarez.com and he can also be reached by e-mail at admin at benjaminnevarez dot com and on twitter at @BenjaminNevarez.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement