Check Statistics

This script helps to check when was the last time the statistics were updated for all tables/indexes in all the databases, also provides a table modification rate value (percentage) so then you can perform update stats with fullscan for tables/indexes based on a defined rule (ex. stats for tables/indexes not updated in the last 30 days and/or the modification rate is over 30%) 

select @@ServerName, getdate()

/* **************************************************************** */

use master

go

DECLARE @db sysname,

            @cmd varchar(4000)

SET NOCOUNT ON

IF (object_id( 'tempdb..#DBA_STATISTICS_STATUS' ) IS NOT NULL) DROP TABLE #DBA_STATISTICS_STATUS ;

CREATE TABLE #DBA_STATISTICS_STATUS(

id int identity primary key,

database_name sysname,

table_schema sysname,

table_name sysname,

index_name sysname,

table_id int,

index_id int,

modifiedRows int,

rowcnt int,

ModifiedPercent DECIMAL(18,8),

lastStatsUpdate datetime

)

DECLARE Cursor_Statistics CURSOR FOR

SELECT name FROM sys.databases (NOLOCK)

WHERE NAME not in ('tempdb') and Is_Read_only = 0

order by name

OPEN Cursor_Statistics

FETCH NEXT FROM Cursor_Statistics

INTO @db

WHILE @@FETCH_STATUS = 0

BEGIN

      SELECT @cmd = 'USE ['+@db+'];

Insert into #DBA_STATISTICS_STATUS

select

db_name() as database_name,

schemas.name as table_schema,

tbls.name as table_name,

i.name as index_name,

i.id as table_id,

i.indid as index_id,

i.rowmodctr as modifiedRows,

(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,

convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,

stats_date( i.id, i.indid ) as lastStatsUpdate

from sysindexes i

inner join sysobjects tbls on i.id = tbls.id

inner join sysusers schemas on tbls.uid = schemas.uid

inner join information_schema.tables tl

on tbls.name = tl.table_name

and schemas.name = tl.table_schema

--and tl.table_type=''BASE TABLE''

where 0 < i.indid and i.indid < 255

and table_schema <> ''sys''

and i.rowmodctr <> 0

and i.status not in (8388704,8388672)

and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0

'

    

    

      --PRINT @cmd

      EXEC (@cmd)

      FETCH NEXT FROM Cursor_Statistics

INTO @db

END

CLOSE Cursor_Statistics

DEALLOCATE Cursor_Statistics

SELECT * from #DBA_STATISTICS_STATUS WHERE ModifiedPercent >= 20 or datediff(DD,lastStatsUpdate,getdate()) >= 30

ORDER BY database_name,rowcnt desc

DECLARE @table sysname,

            @schema sysname,

            @index sysname        

DECLARE Cursor_Statistics CURSOR FOR

SELECT database_name,table_schema,table_name,index_name from #DBA_STATISTICS_STATUS WHERE ModifiedPercent >= 20 or datediff(DD,lastStatsUpdate,getdate()) >= 30

ORDER BY database_name,rowcnt desc

OPEN Cursor_Statistics

FETCH NEXT FROM Cursor_Statistics

INTO @db,@schema,@table,@index

WHILE @@FETCH_STATUS = 0

BEGIN

      SELECT @cmd = 'USE ['+@db+'];

      UPDATE STATISTICS '+@schema+'.'+@table+' '+@index+' WITH FULLSCAN;

      '

    

      PRINT @cmd

      EXEC (@cmd)

      PRINT '--'+cast(getdate() as varchar(20))

      FETCH NEXT FROM Cursor_Statistics

      INTO @db,@schema,@table,@index

END

CLOSE Cursor_Statistics

DEALLOCATE Cursor_Statistics

GO

select @@ServerName, getdate()

/* **************************************************************** */

IF (object_id( 'tempdb..#DBA_STATISTICS_STATUS' ) IS NOT NULL) DROP TABLE #DBA_STATISTICS_STATUS ;