Re: Interpreting the Estimated Execution Plan
From: Eric Sabine (mopar41_at____ho_y_tmail.ScPoAmM)
Date: 04/21/04
- Next message: Hari: "Re: BCP Utility"
- Previous message: Tibor Karaszi: "Re: Table-Valued function slower than In-Line Function??"
- In reply to: Sky Fly: "Interpreting the Estimated Execution Plan"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 21 Apr 2004 08:42:13 -0400
IMO, where the execution plan is most helpful is when you are comparing 2
alternate ways of solving the problem at hand. You pit the two separate
statements against each other and find which costs less. In the execution
plan for each individual statements, I _try_ to strive for equal cost among
the higher cost items, e.g., if 2 elements rank as 50% and 10%, I see what I
can do to improve the 50% item to get as close to 30-30 as possible.
Basically, I try to optimize each separate statement as much as possible and
then compare the overall result. Sometimes you can improve it and sometimes
you can't. If your situation won't be harmed by adding indexes, then you
add the appropriate indexes. Yes, a seek is better than a scan but you
can't always get away from a scan. Even if you have the most perfect index,
SS may _still_ decide to perform a scan. This is based on the amount of
data it believes it will have to retrieve. Basically, its like if you're
looking for a topic in a book and the index page indicates it's so spread
out throughout the book, it might actually cost you less time to scan every
page than to keep looking in the index, find the [bookmark], lookup the
page, go back to the index, etc.
hth
Eric
"Sky Fly" <nobody@blackhole.com> wrote in message
news:c65l2o$8ad6j$1@ID-18325.news.uni-berlin.de...
> Hello,
>
> I'm trying to improve the performance of a query, and one
> of the tools I'm turning to in this regard is the Estimated
> Execution Plan shown in Query Analyzer. I have the following
> questions:
>
> 1. I notice that each of the icons shown in the plan has a
> 'cost' associated with it, which is expressed as a percentage
> and is shown in a label that you can see if you move the mouse
> over the icon. If an icon has a high cost, does it mean that
> I have to improve performance on the table that the icon is
> associated with?
>
> 2. The icon labels have different captions - like 'Index Seek',
> 'Constant Scan' or 'Table Scan'. I guess that a 'Table Scan'
> is potentially bad news, especially if the table that is being
> scanned is very large, so this would tell me that I should
> look at putting an index on the table. Are there other similar
> interpretations I can make from the various other captions
> to improve my performance?
>
> TIA,
>
> --
> Akin
>
> aknak at aksoto dot idps dot co dot uk
>
>
- Next message: Hari: "Re: BCP Utility"
- Previous message: Tibor Karaszi: "Re: Table-Valued function slower than In-Line Function??"
- In reply to: Sky Fly: "Interpreting the Estimated Execution Plan"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
