SQL Server獲取索引創建時間&重建時間&重組時間

之前寫過一篇博客“SQL Server中是否可以準確獲取最後一次索引重建的時間？“，裡面主要講述了三個問題：我們能否找到索引的創建時間？最後一次索引重建（Index Rebuild)的時間？ 最後一次索引重組（INDEX REORGANIZE)的時間呢？,當時得出的結論，答案是我們無法準確的找到索引的創建時間、最後一次索引重組時間，最後一次索引重建的時間。但是最近看到一篇博客“SQL Server – Get Index Creation Date”，然後研究了一下，即使SQL Server暫時沒有一個系統表或DMV視圖有保存索引創建的時間，索引重建的時間、索引重組的時間。但是我們可以通過系統跟蹤文件獲取它們的值，當然也有限制條件，並不是所有的索引都能找到這些值。請見下麵詳細解說：

索引的創建時間

索引的創建時間，可以用下麵SQL獲取，但是我們知道跟蹤有可能停止或禁用；跟蹤文件也可能被覆蓋。所以這種方法只能查詢最近一段時間的。它有很強的時效性。所以這種方法不能通用。註定其只能作為一種方法參考，而不能通用。

DECLARE @filename VARCHAR(500) 

SELECT @filename = CAST(value AS VARCHAR(500)) 

FROM fn_trace_getinfo(DEFAULT) 

WHERE property = 2 

  AND value IS NOT NULL 

-- Go back 4 files since default trace only keeps the last 5 and start from there.

SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'

SELECT 

       gt.EventClass, 

       gt.EventSubClass,

       te.Name AS EventName,

       gt.HostName, 

       gt.StartTime, 

       gt.DatabaseName,

       gt.ObjectName,

       gt.IndexID

FROM fn_trace_gettable(@fileName, DEFAULT) gt 

JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 

WHERE EventClass = 46

  and ObjectType = 22601

  and gt.DatabaseName <> 'tempdb'

ORDER BY StartTime desc; 

索引的重建時間 &索引的重組時間

如下所示，Object:Altered的trace_event_id為164，這裡我們無法區分ALTER INDEX ... REBUILD 和  ALETER INDEX ...REORGANIZE. 對於索引重建、索引重組，fn_trace_gettable返回的TextData為Null值，也無從判斷。所以這裡能記錄準確的時間，但是無法區分索引重建與索引重組。

DECLARE @filename VARCHAR(500) 

SELECT @filename = CAST(value AS VARCHAR(500)) 

FROM fn_trace_getinfo(DEFAULT) 

WHERE property = 2 

  AND value IS NOT NULL 

-- Go back 4 files since default trace only keeps the last 5 and start from there.

SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'

SELECT 

       gt.EventClass, 

       gt.EventSubClass,

       te.Name AS EventName,

       gt.HostName, 

       gt.StartTime, 

       gt.DatabaseName,

       gt.ObjectName,

       gt.IndexID

FROM fn_trace_gettable(@fileName, DEFAULT) gt 

JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 

WHERE EventClass = 164

  and ObjectType = 22601

  and gt.DatabaseName <> 'tempdb'

ORDER BY StartTime desc; 

測試驗證如下所示：

USE YourSQLDba;

GO

ALTER INDEX Pk_HistMaintTrav ON [Maint].[JobHistory] REBUILD;

ALTER INDEX PK_DataBaseSizeDtl_Day ON [Maint].[DataBaseSizeDtl_Day] REORGANIZE;

CREATE INDEX IX_DataBaseSizeDtl_Day_N1 ON [Maint].[DataBaseSizeDtl_Day](DataBaseName);

註意：上面腳本在有些環境可能會出錯，主要是因為trac文件的路徑，例如C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_603.trc 就會遇到下麵錯誤，需要根據實際情況修改腳本。

Msg 245, Level 16, State 1, Line 8

Conversion failed when converting the varchar value '50.MSSQLSERVER\MSSQL\Log\log_603' to data type int.

參考資料：

https://sqlconjuror.com/sql-server-get-index-creation-date/