SQL Server: SQL Statistics Object

The SQL Statistics object in Microsoft® SQL Server™ provides counters to monitor compilation and the type of requests sent to SQL Server. Monitoring the number of query compilations and recompilations and the number of batches received by SQL Server give you an indication of how quickly SQL Server is processing user queries and how effectively the query optimizer is processing the queries.

Compilation is a significant part of a query’s turnaround time. The objective of the cache is to reduce compilation by storing compiled queries for later reuse, thus eliminating the need to recompile queries when later executed. However, each unique query needs to be compiled at least once. Query recompilations can be caused by the following factors:

Schema changes, including base schema changes such as adding columns or indexes to a table, or statistics schema changes such as inserting or deleting a significant number of rows from a table.
Environment (SET statement) changes. Changes in session settings such as ANSI_PADDING or ANSI_NULLS can cause a query to be recompiled.

These are the SQL Statistics counters.

SQL Server SQL Statistics counters	Description
Auto-Param Attempts/sec	Number of auto-parameterization attempts per second. Total should be the sum of the failed, safe, and unsafe auto-parameterizations. Auto-parameterization occurs when SQL Server attempts to reuse a cached plan for a previously executed query that is similar, but not exactly the same, as the current query. For more information, see Auto-parameterization.
Batch Requests/sec	Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). High batch requests mean good throughput. For more information, see Batch Processing.
Failed Auto-Params/sec	Number of failed auto-parameterization attempts per second. This should be small.
Safe Auto-Params/sec	Number of safe auto-parameterization attempts per second.
SQL Compilations/sec	Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles due to recompiles. Once SQL Server user activity is stable, this value should reach a steady state.
SQL Re-Compilations/sec	Number of SQL recompiles per second. Counts the number of times recompiles are triggered. In general, you want the recompiles to be low.
Unsafe Auto-Params/sec	Number of unsafe auto-parameterization attempts per second. The table has characteristics that prevent the cached plan from being shared. These are designated as unsafe. The fewer of these that occur the better.

SQL Server: SQL Statistics Object

See Also