问题

虽然索引可以使查询的执行速度加快数倍，因为它们可以使查询过程更快，但也存在与之相关的开销。它们会消耗额外的磁盘空间，并且每当数据更新、删除或追加到表中时都需要额外的时间来更新自身。此外，当您执行任何数据修改操作（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 函数返回有关一个特定表或索引的表格数据。

说明：

• 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

后续

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