CREATE STATISTICS [hind_1534680565_2A_12A] ON [dbo].[flooractivityitem] ([flooractivityid], [vote]) CREATE STATISTICS [hind_1534680565_12A_2A] ON [dbo]. [flooractivityitem] ([vote], [flooractivityid]) CREATE STATISTICS [hind_1534680565_12A_3A] ON [dbo].[flooractivityitem] ([vote], [legid]) CREATE STATISTICS [hind_1534680565_3A_2A] ON [dbo].[flooractivityitem] ([legid], [flooractivityid]) CREATE STATISTICS [hind_1534680565_2A_3A] ON [dbo]. [flooractivityitem] ([flooractivityid], [legid])Does this suggest that even though Auto Statistics is on, that they are not getting built correctly?
Maintaining indexes and statistics are critical components for high performing SQL Server databases. Based on your question, it sounds as if a window of opportunity is available to perform maintenance. Since a maintenance windows is available, the recommendation is to UPDATE STATISTICS (UPDATE STATISTICS) after rebuilding clustered (Using Clustered Indexes) or nonclustered (Using Nonclustered Indexes) indexes on your tables (CREATE TABLE). Whether DROP INDEX (DROP INDEX) and CREATE INDEX (CREATE INDEX) statements are issued or the DBCC DBREINDEX (DBCC DBREINDEX) command is used, the statistics should be updated. One way to update all of the statistics in a single user defined database is via the system stored procedure sp_updatestats (sp_updatestats).
Keep in mind that additional time will be required for your maintenance, but the benefits should out weigh the time since it is available during a maintenance window which can be scheduled and automated. Do not forget that rebuilding clustered indexes physically changes the layout of the table and typically reduces fragmentation and compacts the database based on the fill factor (Fill Factor). The SQL Server optimizer uses the statistics available to determine the least cost to perform the most efficient query which also makes a case for updating the statistics.
Each user defined database can be configured to have Auto Create Statistics and Auto Update Statistics configured (Database Properties (Options Tab)). A more granular option to control updating statistics is available via the sp_autostats (sp_autostats) system stored procedure. As new tables or indexes are created, when Auto Create Statistics is enabled statistics will be created by SQL Server upon creation and as query plans are determined by the SQL Server optimizer. With Auto Update Statistics enabled, once the statistics exceed an internal parameter in SQL Server, then the associated statistics will be automatically updated. This process could execute during production hours which has the possibility to negatively impact performance.
As such, since you have the maintenance window it is recommended to enable Auto Create Statistics and disable Auto Update Statistics. As new tables and indexes are created add statistics that are anticipated and then SQL Server will create additional indexes as needed, based on enabling Auto Create Statistics. Then during maintenance windows update the associated statistics based on the table and index maintenance that is performed. Good luck!
For More Information
- Dozens more answers to tough SQL Server questions from Greg Robidoux and Jeremy Kadlec are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in May 2004