为什么我的数据库在我重新组织和重新构建并更新了Server中的统计信息之后仍然是支离破碎的？

Question

我创建了一个维护计划，首先重新组织索引，然后重建，最后每周六更新统计数据。我运行了这个脚本以进行验证：

SELECT 
    S.name as 'Schema',
    T.name as 'Table',
    I.name as 'Index',
    DDIPS.avg_fragmentation_in_percent,
    DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
    INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
    INNER JOIN sys.schemas S on T.schema_id = S.schema_id
    INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id 
        AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
    AND I.name is not null
    AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent DESC

但在运行之后，碎裂程度仍然很高。这样可以吗？我们能做些什么？

谢谢你的帮助！)

Answer 1

底线:对于小型索引来说，分段是不相关的。不要为不足1000页的索引费心，或者我们现在应该说是10000页。如果你记得磁带，我们总是有一些噪音在背景(我的类比，也许更好用瑞典语)。

Tip1:选择列表中的列名不要使用单引号。它偏离了ANSI，它是“奇怪的”。:-)

Tip2:您不必加入所有这些表。使用元数据函数代替。

Tip3:堆不像索引那样具有分段性。因此，要么过滤掉堆，要么学习转发指针/转发记录，并将它们与索引分开处理。这可能是有用的，例如：https://karaszi.com/rebuild-all-fragmented-heaps。

Tip4:在磁盘上来回跳不像以前那么昂贵了。假设你的存储速度比传统的旋转磁盘快，也就是说。也就是说，那种碎裂可能不会对你造成太大的伤害。尽管如此，还是有一些前瞻性的方面。例如，请参见：http://sqlblog.karaszi.com/fragmentation-the-final-installment/。

Tip5:使用更合理的方法在碎片级别进行筛选，比如> 5%而不是> 0%。

下面是一个简单的查询，上面有我的建议。但是，也许你一开始就不应该担心跳来跳去？

SELECT 
 OBJECT_SCHEMA_NAME(f.object_id) AS Schema_
,OBJECT_NAME(f.object_id) AS Table_
,i.name AS Index_
,f.avg_fragmentation_in_percent
,f.page_count
,i.type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS f
  INNER JOIN sys.indexes AS i On f.object_id = i.object_id
WHERE f.avg_fragmentation_in_percent > 5
 AND f.page_count > 1000
 AND i.type_desc NOT IN('HEAP', 'CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE')
ORDER BY avg_fragmentation_in_percent DESC

Answer 2

但在运行之后，碎裂程度仍然很高。这样可以吗？我们能做些什么？

原因在于您粘贴的输出的最后一列。请参阅列page_count。除非page_count值> 2000，否则没有必要重建、重新组织和更新该索引的状态。这是因为我引用了我写的文章的话。这些行基本上取自SQLServer2000BOL，它现在不在网上，被删除，但仍然是正确的。

碎片会影响磁盘I/O，因此，请关注较大的索引，因为它们的页面不太可能被Server缓存。使用报告的页面计数来了解索引的大小(每个页面的大小为8KB)。通常，您不应该关注不足1,000页的索引的碎片级别。在测试中，包含超过10,000页的索引实现了性能提升，而在页面大得多(大于50,000页)的索引上，性能提高最大。

还请注意

对于小索引，即使在重建之后，仍然存在碎片的原因是重建后分配给索引的页面是从混合范围跳变而来的。混合范围包含混合页面，而范围是8页的集合，当需要页面写入信息时，这始终是分配给数据库的前8页。前8页将始终从混合的范围，并在此之后，它将分配统一的范围。从混合范围分配前8页的原因是数据库引擎假设其相当可能的表(在开始时)很小，而且分配统一的范围没有太大的优势，因此它内部决定从混合范围分配前8页。当8页的限制被跨越时，它将开始分配统一的范围。由于混合范围没有分配给任何特定的IAM链，这意味着它可以保存可能分配给8个独立IAM的页面。这是非常重要的事实。以混合的范围分配的前8页可以分散在任何地方，这就是即使在重建之后也会出现碎片的原因。