Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


  SQL Server Tips by Burleson

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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter