SQL Server 中的索引碎片报告

问题

虽然索引可以使查询的执行速度加快数倍，因为它们可以使查询过程更快，但也存在与之相关的开销。它们会消耗额外的磁盘空间，并且每当数据更新、删除或追加到表中时都需要额外的时间来更新自身。此外，当您执行任何数据修改操作（INSERT、UPDATE 或 DELETE 语句）时，可能会出现索引碎片，并且索引中的信息可能会分散在数据库中。碎片索引数据可能会导致 SQL Server 执行不必要的数据读取和跨不同页面的切换，因此针对碎片严重的表的查询性能可能会非常差。在本文中，我将介绍碎片和确定碎片级别的不同查询。

解决方案

首次构建索引时，应该存在很少的碎片或不存在碎片。随着时间的推移，随着数据的插入、更新和删除，底层索引的碎片级别可能会开始上升。那么让我们看看它是如何发生的。

当一页数据填满 100% 并且必须向其中添加更多数据时，就会发生页拆分。为了给新传入的数据腾出空间，SQL Server 将一半数据从整页移动到新页。创建的新页面是在数据库中的所有页面之后创建的。因此，在查找数据时，SQL Server 必须从一页转到数据库中其他位置的另一页来查找所需的下一页，而不是从一页直接转到下一页。这称为索引碎片。

基本上有两种类型的碎片：

• 外部碎片- 外部碎片，也称为逻辑碎片，当索引叶页不符合逻辑顺序时发生，换句话说，当索引的逻辑顺序与索引的物理顺序不匹配时，就会发生外部碎片。这会导致 SQL Server 执行额外的工作来返回有序结果。在大多数情况下，对于返回很少记录的特定搜索或返回不需要排序的结果集的查询来说，外部碎片并不是什么大问题。
• 内部碎片- 当索引页中有太多可用空间时，就会发生内部碎片。通常，需要一些可用空间，尤其是在创建或重建索引时。您可以在创建或重建索引时指定填充因子设置，以指示创建时索引页的填充程度的百分比。如果索引页碎片过多，则会导致查询花费更长的时间（因为查找数据集需要额外的读取），并导致索引变得比所需的更大。如果索引数据页中没有可用空间，则数据更改（主要是插入）将导致如上所述的页拆分，这也需要额外的系统资源来执行。

正如我们所知，严重碎片化的索引会显着降低查询性能，并导致访问它的应用程序响应缓慢。那么现在的问题是如何识别碎片。SQL Server 2005及更高版本提供了动态管理功能（DMF）来确定索引碎片级别。这个新的 DMF ( sys.dm_db_index_physical_stats ) 函数接受参数，例如要查找碎片的数据库、数据库表和索引。有几个选项允许您指定您想要查看的索引碎片的详细程度，我们将在下面的示例中看到其中一些选项。

sys.dm_db_index_physical_stats 函数返回有关一个特定表或索引的表格数据。

输入参数	描述
数据库ID	默认值为 0（NULL、0 和 DEFAULT 在此上下文中是等效值），指定返回 SQL Server 实例中所有数据库的信息，否则如果您需要有关特定数据库的信息，请指定 sys.databases 中的数据库 ID。如果为database_id 指定NULL，则还必须为object_id、index_id 和partition_number 指定NULL。
对象 ID	默认值为 0（NULL、0 和 DEFAULT 在此上下文中是等效值），指定返回指定数据库中所有表和视图的信息，或者您可以为特定对象指定 object_id。如果为object_id 指定NULL，则还必须为index_id 和partition_number 指定NULL。
索引号	默认值为 -1（NULL、-1 和 DEFAULT 在此上下文中是等效值），指定返回基表或视图的所有索引的信息。如果为index_id 指定NULL，则还必须为partition_number 指定NULL。
分区号	默认值为 0（NULL、0 和 DEFAULT 在此上下文中是等效值），指定返回所属对象的所有分区的信息。partition_number 从 1 开始。非分区索引或堆的partition_number 设置为1。
模式	mode 指定用于获取统计信息的扫描级别。有效输入为 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。默认值 (NULL) 是 LIMITED。 LIMITED - 这是最快的模式，扫描的页面数最少。对于索引，仅扫描 B 树的父级页面（即叶级以上的页面）。在 SQL Server 2008 中，仅检查堆的关联 PFS 和 IAM 页；不扫描堆的数据页。在 SQL Server 2005 中，堆的所有页都以 LIMITED 模式进行扫描。 SAMPLED - 它返回基于索引或堆中所有页面的 1% 样本的统计信息。如果索引或堆的页数少于 10,000，则使用 DETAILED 模式而不是 SAMPLED。 详细 - 它扫描所有页面并返回所有统计信息。

