A Challenging Question

How does one figure out, I mean compare the results after some changes
have been done to the SQL Server, I mean the method in which the
comparision is done, lets say I have a query I ran it it gave me the
results in 40 seconds , I ran it again this time it took 30 seconds,
then I again ran it it took 35 seconds, I then created an index , this
time the query ran in 30 seconds ... how does one compare such things ,
I mean i need to give stats as too what kind of performace has takern
place ... please helpo , I need to knw as a DBa how would you convince
your maanger that becasue of some changes the performance has improved,
cause when you ask the users they say its ok , we dont see the
differnece & stuff , please help.If you don't see any difference in response times then the change has
probably not improved the performance at all.
If I was trying to ascertain whether a new index made a difference or
not I would look at the query execution plan before & after the index
was created to see if the new index is now getting used or not. I would
also run the query with "statistics io" and "statistics time" on before
& after the index was created to see what real difference it makes to IO
and elapsed time.
*mike hodgson*
http://sqlnerd.blogspot.com
Double_B wrote:

>How does one figure out, I mean compare the results after some changes
>have been done to the SQL Server, I mean the method in which the
>comparision is done, lets say I have a query I ran it it gave me the
>results in 40 seconds , I ran it again this time it took 30 seconds,
>then I again ran it it took 35 seconds, I then created an index , this
>time the query ran in 30 seconds ... how does one compare such things ,
>I mean i need to give stats as too what kind of performace has takern
>place ... please helpo , I need to knw as a DBa how would you convince
>your maanger that becasue of some changes the performance has improved,
>cause when you ask the users they say its ok , we dont see the
>differnece & stuff , please help.
>
>|||But then at times seeing the time the query gets executed also keeps
differing, sometimes a query takes 10 sec , the same may take 12 or 15
secs the next times its executed, ...het does anyone have a document
that really explain you the execution plan & how to read the estimated
time the plan takes & figure out whats the issue & how to tackle it
Thanks,|||If you post the execution plan (& the schema for the tables involved) we
may be able to help you. Response times tend to vary a little due to
unrelated (or at least not directly related) things such as query plan
compilation/recompilation, I/O waits (busy disks), lock & latch waits
(waiting for another connection to release the locks on the indexes you
want to access), other apps hogging CPU cycles, etc.
As for documentation on execution plans, Kalen Delaney wrote an
excellent book about 5 years ago called Inside SQL Server 2000
<http://www.amazon.com/gp/product/07...5Fencoding=UTF8>.
It has a couple good chapters in it on the query processor (Ch15) and
query tuning (Ch16) that may clarify things for you. If not then it's
still a very worthwhile volume to have on your professional bookshelf if
you're serious about SQL Server.
*mike hodgson*
http://sqlnerd.blogspot.com
Double_B wrote:

>But then at times seeing the time the query gets executed also keeps
>differing, sometimes a query takes 10 sec , the same may take 12 or 15
>secs the next times its executed, ...het does anyone have a document
>that really explain you the execution plan & how to read the estimated
>time the plan takes & figure out whats the issue & how to tackle it
>Thanks,
>
>|||Thanks a lot