SQL SERVER – Execution Plan – Estimated I/O Cost – Estimated CPU Cost – No Unit

During the SQL Server Optimization training, I enjoy teaching Execution Plans. I am always sure that questions related to 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 execution plans to execute the query. Each alternative plans each operation and assign 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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

37 thoughts on “SQL SERVER – Execution Plan – Estimated I/O Cost – Estimated CPU Cost – No Unit

  1. 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.

  2. “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.

    • 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 :)

  3. 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.

  4. 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

  5. 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

  6. 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?

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. Hi Darshan,

    Thank you. But can you please let me know how other process can cause my current query execution Plan.

    Regards,
    Manjunath

  14. 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?

  15. Hi Pinal. How can we write TSQL queries that take benefit of parallelism/multiple processors that are available on a production environment.

  16. 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.”

  17. 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.

  18. 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

  19. Pingback: SQL SERVER – Weekly Series – Memory Lane – #013 « SQL Server Journey with SQL Authority

  20. 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.

  21. 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

  22. 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).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s