Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Robin Schumacher

Obtain a Baseline

Before commencing any optimization attempts, the DBA should obtain a performance baseline that provides the execution metrics of the query or stored code. At a minimum, elapsed execution time, CPU utilization, and I/O resource usage should be understood.

The DBA may already have such metrics if SQL was identified from trace activities, but if not, these statistics are easy to get. All one is required to do is to turn on the SHOW SERVER TRACE and SHOW CLIENT STATISTICS options of Query Analyzer, run the code, and SQL Server will then give the baseline needed.

Another piece of information important to have, yet often ignored, concerns the number of times SQL or stored code is run on a daily basis. Knowing this information is critical for a couple of reasons.

First, the DBA should not waste valuable time tuning SQL that is seldom run on the server because it is likely to have little impact on overall performance levels.

Next, the DBA should not be fooled by something that appears to run quickly and is run a lot. Taking a query down from two seconds to one will have quite an impact if the SQL is run thousands of times per day on SQL Server.

Unfortunately, this piece of information is not easy to obtain in SQL Server 7 2000 even with SQL Profiler or tracing. In SQL Server 2005, however, a new dynamic management view has been introduced that does track the execution count of routinely executed SQL.


The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter