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 database professional based in Los Angeles, CA, and author of "Inside the SQL Server Query Optimizer". He has also contributed to other SQL Server books including "SQL Server 2012 Internals". Benjamin has 20 years of experience with relational databases and has been working with SQL Server since version 6.5. He holds a Master’s Degree in Computer Science and has been a speaker at many SQL Server conferences, including the PASS Summit and SQL Server Connections. Benjamin's blog is at http://www.benjaminnevarez.com and can 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