T-SQL Script for SQL Server Statistics Last Updated Time & Percent Rows modified
Posted by Sudarshan Narasimhan on August 4, 2011
I wrote a post on the same topic @ Amit’s Blog. I made some changes to the script to make it easier for DBA’s to read the output. I am re-posting this here for your reference. This can be quite handy if you are managing multiple SQL instances and you have lot of tables or have large databases where you only want to update statistics on some tables based on row modifications (data changes e.g. OLTP). You can always use DBCC SHOW_STATISTICS or SP_AUTOSTATS to find out the same information for a specific table, index or column stats.
select
schemas.name as table_schema,
tbls.name as Object_name,
i.id as Object_id,
i.name as index_name,
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 lastStatsUpdateTime
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
order by modifiedRows desc
Keep in mind that Rowmodctr values displayed in above output 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 info on this.
You can use this script to generate the UPDATE STATISTICS command for each table in your table. I am using FULL SCAN by default, which you can of course modify to use specific SAMPLE. You have to un-comment the comments lines, to actually perform the update.
select identity(int,1,1) as rownum,table_name into table_count from information_schema.tables where table_type=‘base table’
declare @count int,@stmt varchar (255),@maxcount int,@tblname varchar(50)
set @count=1
select @maxcount=count(*) from table_count
while @count < @maxcount+1
begin
select @tblname=table_name from table_count where rownum=@count
set @stmt = ‘UPDATE STATISTICS ‘+ ‘[‘ +@tblname+ ‘]’ + ‘ WITH FULLSCAN’
print @stmt
–PRINT (‘Updating statistics for table :’+@tblname)
–EXEC(@stmt)
–PRINT (‘Finished Updating statistics for table :’+@tblname)
print ”
set @count=@count+1
set @stmt=”
End
drop table table_count
Have fun with your database maintenance
. As always stay tuned for more cool SQL stuff.
The SQL Dude! 
How to check database statistics last updated date/time & Percent Rows modified in SQL Server « TroubleshootingSQL said
[…] T-SQL Script for SQL Server Statistics Last Updated Time & Percent Rows modified « The SQL… said August 4, 2011 at 12:17 am […]
Eunice said
thanks much, this will help!
Jon said
It may seem like an odd request, but I need to find out the date that the various tables in a database were FIRST updated. This is because our production database is purged of older records every few months due to size limitations, and we have a bunch of database backup files that need to be restored in order to perform retrospective queries. Some backups were taken prior to the purge process, and it would be helpful to know how far back in time each backup goes, so I know which one(s) I should restore (based on the dates that need to be queried). Can anyone provide some suggestions that may help?
Sudarshan Narasimhan said
Jon,
If its backup time you’re after you can get that information using RESTORE HEADERONLY and looking at the values for BackupStartDate and BackupFinishDate. But, this doesn’t necessarily tell you if any particular table was modified between this backup and the previous one. It’s very much possible that a table has no changes between 2 backups. Either case there is no way to get such object level information from a backupset without actually restoring the backup and querying the data. Also, SQL Server does not maintain the kind of information that you’re looking for (like First Updated). The only information it tracks is last_updated time (both user and system) and this can be viewed using sys.dm_db_index_usage_stats
select OBJECT_NAME(object_id) as ObjName, last_user_update, last_system_update from sys.dm_db_index_usage_stats
where db_name(database_id) = ‘test’ and OBJECT_NAME(object_id) = ’employees’
I’m not sure if this helps your scenario.