代码改变世界

SQL Server获取索引创建时间&重建时间&重组时间

  潇湘隐者  阅读(3481)  评论(0编辑  收藏  举报

之前写过一篇博客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值,也无从判断。所以这里能记录准确的时间,但是无法区分索引重建与索引重组。

 

 

clip_image001

 

 

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);

 

clip_image002

 

clip_image003

 

 

注意:上面脚本在有些环境可能会出错,主要是因为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/

编辑推荐:
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
历史上的今天:
2018-10-22 Linux压缩打包tar命令总结
点击右上角即可分享
微信分享提示