Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Statistical maintenance functionality (autostats) in SQL Server
Article ID: 195565 - View products that this article applies to.
This article was previously published under Q195565
The newly-introduced statistical maintenance functionality, AutoStat, may generate unwanted overhead on a production system by performing one of the following actions:
For information about autostats in SQL Server 2000, see "Statistics Used by the Query Optimizer in Microsoft SQL Server 2000" at the following MSDN Web site:
Note If you are using Microsoft SQL Server 2005, see the following Microsoft white paper for information about how statistics are used by the query optimizer in SQL Server 2005:
General informationSQL Server makes use of a cost-based optimizer that can be extremely sensitive to statistical information that is provided on tables and indexes. Without correct and up-to-date statistical information, SQL Server can be challenged to determine the best execution plan for a particular query.
Statistics maintained on each table in SQL Server to aid the optimizer in cost-based decision making include the:
To maintain the statistical information in an as up-to-date fashion as possible, SQL Server introduces AutoStat, which, through SQL Server's monitoring of table modifications, is capable of automatically updating the statistics for a table when a certain change threshold has been reached. Additionally, SQL Server introduces auto-create-statistics, which causes the server to automatically generate all statistics required for the accurate optimization of a specific query.
Determining when AutoStat generation is imminentAs mentioned above, AutoStat will automatically update the statistics for a particular table when a "change threshold" has been reached. The sysindexes.rowmodctr column maintains a running total of all modifications to a table that, over time, can adversely affect the query processor's decision making process. This counter is updated each time any of the following events occurs:
After table statistics have been updated, the rowmodctr value is reset to 0 and the table's statistics schema version is updated.
Further, in situations in which a stored procedure's execution plan is taken from cache and that plan is sensitive to statistics, the statistics schema version will be compared to the current version. If there are new statistics available, the plan for the stored procedure will be recompiled.
The basic algorithm for auto update statistics is:
NOTE: In addition to cardinality, the selectivity of the predicate also affects AutoStats generation. This means that statistics may not be updated afer every 500 modifications if cardinality were < 500 or for every 20% of changes if cardinality were > 500. A scale up factor (value ranges from 1 to 4, 1 and 4 inclusive) is generated depending on the selectivity and a product of this factor and the number of changes as obtained from the algorithm would be the actual number of modifications required for AutoStats generation.
The above algorithm can be summarised in the form of a table:
The following are two examples to help demonstrate this concept:
_________________________________________________________________________________ Table Type | Empty Condition | Threshold When Empty |Threshold When Not Empty _________________________________________________________________________________ Permanent | < 500 rows | # of Changes >= 500 | # of Changes >= 500 + (20% of Cardinality) ___________________________________________________________________________ Temporary | < 6 rows | # of Changes >= 6 | # of Changes >= 500 + (20% of Cardinality) ___________________________________________________________________________ Table Variables | Change in cardinality does not affect AutoStats generation. ___________________________________________________________________________
Example 1Consider the authors table in the pubs database, which contains 23 rows and has two indexes. The unique clustered index, UPKCL_auidind, is indexed on one column, au_id, and a composite nonclustered index, aunmind, has been created on the au_lname and au_fname columns. Because this table contains fewer than 500 rows, AutoStat will begin after 500 changes to the table data have occurred. The changes can be one of 500 or more inserts, deletes, changes to an indexed column such as au_lname, or any combination thereof.
You can, therefore, predict when UPDATE STATISTICS will be initiated by monitoring the sysindexes.rowmodctr value, which will be incremented upon each update. When it reaches or exceeds 500, you can expect UPDATE STATISTICS to be started.
Example 2Consider a second table, t2, that has a cardinality of 1,000. For tables with greater than 500 rows, SQL Server will UPDATE STATISTICS when (500 + 20 percent) changes have been made. Doing the math, 20 percent of 1,000 is 200, so you can expect to see AutoStat start after approximately 700 modifications have been made to the table.
Automating Autostats determinationTo automate the determination of when AutoStat will be run, you can poll the sysindexes table and identify when table modifications are reaching the starting point. The following is a basic algorithm for doing so:
You could later schedule a job to do the following:
Controlling whether UPDATE STATISTICS are run against a tableThe most obvious solution to this question, when AutoStat has proven to be Problematic, is to disable auto statistic generation, thereby leaving the database administrators free to schedule UPDATE STATISTICS during less intrusive times. You can do this either by using the UPDATE STATISTICS statement or the sp_autostats stored procedure. The syntax for the UPDATE STATISTICS statement is:
The syntax for the sp_autostats stored procedure is:
sp_autostats <table_name>, <stats_flag>, <index_name>where <stats_flag> is either "on" or "off".
You can also use sp_dboption to disable the automatic occurrence of UPDATE STATISTICS or CREATE STATISTICS on a per-database level:
sp_dboption <dbname>,'auto update statistics', <on | off>
sp_dboption <dbname>,'auto create statistics', <on | off>
Controlling the number of concurrent UPDATE STATISTICS processesCurrently, short of disabling AutoStat for specific tables, it is not possible to configure the number of automatic UPDATE STATISTICS statements that are being run concurrently (DCR 51539 has been filed for this). The server does, however, limit the number of concurrent UPDATE STATISTICS processes to four per processor.
Determining when Autostats are being runYou can use trace flag 205 to report when a statistics-dependent stored procedure is being recompiled as a result of AutoStat. This trace flag will write the following messages to the error log:
When trace flag 205 is enabled, the following message will also bracket the AutoStat message from 8721 when statistics are updated. The opening message of the bracket can be distinguished by the RowModCnt value, which will be greater than 0. The closing bracket, after the UPDATE STATISTICS, will have a RowModCnt value of 0:
1998-10-15 11:10:51.98 spid9 Recompile issued : ProcName: sp_helpindex
LineNo: 75 StmtNo: 29
For this message, "RowModCnt" is the total number of modifications to the table. "RowModLimit" is the threshold which, when exceeded, results in an UPDATE STATISTICS statement execution for the table.
1998-10-15 11:38:43.68 spid8 Schema Change: Tbl Dbid: 7 Objid:
133575514 RowModCnt: 60500 RowModLimit: 60499
It is also possible to enable trace flag 8721, which will dump information into the error log when AutoStat has been run. The following is an example of the type of message that you can expect to see:
For this message, "Mods" is the total number of modifications to the table. "Bound" is the modification threshold, "Duration" is the amount of time that the UPDATE STATISTICS statement required to complete, and "UpdCount" is the count of updated statistics.
1998-10-14 16:22:13.21 spid13 AUTOSTATS: UPDATED Tbl: [authors]
Rows: 23 Mods: 501 Bound: 500 Duration: 47ms UpdCount: 2
You can also use the SQL Server Profiler to identify when UPDATE STATISTICS statements are being run. To do this, perform the following steps:
Schema locksSQL Server employs two types of schema locks, both of which are taken when it updates the statistics for a table:
You can view these locks by running sp_lock or by selecting from the syslockinfo table.
Sch-S: Schema Stability Lock ---------------------------- This lock ensures that a schema element, such as a table or index, will not be dropped while any session holds a schema stability lock on the schema element. Sch-M-UPD-STATS: Schema Modification Lock ----------------------------------------- This is a non-blocking lock that is used by the system to ensure that only one automatic UPDATE STATISTICS process is run against a table at any given point in time. The sp_lock stored procedure will report this lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M.
Article ID: 195565 - Last Review: November 2, 2007 - Revision: 7.6