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
Hi sir,
but that is the thing we want to know that what does 0.003125 means. Is that seconds, worker threads, milli seconds or what. means it is ok we know if the value is large it is bad but what does this value denotes to like i am jus concerned about unit.
Hope you will spot some light on this.
Hi Sumit,
As mentioned it is just combined value. There is no unit like ms or threads or anything. That is the point of this post. There is no unit. You can use this number for comparison with other executions counts.
Kind Regards,
Pinal
Hi Pinal,
What should be the minimum value of this number for the best execution plan?
“When any query is executed, the SQL Server Query Optimizer prepares several alternative execution plans to execute the query”
As per above your statement how can we see all the several alternative execution plans?
Why we can able to see only one execution plan?
If all execution plan have different estimated I/O cost and estimated CPU cost then SQL server display and use execution plan with minimum value with estimated I/O cost and estimated CPU cost , is it right?
Can you please more specific?
@paresh
Yes that is true SQL server Query Optimizer creates several alternative plans to get expecyed results and we not able to see all the execution plans because this is by design that Query optimizer choose the best plan to get the expected results. So we are able to see only the best execution plan by Query Optimizer and i think that is what the name suggests SQL Server Query Optimizer.
This is my share of knowledge.
if Pinal sir can spot more light on this thing that if we able to see all the plans produced by Query Optimizer as per my knowledge it is not possible.
The phrase is “shed more light”, not “spot more light”. Just an FYI.
LMFAO
I use this tool almost daily and it is great! Here’s a short example how I use execution plans…
What I do is. First I try to find where the problem lies. If user interface is slow on some page/view/whatever I go through every SQL operation on that page. I run them “by hand” using SSMS. This way I can quickly isolate the queries that are causing problems.
Next I check their execution plans. Different operations shows how much they cost relative to the whole query. I concentrate on finding operations which cost the most. Usually I find that my tables are lacking indexes.
So I go ahead and open another SMSS instance. I create index based on the previous execution plan and check the execution plan again. This way I’m able to compare execution plans side-by-side and quickly see if my new indexes are working properly.
Now why would I want to see the other, not-so-good, execution plans? Those are irrelevant. Only thing that matters is what the Query Optimizer chooses to use.
Sometimes I must say it chooses something that doesn’t make sense to me but I trust that Microsoft’s programmers knows better :)
Marko,
Another great comment!
Hi,
These execution plans are very useful, what does estimated operator cost 32% mean?
I believe that if the SQL Query optimizer already chooses the best execution plan it can find, and that the estimated CPU and IO costs are still too high for your needs, then it’s a good indication that your query or database needs a structure change.
WHY WE USE COMPUTE
Use can use
SET STATISTICS TIME ON
before the query and then
SET STATISTICS TIME OFF
and execute to know the exact time a query is taking to execute…
some times this helps with the comparition.
I can second that!
I’m working in a project where we have two almost identical databases, but not quite. One is used in development and one in testing. Dev-db is 100x larger than the test-db and dev-server is 10x more powerfull (more cores and memory).
Now if you go and just take execution plan from dev-db and then compare with execution plan from test-db you can get wildly different plans and wildly different estimations. Only way to be sure if optimizations works as planned is to set STATISTICS TIME ON and run the same query on both dbs.
Production Server:
SQL Server Execution Times:
CPU time = 16563 ms, elapsed time = 17187 ms.
Development Server
SQL Server Execution Times:
CPU time = 3433 ms, elapsed time = 4167 ms.
Above are the query execution timings in production it is 17 secs and test server it is 4 secs for the same copy of database
Please help me steps how I can optimise
Hi Pinal,
is it possible that
set statistics io will differ with the actual /estimated execution plan’s estimated cost?
Hi Abhijit,
The set statistics io result should not differ than the actual execution plan’s io cost. The estimated cost is just an estimation of SQL optimizer on the basis of data statistics and that may differ considerably if data statistics is not updated.
Regards,
Pinal Dave
All right, let’s no look for a unit or any other measurement. But how do I know if a given number is low or high. For example:
I/O Estimated Cost = 0.89874 is high or low?
Hello Juan,
As I write in article that 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.
Regards,
Pinal Dave
Pinal
I understand that “unit” has no predefined value. It may be 1 second, 1.5 seconds, or 4 years.
You said that the unit is used to compare different possible execution plans, and then chooses the plan with the lowest overall cost. For this to happen, obviously the unit, whatever it may be, has to be consistent between the different execution plans.
My question is that are these units consistent between entirely different statements? For example if I submit one query, which is very I/O intensive, and then 5 minutes later, on a different database connection, submit another query which is very CPU intensive, will the units for CPU and I/O be the same for both the queries (of course their numbers will be different)?
Thanks,
Lee
Sometime they will be and sometime they may not.
Usual case is they are same in most of the cases.
Dear Pinaldave,
I came across the phrase: the total I/O was two logical reads in interpreting an actual execution plan. Could you please explain a bit about it?
Thank you in advance.
Steve
Hi Pinal,
There is a Query.
If i run it on Production environment it takes around 6 mins if same i try to run on my DEV Server its taking more than an hour. can you please help me out. And also is there any way to compare two execution plans?
Thanks
Manjunath
Hi Pinal
I was looking your notes on performance tuning. I was very impressed after looking those.I am very new to SQL DBA. I have one question for you I just want to knowhow we can tune the stored procedures in sql server2005.
I was trying to tune the sp’s but I am able to change the indexes on the table. i can still see the higher i/o and CPU values. I can tune the sp’s upto some value but not more than that. I will be very helpful if you help me in this.
Thanks,
Keshav
Manjunath,
That is because of some other processes are going on your production server. That why execution plan is diifer then dev server.
Thanks
Darshan
Hi Darshan,
Thank you. But can you please let me know how other process can cause my current query execution Plan.
Regards,
Manjunath
Please see pauls whites blog on this —
The I/O cost of 0.003125 is exactly 1/320 – reflecting the model’s assumption that the disk subsystem can perform 320 random I/O operations per second
great knowlege i get regarding execution plan
I am wondering if anyone knows if there is a way to check the estimated cost of a query before running it, and then return that value as a variable so that, if it is too high, I can elect to prevent certain queries from executing?
Hi Pinal. How can we write TSQL queries that take benefit of parallelism/multiple processors that are available on a production environment.