The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

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 maintenanceSmile. As always stay tuned for more cool SQL stuff.

Advertisements

4 Responses to “T-SQL Script for SQL Server Statistics Last Updated Time & Percent Rows modified”

  1. […] T-SQL Script for SQL Server Statistics Last Updated Time & Percent Rows modified « The SQL… said August 4, 2011 at 12:17 am […]

  2. Eunice said

    thanks much, this will help!

  3. 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?

    • 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: