Execute UPDATE STATISTICS for all SQL Server Databases
Written By: Tim Ford -- 10/17/2008
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
Problem
If you're like me, you have a SQL Agent job in place to rebuild or reorganize only the indexes in your databases that truly require such actions. If you rely on the standard maintenance plans in Microsoft SQL Server, a Scorched-Earth policy of rebuilding all indexes occurs. That is whether such actions are required or not for a specific index, a rebuild of the index and all the locking and churning in the logs occurs. That is why so many of us "roll our own" index maintenance solutions as it were. It is also one of my biggest peeves with Microsoft - why was this not built into SQL Server 2008? Ah, at any rate, by only maintaining indexes that are fragmented, statistics updates do not occur globally against the tables/indexes in your databases. What we need is a quick solution for updating all the statistics for every database on our SQL Server instance.
Solution
Before you go ahead and state the fact that you have AUTO_UPDATE_STATISTICS ON for your databases remember that does not mean that they are being updated!
Bah! You say? Well think about this for a second. I've touched upon this in an earlier tip, SQL Server's engine will update the statistic when:
- When data is initially added to an empty table
- The table had > 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records since that collection date
- The table had < 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records + 20% of the row count from the previous statistics collection date
Based upon this criteria, there will be many cases where the underlying data changes in such a way or in such levels that the statistics that exist for an index will not be indicative of the actual data in the database. Because of this you simply can not rely on the engine to keep you statistics in check and current. Here is a simple block of code that will iterate through all your databases in order to build the sp_updatestats command that can then be copied and pasted into a new query window for execution. This code will work with all current and previous versions of SQL Server back through SQL 7.0.
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] NOT IN ('model', 'tempdb')
ORDER BY [name]
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13)
PRINT @SQL
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB |
On my test server this code yields the following results.
The next step is to copy this text, paste it into a query window in SQL Server Management Studio, then execute it against the instance. Alternately you may choose only to execute it against select databases, but that is entirely up to you.
USE [Dummy]
EXEC sp_UpdateStats
USE [master]
EXEC sp_UpdateStats
USE [msdb]
EXEC sp_UpdateStats
USE [MSSQLTips]
EXEC sp_UpdateStats
USE [MSSQLTips_DUPE]
EXEC sp_UpdateStats
USE [Northwind]
EXEC sp_UpdateStats
USE [Sitka]
EXEC sp_UpdateStats
USE [Utility]
EXEC sp_UpdateStats |
I've provided a sample of the output generated by the collection of SQL statements executed above. As you can see, the engine still will review the statistics to see if they warrant updating. It will ignore those that are acceptable and will update only the statistics that require such action. I can assure you that each of the databases in the listing above had AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS both set to ON, yet the following results are indicative of statistics that can become outdated.
Next Steps
- Review earlier tips on the topic of statistics from mssqltips.com
- Run the scripts in your test environment and analyze the results. I suggest doing this against a database with a poorly-executing query to see if you achieve noticeable performance increases after running the update script.
- Subscribe to MSSQLTips.com's daily newsletter for more valuable answers to your SQL Server questions. We're your daily source for SQL Server tips.
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
