Last time Index was rebuilt

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