Index Statistics are important for query optimizer to generate optimal query plans. If indexes have outdated or stale statistics, your query’s performance will suffer. 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 update outdated statistics.
In reporting mode, 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
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
Recent Comments