Update Statistics for All Tables and Databases in a SQL Server Instance


By:   |   Updated: 2018-12-24   |   Comments (2)   |   Related: More > Maintenance


Problem

You are a database administrator and have hundreds of SQL Server instances.  One common administration task is to update statistics for your database objects for query optimization, if you have a lot of instances with large tables to work with this can be a time consuming task.  Also, if you are handed new instances, you don’t know the status of the indexes and statistics, so you may want to update them for all tables on all databases with a single script, this is especially important when restoring databases or performing migrations to have up to date statistics to avoid performance issues.

Solution

With the help of the undocumented stored procedure sp_MSforeachdb we can achieve this task with a simple script to improve query performance. The sp_MSforeachdb stored procedure uses a global cursor to issue a Transact-SQL statement against each database on an instance.

Below is the basic usage.  This will iterate over each database and display the database name.  The "current" database is indicated by [?] which gets replaced dynamically with each database name including the system databases.

EXEC sp_MSforeachdb 'USE [?] SELECT ''[?]'' as DBname'

If we execute the query, we will have output like this:

Basic usage of sp_MSforeachdb

Updating Statistics for a SQL Server Database

The second part of the solution is to create a Transact-SQL query to update all statistics for a given database. This is achieved with the use of procedure sp_updatestats. This runs the UPDATE STATISTICS command against all user defined tables in the current database.

The basic usage is:

sp_updatestats

Executing Update Statistics for all SQL Server Databases

Now if we combine these 2 procedures, we can create a script that will update statistics on all tables for all databases on a SQL Server instance.

The basic command will look like this:

sp_MSforeachdb 'use [?]; exec sp_updatestats'

If we execute it, the output will be something like this in SQL Server Management Studio:

Basic sp_updatestats usage

The script was executed against all databases, including the system databases. But what If you want to exclude system databases from the script?

Excluding SQL Server System Databases from the Script

If you want to execute the above script and exclude system databases, some additional code must be put in place. We know that system databases are identified by database ID 1 to 4, as you can see with this simple select query:

SELECT database_id, name
FROM sys.databases

If we execute it, we can see the database id’s belonging to the system databases in SQL Server Management Studio::

Database list from sys.databases

All we need to do is to modify our script to filter those database IDs from the execution. To have cleaner code, I separated the T-SQL for updating the statistics into a parameter, like this:

DECLARE @TSQL nvarchar(2000)

-- Filtering system databases from execution
SET @TSQL = '
IF DB_ID(''?'') > 4
BEGIN
   USE [?]; exec sp_updatestats
END
'
-- Executing TSQL for each database
EXEC sp_MSforeachdb @TSQL

If you execute the query, this will only update databases that have a DB_ID > 4.

Excluding Specific User Databases from the sp_updatestats T-SQL Script

There can be some cases where you want to exclude specific databases from the update, and you can do it by adding just a few lines of code to the script.

We can filter the databases using an IN operator in the IF statement, as follows:

DECLARE @TSQL nvarchar(2000)

-- Filtering system databases and user databases from execution
SET @TSQL = '
IF (DB_ID(''?'') > 4
   AND ''?'' NOT IN(''distribution'',''SSISDB'',''ReportServer'',''ReportServertempdb'')
   )
BEGIN
   PRINT ''********** Rebuilding statistics on database: [?] ************''
   USE [?]; exec sp_updatestats
END
'
-- Executing TSQL for each database
EXEC sp_MSforeachdb @TSQL

As you can see, we use a NOT IN operator to indicate what databases we want to exclude. For this example, we included some of the other system databases that are used for other SQL Server components. We have also included a print statement to show what database are being updated for each iteration.

This is a sample result of the query execution:

Update statistics script execution

More about SQL Server sp_updatestats

According to Microsoft documentation, as we commented earlier, this function executes UPDATE STATISTICS against all user defined and internal tables on a database.

You can also specify the RESAMPLE parameter as well. This is used to update each statistic using its most recent sample rate. Allowed values for this parameter are NO and RESAMPLE, with NO as the default. Using RESAMPLE may cause full table scans for indexes, while using NO will use the most recent sample rate.

Usage of this parameter is:

EXEC sp_updatestats @resample = 'RESAMPLE'

The permissions required to execute sp_updatestats are sysadmin or being a database owner.

The method described above is best suited when you receive the server the first time, running the script frequently can be unnecessary and will only cause high resource consumption.  Although you will notice that statistics will not be updated if not required, if you look at the image above you can see the where it says "did not require update".

Also, please have in mind that updating statistics can cause related queries to recompile.

With AUTO_UPDATE_STATISTICS enabled, statistics are updated automatically by the database engine when row count changes for a column reach a threshold, you can read more about this threshold in the official documentation by Microsoft.

Also Microsoft provides an Adaptive Index and statistics maintenance solution, that can be found here: https://github.com/Microsoft/tigertoolbox/tree/master/AdaptiveIndexDefrag.

Why is it important to update statistics?

Statistics are used by the Query Optimizer to create execution plans based on statistical distribution of required values, the information is stored in BLOBs (binary large objects).  Value cardinality or a histogram is used to determine the number of rows to return, and execution plans are built with this information. Density is the number of duplicated values a column can have, this is used to calculate selectivity and enhance cardinality / histogram estimates. For unique values, the density is 1. High density variations on a given column can lead to parameter sniffing issues. Also, outdated or non-existent statistics can lead to poor performing queries or sub-optimal query plans.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips


Article Last Updated: 2018-12-24

Comments For This Article




Friday, March 19, 2021 - 11:34:53 AM - Allen Back To Top (88445)
I am not certain but my guess is that the two calls to the batch procedure sp_MSforeachdb are conflicting. One needs to end and close all cursors before the next one starts.

Maybe put BEGIN END around each one or put a USE ? between each to isolate them?

Tuesday, July 21, 2020 - 9:00:18 AM - Honey Back To Top (86166)

I don't have limited  privileges on my admin user account and I want to set up the update statistics jobs for all user databases at once. I am trying below code but getting error:

DECLARE @TSQL nvarchar(2000)
-- Filtering system databases and user databases from execution
SET @TSQL = '
IF (DB_ID(''?'') > 4
   AND ''?'' NOT IN(''rdsadmin'',''master'',''msdb'',''temdb'',''model'')
   )
BEGIN
   PRINT ''********** Rebuilding statistics on database: [?] ************''
   USE [?]; exec sp_MSforeachdb "UPDATE statistics ? WITH ALL"
   USE [?]; exec sp_MSforeachdb "UPDATE STATISTICS ? WITH FULLSCAN"
END
'
/
-- Executing TSQL for each database
EXEC sp_MSforeachdb @TSQL

But while running this code I am getting this error:

********** Rebuilding statistics on database: [up] ************
Msg 16915, Level 16, State 1, Line 9
A cursor with the name 'hCForEachDatabase' already exists.
********** Rebuilding statistics on database: [up1] ************
Msg 16915, Level 16, State 1, Line 9
A cursor with the name 'hCForEachDatabase' already exists.

Can you please suggest on how to do that



download














get free sql tips
agree to terms