Problem
Updating statistics is valuable for ensuring the SQL Server optimizer has the current statistical information to most efficiently process the query results. As a best practice, the UPDATE STATISTICS command should be issued on a regular basis to provide SQL Server with the most recent data. Automatically updating statistics is possible with the ‘Auto Update Statistics’ database configuration, but this could cause performance issues if a large table’s statistics are updated during the middle of the day. To prevent this problem, the ‘Auto Update Statistics’ option can be disabled on a per database basis. This paradigm creates the need to rebuild all of the statistics in a manual manner. Does a happy medium exist to let SQL Server automatically manage the statistics on small tables and let me manage the statistics on the larger tables?
Solution
The simple answer is ‘yes’. SQL Server statistics can be configured on a per database basis with the ability to not automatically re-compute indexes on larger tables with the NORECOMPUTE option of the UPDATE STATISTICS command. So let’s break down the configurations and commands to make this a reality.
Where can I find the auto update statistics option?
| SQL Server 2000 – Database Properties |
 |
| SQL Server 2005 – Database Properties |
 |
How can I incorporate the NORECOMPUTE option of the UPDATE STATISTICS command in my code?
| SQL Server 2000 | SQL Server 2005 |
| UPDATE STATISTICS dbo.Authors WITH NORECOMPUTE GO | UPDATE STATISTICS dbo.Customer WITH NORECOMPUTE; GO |
Are any commands available to update the statistics in a simple manner?
| SQL Server 2000 | SQL Server 2005 |
| master.dbo.sp_updatestats | master.sys.sp_updatestats |
How can I re-enable Auto Update Statistics once I have used the NORECOMPUTE option?
| SQL Server 2000 | SQL Server 2005 |
| master.dbo.sp_autostats | master.sys.sp_autostats |
Next Steps
- Review the ‘Auto Update Statistics’ option for your database to determine if the configurations are as expected.
- Determine how statistics are currently being updated and how to best handle this portion of the SQL Server maintenance process.
- If you are not currently performing SQL Server maintenance including updating the statistics, then take the time to build a maintenance process for your databases.
- If you have Auto Update Statistics enabled on all of your large databases and are not experiencing any issues consider re-evaluating this approach and use the NORECOMPUTE option of the UPDATE STATISTICS command for the large tables and let SQL Server maintain the statistics for the small tables.
- Thank you to Armando from the MSSQLTips.com community for sharing this tip idea. If you would like to share tips of your own, please email your ideas to tips@mssqltips.com.
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.
