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://blog.sqlauthority.com)
40 Comments. Leave new
Hello Pinal,
In which situation following type of error can occur in sql :-
“The query has been canceled because the estimated cost of this query (830) exceeds the configured threshold of 300.”
How is speed mismatch handled between CPU and I/O unit ? Explain
I would call this exection plan unit “Gatez” (in short: Gz). So for example a query has 0.3 CPU Gz and 0.8 I/O Gz.
1 CPU Gz means one SQL Server executes one query for one moment.
1 I/O Gz means one SQL Server reads one portion of data for one moment.
I want quick answer to my question please
I apply a query using sql server 2012 and enforce it to executed with some test indexes but in the all cases i have different i/o cost and elapsed time
BUT the cpu time was constant for one query with different indexes it didnt change WHY??
When I work with a different query it give new cpu cost but also it still constant for the same query when i use different indexes type
I have the same problem as u have… have u get the solution? I want see the difference in cpu cost when same select query is executed with index and without index.
Hi sir
My question is about avg_user_total_cost in stored procedure. I tune stored procedures and I realized that avg_user_total_cost had some different value. e.g 66,22 or 999,12 etc. How can I or a computer calculate that value? And What is the maximum value of avg_user_total_cost? Could you explain me ?
Thank you so much.
Hello Sir,
First of all thanks for posting such amazing articles. Second I have one question. I prepared several queries for selecting most recent order by customer and its details. On the basis of execution plan I have decided which query is best. But now I am confused because when I shorten the query with CROSS APPLY then execution plan of query shows it is costly in terms of Estimated operator cost others all parameter is same. But When I ran it in batch with parallel optimized query giving same output. It shows CROSS APPLY query cost 50% less in batch then other. What should I consider execution plan details are opposite but query cost in batch is opposite. I am mentioning query below. Run both of them in batch and suggest which one is better and why. These query are of northwind database
select Distinct cust.companyname,t1.orderid,t1.shipname,t1.freight from
(select o1.customerid,orderid,shipname,freight from orders o1 where orderid=
(select max(orderid) from orders o where o.customerid=o1.customerid)) t1 right join customers cust on
t1.customerid=cust.customerid
order by cust.companyname
select cust.companyname,t.* from customers cust
outer apply (select top 1 orderid,shipname,freight from orders o
where o.customerid=cust.customerid order by orderid desc) t
Hello,
is there any way to get estimated time before for the query (before execute).
like : Select * from employee (then how much time it will take to run).
check over the bottom right corner , its shows the the time sql management studio
If I have an index scan on a network server with an Est. CPU cost of 1+ and Est. I/O cost of 4+ — how should I interpret that? Is this excessive resource consumption? A fair amount? Without being able to compare against a max value, it’s difficult to determine if the query needs rewritten or if it is within reasonable resource constraints.