Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Case Study in Performance Tuning


A Case Study in Performance Tuning

Author
Message
Gordon Pollokoff
Gordon Pollokoff
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 335
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/gpollokoff/acasestudyinperformancetuning.asp

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7187 Visits: 2679
Nicely done. Exactly the type of situation where a good DBA earns their pay - and proves the value of having one to start with!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
JT Lovell
JT Lovell
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 3

Great case study! 

I'd love to hear more about the steps you followed to perform the server trace when you were analyzing the script.  And also it would be helpful to hear in more detail the process you went through to measure the timing runs. 

It seems that everyone does this a little differently, so if you share it please let me know! 

JT Lovell


davidf
davidf
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 53

ditto to Andy's post. Thanks for sharing your experience.

 





Gordon Pollokoff
Gordon Pollokoff
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 335

JT,

Thanks for the kudos.  

The process for measuring the timing was quite simple.  I created a script that stored the current date/time in a variable, then executed the stored procedure, than calculated the elapsed time once the procedure ended and printed that value out.  To verify the improvement, the script was altered to capture start time, run the procedure, calculate and print elapsed time, add the new index, capture start time, execute and then print elapsed time.

The server trace was used for two purposes.  The first was to identify the bottlenecks in the stored procedure.  These were identified merely by examing the duration of T_Sql events.  The second purpose was to serve as input into the index tuning wizard.

Hope that helps,

Gordon



Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
JT Lovell
JT Lovell
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 3

Thanks for the followup, and great info!

JT


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search