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

Global SQL Metrics

When performing SQL workload analysis, there are not many global SQL metrics to examine. However, the ones SQL Server makes available certainly deserve a cursory review. The global_sql query below will easily pick these up:

* global_sql

-- Script is available in the Online Code Depot

The main two metrics of interest are failed auto-params and SQL re-compilations. Auto-parameterization occurs when an instance of SQL Server attempts to reuse a cached plan for a previously executed query similar to, but not the same as, the current query. The failed auto-param metric shows the number of failed auto-parameterization attempts.

SQL Serverís ability to match new SQL statements with existing, unused execution plans increases when parameters or parameter markers are used in T-SQL statements. If a SQL statement is executed without parameters, SQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan.

A small number for this statistic shows SQL Server is efficiently reusing existing cached plans. The ability of SQL Server to match complex SQL statements to existing, unused execution plans, can be increased by explicitly specifying the parameters using either sp_executesql or parameter markers in the T-SQL code. Doing this helps lower the number of failed auto-parameterizations.

The SQL re-compilations statistic represents the total number of recompiles triggered in a SQL Server instance. Recompiles occur when SQL Server determines the currently defined execution plan for an executing stored procedure might no longer be the best possible plan. SQL Server pauses the query execution and recompiles the stored procedure.

Recompiles slow down the process executing the procedure and increase the load on the CPU. Further, the procedure will place a compile lock on the objects referenced by the code, which may increase the risk of lock contention on the system.

By extension, the more recompiles occurring on the system, the more overall load increases resulting in poor performance. In general, the number of recompiles should be kept low. The most common reasons SQL Server would issue a recompile are:

  • Running sp_recompile against any table referenced in the stored procedure.

  • Significant data changes in a referenced table.

  • Schema changes to referenced objects.

  • The use of the WITH RECOMPILE clause in the CREATE PROCEDURE or EXECUTEstatement when a plan is no longer available in the system cache.

  • Use of tempoary objects in certain circumstances.

  • Setting certain options to a non-default setting (OFF). These include ansi_defaults, ansi_nulls, ansi_padding, ansi_warnings, and concat_null_yeilds_null.

The DBA can locate stored procedures causing recompiles by setting traces either through SQL Profiler or server side traces and can catch the Stored Procedures SP:Recompile event.

The individual SQL statements causing the recompile can sometimes be discovered by collecting the SP:StmtStarting and SP:StmtCompleted events. SQL statements appearing immediately before and after the recompile event are the culprits.


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