Sunday, May 28, 2017
 
SQL Server Script Repository
Facebook Twitter LinkedIn Google+ Addthis
 
     

SQL Script - Update Statistics in SQL Server

By Saleem Hakani on 8/24/2014

Statistics are important for query optimizer to generate optimal query plans. If you have outdated or stale statistics for your index then your query's performance will suffer. Therefore, it is important to update the statistics to make sure query optimizer always generates the best and optimal plan.

This script allows you to report and/or update outdated/stale statistics for either individual indexes or for all indexes in a databases and for all indexes across all user databases.

This script provides the following information:
1. Name of the Database
 2. Table Name
 3. Index Name
 4. Last date when Statistics was Updated
 5. Row Count of the Table
 6. Number of Rows Changed
 7. Percentage of Row Changes Occurred Since Last time Statistics were Updated
 8. Script for you to manually execute Update Statistics on individual objects

 

--Author: Saleem Hakani (Http://sqlcommunity.com)

Create Procedure [dbo].[SQLOPS_UpdateStatistics] (@cmd Varchar(10)=null,@DBName Varchar(255)=null,@Update Varchar(5)='NoFix') as

SET NOCOUNT ON

--Author: Saleem Hakani (Http://sqlcommunity.com)

IF (@cmd is null or @cmd <> 'Execute' or @DBName is null)

Begin

Print 'SQLOPS_UpdateStatistics (Author: Saleem Hakani)'

Print '=========================================='

Print ' '

Print 'This procedure allows you to report and/or update outdated/stale statistics for individual indexes,'

Print 'for all indexes in a databases and for all indexes across all user databases. When generating the report, it provides:'

Print '1. Name of the Database'

Print '2. Table Name'

Print '3. Index Name'

Print '4. Last date when Statistics was Updated'

Print '5. Row Count of the Table'

Print '6. Number of Rows Changed'

Print '7. Percentage of Row Changes Occurred Since Last time Statistics were Updated'

Print '8. Script for you to manually execute Update Statistics on individual objects'

Print ' '

Print 'Syntax: SQLOPS_UpdateStatistics <@Cmd>, <@DBName>, <@Update>'

Print '@Cmd: Can be EXECUTE (To execute this SPROC) or HELP (To obtain this Help)'

Print '@DBName: Can be the name of an individual database or ''ALL'' for all databases'

Print '@Update: Can be ''Fix'' (to Update Statistics) or ''NoFix'' (Just provide the Report)'

Print ' '

Print 'To Get a list of outdated statistics from SQLOPS database:'

Print 'Exec SQLOPS_UpdateStatistics ''Execute'',''SQLOPS'''

Print ' '

Print 'To Get a List and to fix Outdated Statistics from SQLOPS database:'

Print 'Exec SQLOPS_UpdateStatistics ''EXECUTE'',''SQLOPS'',''Fix'''

Print ' '

Print 'To Get a List of Outdated Statistics from all User Databases:'

Print 'Exec SQLOPS_UpdateStatistics ''Execute'',''ALL'''

Print ' '

Print 'To Get a list and to fix Outdated Statistics for all User Databases'

Print 'Exec SQLOPS_UpdateStatistics ''Execute'',''ALL'', ''Fix'''

Return

End

Declare @DBNameALL Varchar(255),

@SQLStr1 NVarchar(4000),

@SQLStr NVarchar(4000),

@ExedbSTR NVARCHAR(4000)

Declare @DBStats Table

(

DBName Varchar(255),

TableName Varchar(255),

IndexName Varchar(255),

Statistics_Last_Update Varchar(30),

ROW_COUNT BigInt,

ROWS_CHANGED BIGINT,

Percent_Changed Decimal(16,2),

Script Varchar(2000)

)

If (@cmd ='Execute') and (@DBName <> 'ALL')

Begin

IF Not Exists (Select Name from Sys.Databases where Name=@DBName)

Begin

Print 'Database ['+Upper(@DBName)+'] Does Not Exist in the System. Please Re-Enter a Valid Database Name'

Return

End

Set @SQLStr='Use ['+@DBName+']; SELECT db_Name() as DB_Name

,SYSSCH.name+''.''+SYSTAB.name AS TableName

, SYSIND.name AS IndexName

, ISNULL(Convert(Varchar,STATS_DATE(SYSIND.id,SYSIND.indid)),''DATE NOT FOUND'') AS ''Statistics_Last_Updated''

, SYSIND.rowcnt AS ''RowCount''

, SYSIND.rowmodctr AS ''Rows_Changed''

, CAST((CAST(SYSIND.rowmodctr AS DECIMAL(28,8))/CAST(SYSIND.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS ''%_Changed''

,''USE [''+DB_Name()+'']; Update Statistics ''+SYSSCH.NAME+''.''+SYSTAB.Name+'' [''+SYSIND.name+''];'' as ''Script''

FROM sys.sysindexes SYSIND

INNER JOIN sys.tables SYSTAB ON SYSTAB.[object_id] = SYSIND.[id]

INNER JOIN sys.schemas SYSSCH ON SYSSCH.[schema_id] = SYSTAB.[schema_id]

WHERE SYSIND.id > 100

AND SYSIND.indid > 0

AND SYSIND.rowcnt >= 500

AND SYSIND.rowmodctr > 0

ORDER BY 7 DESC'

Insert into @DBStats Exec SP_ExecuteSQL @SQLSTR

End

--If All Databases

If (@cmd='Execute' and @DBName = 'ALL')

Begin

Declare CheckDB Cursor for Select Name from Sys.Databases where Name not in ('Master','Model','MSDB','TempDB') and State=0

Open CheckDB

Fetch Next from CheckDB into @DBNameALL

While (@@FETCH_STATUS=0)

Begin

Set @SQLStr1='Use ['+@DBNameALL+']; SELECT db_Name() as DB_Name

,SYSSCH.name+''.''+SYSTAB.name AS TableName

, SYSIND.name AS IndexName

, ISNULL(Convert(Varchar,STATS_DATE(SYSIND.id,SYSIND.indid)),''DATE NOT FOUND'') AS ''Statistics_Last_Updated''

, SYSIND.rowcnt AS ''RowCount''

, SYSIND.rowmodctr AS ''Rows_Changed''

, CAST((CAST(SYSIND.rowmodctr AS DECIMAL(28,8))/CAST(SYSIND.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS ''%_Changed''

,''USE [''+DB_Name()+'']; Update Statistics ''+SYSSCH.NAME+''.''+SYSTAB.Name+'' [''+SYSIND.name+''];'' as ''Script''

FROM sys.sysindexes SYSIND

INNER JOIN sys.tables SYSTAB ON SYSTAB.[object_id] = SYSIND.[id]

INNER JOIN sys.schemas SYSSCH ON SYSSCH.[schema_id] = SYSTAB.[schema_id]

WHERE SYSIND.id > 100

AND SYSIND.indid > 0

AND SYSIND.rowcnt >= 500

AND SYSIND.rowmodctr > 0

ORDER BY 7 DESC'

Insert into @DBStats Exec SP_ExecuteSQL @SQLSTR1

Fetch Next from CheckDB into @DBNameALL

End

Close CheckDB

Deallocate CheckDB

End

If (@Update='Fix')

Begin

If (@DBName<>'ALL')

Begin

Declare CheckDBFix Cursor for Select Script from @DBStats

End

If (@DBName='ALL')

Begin

Declare CheckDBFix Cursor for Select Script from @DBStats

End

Open CheckDBFix

Fetch Next from CheckDBFix into @ExedbSTR

While (@@FETCH_STATUS=0)

Begin

Exec SP_ExecuteSQL @ExedbSTR

Print 'Executed: '+@ExedbSTR

Fetch Next from CheckDBFix into @ExedbSTR

End

Close CheckDBFix

Deallocate CheckDBFix

End

If (@Update <> 'Fix')

Begin

If (Select count(*) from @DBStats) >=1

Begin

Select * from @DBStats order by 7 DESC

Return

End

If (Select count(*) from @DBStats) = 0

Begin

Print 'There are no statistics to be updated.'

Return

End

Return

End

GO

DBCC SHOWCONTIG
Outdated Statistics
SP_UpdateStats
Stale Statistics
Minimize
Script Categories
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM