Browse: Wetory / Scripting library / Update statistics for all databases

SQL Server - Update statistics for all databases

by Tomáš RybnickýNo Comments

Easily update statistics with one line function. I know that you are maintaining statistics by some maintenance jobs scheduled to run on regular basis. You can use builtin Maintenance plans or Olla Hallengrens’s stored procedures or something you built.

Anyway sometime I found this quickie useful when migrating databases to different version of SQL Server. Once you move databases to different instance it is always good to rebuild indexes and update statistics…So it is up to you if you have some maintenance in place or you will use this simple script.

TIP: You can specify @resample parameter of stored procedure sp_updatestats. If ‘resample’ is not specified, sp_updatestats updates statistics by using the default sampling.

WARNING: Keep in mind that it will take some time to complete statistics update for bigger databases so plan you actions properly

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_updatestats'

Results

Script messages after execution:

Updating [Sales].[SalesOrderHeader]
    [PK_SalesOrderHeader_SalesOrderID], update is not necessary...
    [AK_SalesOrderHeader_rowguid], update is not necessary...
    [AK_SalesOrderHeader_SalesOrderNumber], update is not necessary...
    [IX_SalesOrderHeader_CustomerID], update is not necessary...
    [IX_SalesOrderHeader_SalesPersonID], update is not necessary...
    [_WA_Sys_0000000E_72910220], update is not necessary...
    [_WA_Sys_0000000F_72910220], update is not necessary...
    [_WA_Sys_00000011_72910220], update is not necessary...
    [_WA_Sys_00000013_72910220], update is not necessary...
    [_WA_Sys_00000010_72910220], update is not necessary...
    [_WA_Sys_0000000D_72910220], update is not necessary...
    0 index(es)/statistic(s) have been updated, 11 did not require update.
 
Updating [sys].[queue_messages_1977058079]
    [queue_clustered_index], update is not necessary...
    [queue_secondary_index], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[queue_messages_2009058193]
    [queue_clustered_index], update is not necessary...
    [queue_secondary_index], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[queue_messages_2041058307]
    [queue_clustered_index], update is not necessary...
    [queue_secondary_index], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[filestream_tombstone_2073058421]
    [FSTSClusIdx], update is not necessary...
    [FSTSNCIdx], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[syscommittab]
    [ci_commit_ts], update is not necessary...
    [si_xdes_id], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [HumanResources].[EmployeeDepartmentHistory]
    [PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID], update is not necessary...
    [IX_EmployeeDepartmentHistory_DepartmentID], update is not necessary...
    [IX_EmployeeDepartmentHistory_ShiftID], update is not necessary...
    0 index(es)/statistic(s) have been updated, 3 did not require update.
 
Updating [sys].[filetable_updates_2105058535]
    [FFtUpdateIdx], update is not necessary...
    0 index(es)/statistic(s) have been updated, 1 did not require update.
 
Statistics for all tables have been updated.

Leave a Reply