Q

Rebuilt statistics with rebuilt indexes?

This Content Component encountered an error
When I rebuild my indexes with DBCC DBREINDEX, does SQL Server also rebuild my statistics? When I run the index wizard, rarely does it suggest that I build more indexes, but it always suggests creating statistics.
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

This was first published in May 2004

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close