Update Statistics for all databases in SQL Server


June 30, 2013

Introduction

In some case you can improve the query performance by executing UPDATE STATISTICS or the stored procedure SP_UPDATESTATS to update statistics more frequently than the default updates which are triggered by SQL Server Query Optimizer.

This article includes a script which one can run to generate the TSQL code to update all statistics across all user databases. The script works on all previous and current SQL Server versions starting SQL Server 2000.

Highly Recommended:- "SQL Update Statistics" is a Free Tool which can be used to Identify out-of-date SQL Server table and index statistics across the database.

Newsletter Signup

Important Note: - Running update statistics causes SQL Queries to recompile. Hence is it recommended not to run UPDATE STATISTICS or SP_UPDATESTATS stored procedure very frequently as there can be performance tradeoff between improving query plan and the time it takes to recompile queries.

Script to Update Statistics for all databases in SQL Server

DECLARE @TSQLScript VARCHAR(2000)
DECLARE @Databases SYSNAME

DECLARE cUpdateStatistics CURSOR READ_ONLY FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] NOT IN ('master','msdb','model', 'tempdb')
ORDER BY [name]

OPEN cUpdateStatistics
FETCH NEXT FROM cUpdateStatistics INTO @Databases
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TSQLScript = 'USE [' + @Databases +']' + ';' + CHAR(13) + 'EXEC sp_updatestats' + ';' + CHAR(13)
PRINT @TSQLScript
FETCH NEXT FROM cUpdateStatistics INTO @Databases
END

CLOSE cUpdateStatistics
DEALLOCATE cUpdateStatistics

 Result Generated from the above Query Execution

On my server once the above script is executes it generated the below result.

USE [AdventureWorks2012];
EXEC sp_updatestats;

USE [AdventureWorksLT2012];
EXEC sp_updatestats;

USE [MyTechMantra];
EXEC sp_updatestats;

Final step will be to copy and paste the above text to a New Query window in SQL Server Management Studio and press F5 to execute the code.

Script to update statistics on all databases in SQL Server

In the below snippet you could see that SQL Server Engine will review and will update only those statistics which require an update. All database on the server had AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS option set to ON still you see that statistics have been outdated for some of them.

Update Statistics in SQL Server

Conclusion

It is recommended not to run UPDATE STATISTICS or SP_UPDATESTATS stored procedure very frequently on production databases as this will result in query recompilation there by reducing performance on occasions.

Share this Article

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."