|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Comparing Queries via Graphical Query Execution PlanUsing BOL and a couple of other sources I have been learning about the graphical query execution plans available in QA. I understand in general what the graphical plans show, what some of the common icons represetn, and how to read the plans in general. What I am missing is this: What metric do I look for to understand the "total cost of a query". From what I have seen, specific metrics are available *per node* within a given plan to show the given step's cost relative to all other steps in the same query. But I'd like to know what metric or metrics I need to see in order to compare two different query execution plans. I want to be able to say "query 1 is faster than query 2 because of xyz metric"... and I was thinking that I could find this information somehow in the graphical query execution plans - but I haven't seen it if there is such a thing. What am I missing? Thanks. Typically, you'll be able to use two things:
1) the comparitive costs of the queries 2) the comparitive costs of the similar nodes in the queries e.g., using the pubs database, comparing these two queries (from the titleview view) select title, au_ord, au_lname, price, ytd_sales, pub_id from authors, titles, titleauthor where authors.au_id = titleauthor.au_id AND titles.title_id = titleauthor.title_id select title, au_ord, au_lname, price, ytd_sales, pub_id from authors, titles, titleauthor where authors.au_id = titleauthor.au_id AND titles.title_id = titleauthor.title_id and authors.au_id='213-46-8915' you'll see the 1st query is ~74% and the 2nd is ~27% of the total. more importantly, you'll see that the authors table in each has a significant cost and index usage difference between the two queries. in the 1st query, an index scan is used, but in the 2nd a clustered index seek is used. this ist the significant info. [note: the relative cost percentages go up for the other two tables in the 2nd query only because the relative cost of the authors table went down] Jeff wrote: Show quote > Using SQL Server 2000 / QA: > > Using BOL and a couple of other sources I have been learning about the > graphical query execution plans available in QA. I understand in general > what the graphical plans show, what some of the common icons represetn, and > how to read the plans in general. > > What I am missing is this: What metric do I look for to understand the > "total cost of a query". > > From what I have seen, specific metrics are available *per node* within a > given plan to show the given step's cost relative to all other steps in the > same query. But I'd like to know what metric or metrics I need to see in > order to compare two different query execution plans. > > I want to be able to say "query 1 is faster than query 2 because of xyz > metric"... and I was thinking that I could find this information somehow in > the graphical query execution plans - but I haven't seen it if there is such > a thing. > > What am I missing? > > > Thanks. > > Thank you Trey...
So, if I'm understanding you correctly, then there is NOT some single number I can look to... like Query1's AllTellingCostMetric = 8 and Query2's AllTellingCostMetric = 4, therefore Query2 is twice as fast as Query1. Correct? (such a metric does not exist?) So the best I/we can do is *relative* comparisons - both between AND within query execution plans? And yes, I understand there's nothing that can be as definitive as actually executing the queries repeatedly in a controlled testing effort.... but this question is specifically about what I can learn in a predictive capacity from the graphical query execution plans in QA. ....just trying to make sure I understand what I need to be focusing on as I learn this stuff. Thanks... -Jeff Show quote "Trey Walpole" <treypole@newsgroups.nospam> wrote in message news:uykFVzR$FHA.3676@tk2msftngp13.phx.gbl... > Typically, you'll be able to use two things: > > 1) the comparitive costs of the queries > 2) the comparitive costs of the similar nodes in the queries > > e.g., using the pubs database, comparing these two queries (from the > titleview view) > > select title, au_ord, au_lname, price, ytd_sales, pub_id > from authors, titles, titleauthor > where authors.au_id = titleauthor.au_id > AND titles.title_id = titleauthor.title_id > > select title, au_ord, au_lname, price, ytd_sales, pub_id > from authors, titles, titleauthor > where authors.au_id = titleauthor.au_id > AND titles.title_id = titleauthor.title_id > and authors.au_id='213-46-8915' > > you'll see the 1st query is ~74% and the 2nd is ~27% of the total. > more importantly, you'll see that the authors table in each has a > significant cost and index usage difference between the two queries. > in the 1st query, an index scan is used, but in the 2nd a clustered index > seek is used. this ist the significant info. > [note: the relative cost percentages go up for the other two tables in the > 2nd query only because the relative cost of the authors table went down] > > Jeff wrote: >> Using SQL Server 2000 / QA: >> >> Using BOL and a couple of other sources I have been learning about the >> graphical query execution plans available in QA. I understand in general >> what the graphical plans show, what some of the common icons represetn, >> and how to read the plans in general. >> >> What I am missing is this: What metric do I look for to understand the >> "total cost of a query". >> >> From what I have seen, specific metrics are available *per node* within a >> given plan to show the given step's cost relative to all other steps in >> the same query. But I'd like to know what metric or metrics I need to see >> in order to compare two different query execution plans. >> >> I want to be able to say "query 1 is faster than query 2 because of xyz >> metric"... and I was thinking that I could find this information somehow >> in the graphical query execution plans - but I haven't seen it if there >> is such a thing. >> >> What am I missing? >> >> >> Thanks. Yes, Einstein may have been right about one thing... In SQL everything is
relative. :) ML --- http://milambda.blogspot.com/ Jeff (Jeff@NoSpam.com) writes:
> Using BOL and a couple of other sources I have been learning about the You run the query and note the wallclock time. (Typically:> graphical query execution plans available in QA. I understand in general > what the graphical plans show, what some of the common icons represetn, > and how to read the plans in general. > > What I am missing is this: What metric do I look for to understand the > "total cost of a query". DECLARE @d datetime SELECT @d = getdate() -- query comes here SELECT datediff(ms, @d, getdate()) Preferrably this should be performed on a server that is otherwise idle, as else it is difficult to compare queries. Also keep in mind that the first access may be from disk, successive accesses are from cache. Anyway, the point here is what you see in QA are estimates, and the estimates for the different steps may be of interest, as it may tell where the optimizer went wrong, if it did. So any metric for the entire query would be a limited interest. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics