CODEWITHSUNDEEP
sql-server
Manjot
Manjot

Reputation: 11516

Tsql, know when index rebuild, reorg or updatestatistics was last run on SQL server

Using Tsql, how can i know when Index rebuild, reorg or updatestatistics was last run on SQL server (2000, 2005 or 2008)?

Regards

Upvotes: 3

Views: 29549

Answers (2)

SQLMatt
SQLMatt

Reputation: 31

For SQL Server 2000, the following script may be needed as sys.stats does not exist in SQL 2000: 

DBCC SHOW_STATISTICS(TABLENAME, INDEX_NAME)

Many thanks for your script John!

EDIT: I've been contemplating this script for SQL 2005+ (and have had a need to see the data in TableName, IndexName, LastStatsUpdate format), and I came up with the following script.
I'm sure it needs polishing, so feel free to tweak as necessary.

SELECT OBJECT_NAME(object_id) [TableName], 
       name [IndexName],
       STATS_DATE(object_id, stats_id) [LastStatsUpdate]
FROM sys.stats
WHERE name NOT LIKE '_WA%'
AND STATS_DATE(object_id, stats_id) IS NOT NULL
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
ORDER BY TableName, IndexName

Upvotes: 3

John Sansom
John Sansom

Reputation: 41819

SQL Server does not store this information. You can get an "approximate" estimate based on the date the last time the statistics were updated for a given Index as a REBUILD operation will also update the statistics for the Index.

Here is an example using the AdventureWorks database:

USE AdventureWorks;
GO

SELECT name AS Stats,
STATS_DATE(object_id, stats_id) AS LastStatsUpdate
FROM sys.stats
WHERE object_id = OBJECT_ID('Sales.SalesOrderDetail')
and left(name,4)!='_WA_';

GO

You may want to consider using one of the many freely available Index Tuning maintenance scripts for SQL Server. These provide a means to store the details of the Index operations that are performed on a given database and thereby provide information as to when an Index was last rebuilt.

Take a look at the excellent Index Maintenance Script written by Michelle Ufford

Upvotes: 11

Related Questions