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

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



Sun, 06 Feb 2005 23:11:47 GMT
 Query Analyzer - I/O Cost, CPU Cost, and Cost

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.


Quote:
> 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




Mon, 07 Feb 2005 12:36:01 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. SQL Query Analyzer Execution Plan Cost Errors

2. SQL Query Analyzer Execution Plan Cost Errors

3. Query Analyzer costs well beyond 1000%

4. Query analyzer newbe question about Subtree cost

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

6. Platinum Log Analyzer cost?

7. CPU Costs of using views

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

9. Comparitive Costs os RISC Servers

10. Query Cost Estimation

11. Query Cost vs IO

12. Query Cost Ranking


 
Powered by phpBB® Forum Software