Well, here goes my first post – I will keep it simple. As a DBA you’ve probably heard people talking about statistics ALL the time. You probably have jobs/maintenance plans setup to update statistics. But if you ever ran into an issue with slow query performance and wanted to do a quick check on the statistics for ALL indexes in the database, you would use either dbcc show_statistics or sp_autostats. But they work per index/table. Here is a way to do this for the entire database using TSQL query,
— For SQL 2005/SQL 2008
select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time
from sys.sysindexes as a
inner join sys.objects as b
on a.id = b.object_id
where b.type = 'U'
Note: Rowmodctr values displayed above are not entirely reliable starting with SQL 2005 onwards. But, you can rely on last updated time to make a decision if the statistics need to be updated.
Read here for more
Many of you might have lot of tables or have large databases where you only want to update statistics using ‘UPDATE STATISTICS’ or ‘sp_updatestats’, but only for those tables that have large row modifications. Here is a script that you can use to get the output of each index that has significant row modifications. You can pipe this to a temp table and choose to update statistics on only these tables. You can do this by looking at the “ModifiedPercent” column.
select
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
— For SQL 2000
SELECT 'Index Name' = i.name, 'Table Name' = o.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
where o.id = i.id
You can put this in a cursor for every database if you want to and play around with it. Will post back another script which will help you decide what criteria you want to pick when updating indexes. Stay tuned….
Addition: February 4, 2012: An updated version of the script is available here.
TroubleshootingSQL 