| |
 |
|
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
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
