During the SQL Server Optimization training, I enjoy teaching the Execution Plan. I am always sure that questions related to the estimated cost will be raised by attendees. Following are some common questions related to costs:
Q: What is the estimated I/O cost?
Q: What is the estimated CPU cost?
Q: Why there is no unit of measurement for estimated costs?
There are several other questions. However, let me try to answer the above questions today.
Estimated I/O Cost and CPU Cost are just cost estimations as the names suggest. SQL Server Query Optimizer uses the cost to make the decision on selecting the most efficient execution plan. When any query is executed, the SQL Server Query Optimizer prepares several alternative executions plans to execute the query. Each alternative plans each operation and assigns some type of cost on the basis of the heuristic data. This estimated number just implies the amount of work CPU or I/O has to do to complete the task. Due to this reason, there is no unit assigned for these estimations. These estimates should be used by us in the same way by which the SQL Server uses it – The estimate should be used to compare different queries with each other.
Let me know your thoughts on this. Do post here if you have any other questions. I will post the answers in separate posts.
- How to Find Table Cardinality from the Execution Plan? – Interview Question of the Week #213
- SQL SERVER – Execution Plan Ignores Tabs, Spaces and Comments
- What is the Difference Between Physical and Logical Operation in SQL Server Execution Plan? – Interview Question of the Week #122
- How to Force a Parallel Execution Plan for a Query? – Interview Question of the Week #170
- SQL SERVER – Finding The Oldest Query Plan From Cache
- SQL SERVER – List Query Plan, Cache Size, Text and Execution Count
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
