Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A Case Study in Performance Tuning Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2005 11:13 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 18, 2012 8:55 AM
Points: 162, Visits: 334
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
Post #155254
Posted Sunday, January 23, 2005 11:14 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 1:25 PM
Points: 6,703, Visits: 1,675
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
Post #157149
Posted Sunday, January 23, 2005 8:17 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 12, 2007 6:32 AM
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

Post #157178
Posted Monday, January 24, 2005 7:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 02, 2013 7:52 AM
Points: 59, Visits: 34

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

 




Post #157236
Posted Monday, January 24, 2005 7:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 18, 2012 8:55 AM
Points: 162, Visits: 334

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
Post #157243
Posted Monday, January 24, 2005 3:53 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 12, 2007 6:32 AM
Points: 62, Visits: 3

Thanks for the followup, and great info!

JT

Post #157380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse