SQL Server, SQL Statistics Object
The SQLServer: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. In order to save the compilation cost, the Database Engine saves the compiled query plan in a query cache. The objective of the cache is to reduce compilation by storing compiled queries for later reuse, therefore ending the requirement to recompile queries when later executed. However, each unique query must be compiled at least one time. 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.
For more information about simple and forced parameterization, see ALTER DATABASE (Transact-SQL) and Simple Parameterization.
These are the SQL Server 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 tries to parameterize a Transact-SQL request by replacing some literals with parameters so that reuse of the resulting cached execution plan across multiple similar-looking requests is possible. Note that auto-parameterizations are also known as simple parameterizations in Microsoft SQL Server 2000. This counter does not include forced parameterizations. |
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. |
Failed Auto-Params/sec |
Number of failed auto-parameterization attempts per second. This should be small. Note that auto-parameterizations are also known as simple parameterizations in SQL Server 2005. |
Forced Parameterizations/sec |
Number of successful forced parameterizations per second. |
Safe Auto-Params/sec |
Number of safe auto-parameterization attempts per second. Safe refers to a determination that a cached execution plan can be shared between different similar-looking Transact-SQL statements. SQL Server makes many auto-parameterization attempts some of which turn out to be safe and others fail. Note that auto-parameterizations are also known as simple parameterizations in SQL Server 2005. This does not include forced parameterizations. |
SQL Attention rate |
Number of attentions per second. An attention is a request by the client to end the currently running request. |
SQL Compilations/sec |
Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles caused by statement-level recompilations in SQL Server 2005. After SQL Server user activity is stable, this value reaches a steady state. |
SQL Re-Compilations/sec |
Number of statement recompiles per second. Counts the number of times statement recompiles are triggered. Generally, you want the recompiles to be low. In SQL Server 2005, recompilations are statement-scoped instead of batch-scoped recompilations in Microsoft SQL Server 2000. Therefore, direct comparison of values of this counter between SQL Server 2005 and earlier versions is not possible. |
Unsafe Auto-Params/sec |
Number of unsafe auto-parameterization attempts per second. For example, the query has some characteristics that prevent the cached plan from being shared. These are designated as unsafe. This does not count the number of forced parameterizations. |
See Also
Reference
Other Resources
Monitoring Resource Usage (System Monitor)