[KB223924850043681功能解说] 如何检查SQL Server上的索引碎片
[b]* 本文由赛捷软件（上海）有限公司翻译完成，未经授权不得转载。如需转载，请先联系相应版块的版主取得授权。如何检查SQL Server上的索引碎片[/b]
创建日期：2021-06-30
最后修改日期：2021-06-30
[b]原因[/b]
出现碎片的原因有很多，但最重要的是，索引碎片会导致查询速度变慢。在排除性能故障时，请首先检查碎片问题。
[b]解决方法[/b]
• 要检索X3数据库中的索引碎片，可以运行以下脚本。
• 将代码放入SQL Server Management Studio的新查询中，并将数据库连接至X3数据库。
o [color=Red]警告：[/color]此查询需要大量磁盘空间，不应在正常生产时间运行。
SELECT
Database_ID,
CONVERT(VARCHAR(35),DB_NAME(idx.database_id)) AS [DB Name],
sch.name [Schema Name],
idx.[Object_ID],CONVERT(VARCHAR(35),OBJECT_NAME(idx.[OBJECT_ID])) AS 'OBJECT NAME',
idx.Partition_Number [Partition Number],
idx.Index_ID [Index ID],
idx.Index_Type_Desc [Index Type Desc],
idx.Alloc_Unit_Type_Desc [Alloc Unit Type Desc],
idx.Index_Depth,Index_Level [Index Depth Level],
idx.Avg_Fragmentation_In_Percent [Avg Frag Percent],
idx.Fragment_Count [Frag Count],
Avg_Fragment_Size_In_Pages [Avg Frag Size In Pages],
idx.Page_Count [Page Count],
idx.Avg_Page_Space_Used_In_Percent [Avg Page Space Used In Percent],
idx.Record_Count [Record Count],
Ghost_Record_Count [Ghost Record Count],
idx.Version_Ghost_Record_Count [Version Ghost Record Count],
idx.Min_Record_Size_In_Bytes [Min Record Size In Bytes],
idx.Max_Record_Size_In_Bytes [Max Record Size In Bytes],
idx.Avg_Record_Size_In_Bytes [Avg Record Size In Bytes],
Forwarded_Record_Count [Forwarded Record Count]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') idx
INNER JOIN sys.tables t
ON idx.object_id = t.object_id
INNER JOIN sys.schemas sch
ON sch.schema_id = t.schema_id
WHERE idx.page_count > 100
AND idx.avg_fragmentation_in_percent > 15
AND idx.Index_ID > 0
ORDER BY idx.avg_fragmentation_in_percent desc
页:
[1]