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

SQL Statement Analysis

While it might not be true that 80% or more of a database server’s performance is derived from the code that runs inside it with physical design as the number one contributor to performance, one or more bad SQL statements can certainly affect SQL Server’s speed at servicing requests. Therefore, it is important the DBA stay on top of what SQL is issued on the servers and be very adept at ferreting out problematic SQL statements and stored procedures.

Primary attributes used when determining whether or not a particular set of SQL or a stored procedure is worth looking into as well as tuning have already been covered. So, the question is, “How does the DBA find such code?” Unlike some database engines such as SQL Server, SQL Server 7 and 2000 do not have dynamic query views that contain issued SQL statements and their execution statistics.

In SQL Server 2005, a new view, sys.dm_exec_query_stats appears to contain performance execution statistics for issued SQL. As of this writing, it appears the best way to get usable information is to issue a query such as the following sql_stats_2005.sql script:

* sql_stats_2005.sql

-- Script is available in the Online Code Depot

The DBA may not see every query issued against the server in the above query because only cached statements and statistics will be shown. Therefore, whether using SQL Server 7, 2000 or 2005, the one concrete way of capturing code executed against the server is through the use of traces.


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