Query tuning

22 Jun 2006 12:38 PM

Justin

How do you guys go about compare the efficiency of two queries?

What I usually do is run the "Display estimated execution plan" in the query
analyser and see each query cost (relative to the batch) and pick one that
gives lower percentage. Is this a good way to compare queries? Am I
missing something just by looking at that number?

Do I actually have to check IO costs, CPU running time, run the profile (and
look for what)?

Correction, tips and suggestion of best practice will be appreciated.
Thanks

22 Jun 2006 12:52 PM

Uri Dimant

Justin

> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?

Yes , sure , as well as looking at EXECUTION PLAN of the both queries

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

Show quote

"Justin" <nospam@nospam.com> wrote in message
news:Oe3YBjflGHA.4244@TK2MSFTNGP02.phx.gbl...
> How do you guys go about compare the efficiency of two queries?
>
> What I usually do is run the "Display estimated execution plan" in the
> query analyser and see each query cost (relative to the batch) and pick
> one that gives lower percentage. Is this a good way to compare queries?
> Am I missing something just by looking at that number?
>
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
>
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>

22 Jun 2006 1:19 PM

Tracy McKibben

Justin wrote:
Show quote

> How do you guys go about compare the efficiency of two queries?
>
> What I usually do is run the "Display estimated execution plan" in the query
> analyser and see each query cost (relative to the batch) and pick one that
> gives lower percentage. Is this a good way to compare queries? Am I
> missing something just by looking at that number?
>
> Do I actually have to check IO costs, CPU running time, run the profile (and
> look for what)?
>
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>
>

The estimated plan is a good place to start, you can identify the most
expensive parts of the query from that. You should also look at the I/O
stats and the actual execution plan. From the I/O stats, you can
identify the tables that are hit the hardest, and focus on potential
indexes, etc for those tables. Looking at the actual execution plan
will help you identify potential new indexes, improved joins, sorts, etc..