Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Execute UPDATE STATISTICS for all SQL Server Databases


By:   |   Read Comments (8)   |   Related Tips: More > Maintenance

Attend a SQL Server Conference for FREE >> click to learn more


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 
   
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



Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, March 09, 2016 - 4:01:55 AM - pardha Back To Top

 Hi I want the output like below for all databases with including table names. could you please provide the script how to do that.

 

 

USE [AdventureWorks2012]

 

 

UPDATE STATISTICS AdventureWorks2012.Production.ScrapReason WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012.HumanResources.Shift WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012..Production.ProductCategory WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012Purchasing.ShipMethod WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012Production.ProductCostHistory WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012Production.ProductDescription WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012Sales.ShoppingCartItem WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012Production.ProductDocument WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012dbo.DatabaseLog WITH FULLSCAN


Thursday, August 21, 2014 - 4:52:40 AM - Behamooz.com Back To Top

it is better

SELECT 'USE [' + name +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13)   FROM sys.databases AS d


Wednesday, July 16, 2014 - 11:45:57 AM - Joerg Hencke Back To Top

Hi,

thanks for that clear article. I just saw 2 typos ('

Correct should be:

"---

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

---"

 


Thursday, January 31, 2013 - 1:42:35 PM - EuniceRH Back To Top

does this apply to SQL Server 2008 R2 Express as well?


Tuesday, October 09, 2012 - 9:33:34 AM - Greg Robidoux Back To Top

@Dinesh - if you delete a most of the data then you should also rebuild your indexes which will take care of updating statistics.

Look at tips regarding ALTER INDEX...REBUILD

 


Monday, October 08, 2012 - 11:45:01 PM - Dinesh Back To Top

There is one table who having 554535 rows data.I had delete 3lAKH DATA then it required ??

EXEC sp_updatestats database ??


Sunday, October 19, 2008 - 5:15:16 AM - timmer26 Back To Top

You are correct, this is a much more streamlined approach, I wanted to stick to documented processes for this tip however.  I provided a tip earlier this year on sp_MSforeachdb matter of fact.  Both work well though Jerry.  You may want to check the results though.  You may find that, though you thought you excluded the system databases, that they were indeed NOT excluded.


Friday, October 17, 2008 - 7:43:25 AM - jerryhung Back To Top

 This is what I use, simpler too

 

sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'') use ? exec sp_updatestats'


Learn more about SQL Server tools