Sql-server – how to check the last time dbreindex or an index defrag was executed

sql serversql-server-2008

Is there a quick tsql I can use to check when was the last time a dbreindex, index defrag and update stats was executed on the database?

Appreciate everyone's quick response.

Best Answer

Try one of the queries below and see if that is what you are looking for:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_system_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'YourDb')
AND OBJECT_ID=OBJECT_ID('YourTable')


select name as index_name,
         stats_date( object_id, index_id) as stats_update_date
from  sys.indexes
where object_id = object_id('YourTable');


Select * 

From sys.dm_db_index_usage_stats

Related Solutions

Sql-server – Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts

Some info, if not a definitive answer

It's been blogged recently

When are Statistics Updated – What triggers Statistics to Update
Comment from Paul Randal

There is a whitepaper too. See the section "Maintaining Statistics in SQL Server 2008" where there are some conditions that sound like affect you. Example:

One limitation of the automatic update logic is that it tracks changes to columns in the statistics, but not changes to columns in the predicate. If there are many changes to the columns used in predicates of filtered statistics, consider using manual updates to keep up with the changes.

At the end there are some settings to check too: what if OFF at the DB level which overrides an ON at the index/stat level?

HTH...

Sql-server – How to check when statistics was last executed

For your edit - You need to enclose table name into single quotes:

EXEC sp_autostats 'tablename'

and for the original question

First - find the statistics you want to check:

choose statistics

Second - see its properties, and there you will see the last updated timestamp:

enter image description here

Or you may want to execute the following query:

SELECT t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated 
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'

Best Answer

Related Solutions

Sql-server – Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts

Sql-server – How to check when statistics was last executed

Related Question