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 an instance of SQL Server. Monitoring the number of query compilations and recompilations and the number of batches received by an instance of SQL Server gives 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 must 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 an instance of SQL Server attempts to reuse a cached plan for a previously executed query that is similar to, but not 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. After SQL Server user activity is stable, this value reaches 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. |