Graphycal Execution Plans

[Gilberto]

Hi,

I couldn't find much about the Query Analyzer's Graphycal Execution Plans outputs. The topics on SQL Server Books OnLine does not answer those below:

- In what unit values are shown?
- Why does "Estimated I/O Cost" differ from "SET STATISTICS IO" output? Same for "Estimated CPU" and "SET STATISTICS TIME".
- Why "SELECT * FROM authors" is less CPU- and I/O-expensive than "SELECT * FROM authors WHERE au_id = '1'"? The second one is a Clustered Index Seek, while the first is a Clustered Index Scan... It should be "cheaper", especially if the table has only one data page, isn't it?

Tnxs,

G

 

[tlbroadbent]

I think the cost units aren't units in the sense of measuring estimated time or an I/O factor. They seem to be "relative units" if you can have such a thing. It is as though there is some standard value and one query is 4.5 times more expensive than the standard while another is .5 times the estimated cost. Of course, this is just my guess. I'll have to check what "Inside SQL Server" by Kalen Delaney has to say about the meaning of cost when I return to the office.

I've not found any strong correlation between the numbers in the Estimated Query plan and the Time or I/O Statistics of the actual query run. In fact, I think the SQL 7 Query Optimizer has some bugs that produce strange results. Even with strange results, I find the Estimated Query Plan is a valuable tool for optimizing queries.

There are various reasons why Query Optimizer chooses certain plans. The optimizer may estimate that a table scan is less expensive than an index seek. In fact, for small tables a table scan will almost always be less expensive than a non-clustered index scan.

Hope this helps a little. For more info, here is a link to an article about indexes and Query Optimizer.

Not All SQL Server Indexes Are Created Equal

http://www.sql-server-performance.com/indexes_not_equal.asp

Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln