Bert Swope
To check when an index was last rebuilt in SQL Server, you can query the system catalog views. Specifically, you can use the sys.dm_db_index_physical_stats dynamic management function along with the sys.indexes and sys.objects views. Here’s an example query to retrieve the last rebuild date for an index:
SELECT
OBJECT_NAME(s.[object_id]) AS [Table],
i.[name] AS [Index],
s.[index_id],
s.[avg_fragmentation_in_percent],
s.[fragment_count],
s.[last_updated]
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) AS s
INNER JOIN
sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.[index_id] = i.[index_id]
INNER JOIN
sys.objects AS o ON s.[object_id] = o.[object_id]
WHERE
o.[type] = ‘U’ — Only user tables
AND i.[type] = 2 — Only nonclustered indexes
AND i.[name] = ‘YourIndexName’ — Replace with the actual index name
In the above query, replace ‘YourIndexName’ with the name of the index you want to check. The query joins the necessary catalog views and filters for user tables and nonclustered indexes. It retrieves information such as the table name, index name, index ID, average fragmentation percentage, fragment count, and the last updated date.
The last_updated column will provide you with the date and time when the index was last rebuilt.
Alternative SQL to pull the index build date
Example SQL
SELECT name Stats,
cast(STATS_DATE(object_id, stats_id) as date) Index_date
FROM sys.stats
WHERE object_id = OBJECT_ID(‘dbo.HospitalAdmissionFact’)
And cast(STATS_DATE(object_id, stats_id) as date) = cast(GETDATE() as Date)
Order by Index_date DESC
