Query Analyzer - I/O Cost, CPU Cost, and Cost

Query Analyzer - I/O Cost, CPU Cost, and Cost

Post by Mark Milchu » Fri, 23 Aug 2002 00:11:47



When you run a query and look at the Execution Plan you
will see three different costs.
1) I/O Cost - estimated cost of all I/O activity for the
operation.
2) CPU Cost - estimated cost for all CPU activity for the
operation.
3) Cost - cost to the query optimizer in executing this
operation.

Obviously all three should be minimized BUT which is more
important to minimize.  When comparing between two
different methodologies for a task, which cost is most
important when deciding which method is best?

                Method 1    Method 2
                ---------   ---------
Total I/O Cost = 0.02632     0.897
Total CPU Cost = 0.000082    0.567001
Total Cost     = 0.032851    0.016438

Method 1 (does 2 Writes and a Read) is much less costly
when it comes to I/O Cost and CPU Cost.  Method 2 (does 1
Read and 1 Write) is half as costly in terms of Cost.

So which Method will produce better performance and why?

Thanks for any help,
Mark Milchuk

 
 
 

Query Analyzer - I/O Cost, CPU Cost, and Cost

Post by Gert E.R. Draper » Fri, 23 Aug 2002 13:36:01


It is hard to say which one of the two you want to optimize. I/O is in
general more expensive and more limited, assuming you could more easily add
CPU's to a machine, where you can not expand the throughput of a system bus,
but only adding controllers to improve disk I/O. Besides that a really well
tuned database server is very close to being both I/O and CPU bound, most of
the time it ends up being CPU bound.

You can look at these articles to begin with:

HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q243589

Query Tuning
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/opti...
p_tun_1_536v.asp

What in my opinion you should look for is not to focus on the hard numbers
but the relative cost of operations in side the query plan. For example if
you see a SORT operator that takes 80% of the cost of a query, that could be
an indication of a missing or poorly defined index. A table scan that is 50%
of the query cost, could be easily reduced to a fraction by having it become
an index based operation. That is much more important than focusing on the
hard numbers.

Performance tuning is not a hard math problem, it is creating a balance in
an eco system, balancing resources (hardware (memory, disk and network), OS
and database server). If you find the right balance you have a great working
system.

Just my thoughts,

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright ? SQLDev.Net 1991-2002 All rights reserved.


> When you run a query and look at the Execution Plan you
> will see three different costs.
> 1) I/O Cost - estimated cost of all I/O activity for the
> operation.
> 2) CPU Cost - estimated cost for all CPU activity for the
> operation.
> 3) Cost - cost to the query optimizer in executing this
> operation.

> Obviously all three should be minimized BUT which is more
> important to minimize.  When comparing between two
> different methodologies for a task, which cost is most
> important when deciding which method is best?

>                 Method 1    Method 2
>                 ---------   ---------
> Total I/O Cost = 0.02632     0.897
> Total CPU Cost = 0.000082    0.567001
> Total Cost     = 0.032851    0.016438

> Method 1 (does 2 Writes and a Read) is much less costly
> when it comes to I/O Cost and CPU Cost.  Method 2 (does 1
> Read and 1 Write) is half as costly in terms of Cost.

> So which Method will produce better performance and why?

> Thanks for any help,
> Mark Milchuk



 
 
 

1. SQL Query Analyzer Execution Plan Cost Errors

Can anybody help me with a problem in SQL Query Analyzer?

I'm using SQL Server Enterprise Edition SP2 on Windows
2000 Server/Pentium III, and the execution plan is showing
crazy cost numbers for each node in the execution tree.
Sometimes is 0% everywhere, some other times is 200% in
one node and 25% in others, when it should sum up to 100%
for all the nodes. All queries show the correct execution
plan, only the cost estimates are wrong.

I know this is a bug in the graphical execution plan, but
can anybody tell me a remedy for this? I tried the
Evaluation Edition, and the same thing happens. I also
tried with and without service packs (1 and 2).

I've seen screenshots in books and on the Web, and they
are OK, so something must be wrong with my installation.

Thanks,
Adrian

2. Informix platform search page

3. SqlMail probelm(I think?)

4. Query Analyzer costs well beyond 1000%

5. Time dimension members sorting improperly when fetching cellset from cube

6. Query analyzer newbe question about Subtree cost

7. Help! Subquery returned more than 1 value

8. Estimated I/O, CPU, and SubTree Costs

9. Platinum Log Analyzer cost?

10. CPU Costs of using views

11. E_OP048B_COST (consistency check - cpu or disk i/o cost is negat ive)

12. Comparitive Costs os RISC Servers