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