说明：

• sys.dm_db_index_physical_stats动态管理函数替换了 DBCC SHOWCONTIG语句。与需要共享锁的 DBCC SHOWCONTIG 相比，它只需要一个意向共享 (IS) 表锁，而且计算碎片的算法比 DBCC SHOWCONTIG 更精确，因此可以提供更准确的结果。
• 对于索引，每个分区中 B 树的每一层都会返回一行（这就是原因，如果你看下图，对于某些索引，单个索引有两条或两条以上的记录；你可以参考 Index_depth 列，它告诉你索引的层数）。对于堆，为每个分区的 IN_ROW_DATA 分配单元返回一行。对于大对象 (LOB) 数据，将为每个分区的 LOB_DATA 分配单元返回一行。如果表中存在行溢出数据，则为每个分区中的 ROW_OVERFLOW_DATA 分配单元返回一行。

例子

让我们看一个例子。下面提供的第一个脚本给出了给定数据库的碎片级别，包括数据库中的所有表和视图以及这些对象上的所有索引。第二个脚本给出了给定数据库中特定对象的碎片级别。下表给出了有关sys.dm_db_index_physical_stats返回的列及其含义的详细信息。

--To Find out fragmentation level of a given database
--This query will give DETAILED information
--CAUTION : It may take very long time, depending on the number of tables in the DB
USE AdventureWorks
GO
SELECT object_name(IPS.object_id) AS [TableName], 
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent, 
   IPS.avg_fragment_size_in_pages, 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count, 
   IPS.ghost_record_count,
   IPS.fragment_count, 
   IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO

　　

 

--To Find out fragmentation level of a given database and table
--This query will give DETAILED information
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'Production.BillOfMaterials');
IF @object_id IS NULL 
BEGIN
   PRINT N'Invalid object';
END
ELSE
BEGIN
   SELECT IPS.Index_type_desc, 
      IPS.avg_fragmentation_in_percent, 
      IPS.avg_fragment_size_in_pages, 
      IPS.avg_page_space_used_in_percent, 
      IPS.record_count, 
      IPS.ghost_record_count,
      IPS.fragment_count, 
      IPS.avg_fragment_size_in_pages
   FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED') AS IPS;
END
GO

　　

返回栏目	描述
平均碎片百分比	它指示给定对象的外部碎片量。 数字越低越好 - 当该数字接近 100% 时，给定索引中排序不正确的页面越多。 对于堆来说，这个值实际上是盘区碎片的百分比，而不是外部碎片的百分比。
平均页空间已使用百分比	它指示索引中页面的密集程度，即索引中每个页面的平均填充程度（内部碎片）。 数字越高，就碎片和读取性能而言越好。为了实现最佳磁盘空间使用，对于不会有很多随机插入的索引，该值应该接近 100%。但是，具有许多随机插入且页面非常满的索引的页面拆分数量将会增加。这会导致更多碎片。因此，为了减少页面拆分，该值应小于 100%。
片段计数	片段由同一文件中分配单元的物理上连续的叶页组成。一个索引至少有一个片段。索引可以拥有的最大碎片等于索引叶级中的页数。因此碎片越少，连续存储的数据就越多。
页中平均片段大小	较大的碎片意味着读取相同数量的页面所需的磁盘 I/O 较少。因此，avg_fragment_size_in_pages值越大，范围扫描性能越好。

 

后续

• 查看 SQL Server技巧中的识别数据库碎片。
• 查看 MSDN 上的 sys.dm_db_index_physical_stats。
• 一旦确定了高碎片级别，下一步就是修复它，在我的下一篇技巧中，我将详细介绍修复高碎片级别的不同方法。