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

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.

Solarwinds

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

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Initializing a Merge Subscription Without a Snapshot
Next Post
SQL SERVER – Find Statistics Update Date – Update Statistics

Related Posts

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

    Reply
  • How is speed mismatch handled between CPU and I/O unit ? Explain

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

    Reply
  • 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

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

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

    Reply
  • 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

    Reply
  • 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).

    Reply
  • check over the bottom right corner , its shows the the time sql management studio

    Reply

Leave a Reply

Menu