Last time Index was rebuilt

06 September,2013 by Jack Vamvas

Question: How can I find the last time an Index was built on a SQL Server object?

Answer: There isn’t a specific column displaying the date and time of the last index rebuild on a SQL Server. But there are methods to gather enough information , to establish and index rebuild event and time.

Method 1: Query the sys.indexes view and investigate the STATS_DATE function. The STATS_DATE function Returns the date of the most recent update for statistics on a table or indexed view.  When an index is rebuilt , by default , the index statistics are updated.   A big clue can be gathered from the last time statistics were updated on an index

 

SELECT name AS Stats,
STATS_DATE(object_id, stats_id) AS LastStatsUpdate
FROM sys.stats
order by LastStatsUpdate desc;

 

Method 2: Query the default trace and use  the Object:Altered Event Class. The Object:Altered Event Class documents an object being altered. Example: ALTER DATABASE and ALTER INDEX. It is possible, using the information from the Object:Altered Event Class , found in the default trace – to gather clues and assist in establishing if indexes were rebuilt .

Note: This method is not conclusive and extra checking is required.

 

SELECT  TextData, LoginName, StartTime, DatabaseID, OBJECT_NAME(ObjectID), IndexID
FROM ::fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1

                                                              f.[value]

                                                      FROM    sys.fn_trace_getinfo(NULL) f

                                                      WHERE   f.property = 2

                                                    )), DEFAULT) tf
INNER JOIN sys.trace_events te
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS tc
ON te.category_id = tc.category_id
WHERE databasename = 'CM_PS2' AND
objectname IS NOT NULL AND 
te.category_id = 5 AND 
te.trace_event_id = 164

 Read More

ALTER INDEX permissions - SQL Server DBA

SQL Server - Find last time STATISTICS updated - update statistics

SQL Server - Index missing Statistics - SQL Server DBA

SQL Server – default trace FAQ - SQL Server DBA

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Last time Index was rebuilt


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer