Sql-Server
為什麼索引 REBUILD 不會減少頁數 > 1000 的索引碎片？
這是對本網站上現有問題“為什麼索引重建不會減少索引碎片？ ”的後續行動。該問題的公認答案以及我在網際網路上找到的所有其他資源都說，如果您的頁數較少，碎片整理將無濟於事。當百分比低於 10% 時，我也看到了“它只能這麼低”的答案
但是我有 3 個索引的表，所有 3 個索引的頁數都 > 1900，並且碎片都在 80% 以上。
這是我執行的查詢。
select database_id, object_id, index_id, partition_number, index_type_desc, alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count FROM sys.dm_db_index_physical_stats (DB_ID(N'i101600'), 78675378, null, null, null) SELECT b.name, c.name, a.* FROM sys.dm_db_index_physical_stats (DB_ID(N'i101600'), 78675378, null, null, 'DETAILED') a inner join sys.tables b on a.object_id = b.object_id inner join sys.indexes c on a.object_id = c.object_id and a.index_id = c.index_id print 'Rebuilding' Alter index all on aahaDXavailables rebuild with (online = off) print 'Done Rebuilding' SELECT b.name, c.name, a.* FROM sys.dm_db_index_physical_stats (DB_ID(N'i101600'), 78675378, null, null, 'DETAILED') a inner join sys.tables b on a.object_id = b.object_id inner join sys.indexes c on a.object_id = c.object_id and a.index_id = c.index_id select count(*) as Rows from aahaDXavailables它的結果
+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+ | 數據庫ID | object_id | index_id | 分區號 | index_type_desc | alloc_unit_type_desc | 索引深度 | index_level | avg_fragmentation_in_percent | 片段計數 | avg_fragment_size_in_pages | 頁數 | +-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+ | 32 | 78675378 | 1 | 1 | 聚集索引 | IN_ROW_DATA | 3 | 0 | 87.7104377104377 | 2640 | 1.125 | 2970 | | 32 | 78675378 | 2 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 0 | 82.0746132848044 | 1853 | 1.18618456556935 | 2198 | | 32 | 78675378 | 3 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 0 | 99.1295442908346 | 1952 | 1.00051229508197 | 1953 | +-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------++------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+ | 姓名 | 姓名 | 數據庫ID | object_id | index_id | 分區號 | index_type_desc | alloc_unit_type_desc | 索引深度 | index_level | avg_fragmentation_in_percent | 片段計數 | avg_fragment_size_in_pages | 頁數 | avg_page_space_used_in_percent | 記錄計數 | ghost_record_count | version_ghost_record_count | min_record_size_in_bytes | max_record_size_in_bytes | avg_record_size_in_bytes | forwarded_record_count | +------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+ | aahaDX 可用 | IX_aahaDXavailables_dxGUID | 32 | 78675378 | 2 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 0 | 82.0746132848044 | 1853 | 1.18618456556935 | 2198 | 64.311304670126 | 327022 | 0 | 0 | 33 | 33 | 33 | 空 | | aahaDX 可用 | IX_aahaDXavailables_dxGUID | 32 | 78675378 | 2 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 1 | 100 | 18 | 1 | 18 | 61.8304423029405 | 2198 | 0 | 0 | 39 | 39 | 39 | 空 | | aahaDX 可用 | IX_aahaDXavailables_dxGUID | 32 | 78675378 | 2 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 2 | 0 | 1 | 1 | 1 | 9.09315542377069 | 18 | 0 | 0 | 39 | 39 | 39 | 空 | | aahaDX 可用 | IX_aahaDXavailables_itemGUID | 32 | 78675378 | 3 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 0 | 99.1295442908346 | 1952 | 1.00051229508197 | 1953 | 72.3821349147517 | 327022 | 0 | 0 | 33 | 33 | 33 | 空 | | aahaDX 可用 | IX_aahaDXavailables_itemGUID | 32 | 78675378 | 3 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 1 | 100 | 16 | 1 | 16 | 61.805819125278 | 1953 | 0 | 0 | 39 | 39 | 39 | 空 | | aahaDX 可用 | IX_aahaDXavailables_itemGUID | 32 | 78675378 | 3 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 2 | 0 | 1 | 1 | 1 | 8.08005930318755 | 16 | 0 | 0 | 39 | 39 | 39 | 空 | | aahaDX 可用 | PK_aahaDX 可用 | 32 | 78675378 | 1 | 1 | 聚集索引 | IN_ROW_DATA | 3 | 0 | 87.7104377104377 | 2640 | 1.125 | 2970 | 80.2371756856931 | 327022 | 0 | 0 | 57 | 57 | 57 | 空 | | aahaDX 可用 | PK_aahaDX 可用 | 32 | 78675378 | 1 | 1 | 聚集索引 | IN_ROW_DATA | 3 | 1 | 94.4444444444444 | 18 | 1 | 18 | 50.9389671361502 | 2970 | 0 | 0 | 23 | 23 | 23 | 空 | | aahaDX 可用 | PK_aahaDX 可用 | 32 | 78675378 | 1 | 1 | 聚集索引 | IN_ROW_DATA | 3 | 2 | 0 | 1 | 1 | 1 | 5.53496417099086 | 18 | 0 | 0 | 23 | 23 | 23 | 空 | +------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+重建 完成重建+------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+ | 姓名 | 姓名 | 數據庫ID | object_id | index_id | 分區號 | index_type_desc | alloc_unit_type_desc | 索引深度 | index_level | avg_fragmentation_in_percent | 片段計數 | avg_fragment_size_in_pages | 頁數 | avg_page_space_used_in_percent | 記錄計數 | ghost_record_count | version_ghost_record_count | min_record_size_in_bytes | max_record_size_in_bytes | avg_record_size_in_bytes | forwarded_record_count | +------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+ | aahaDX 可用 | IX_aahaDXavailables_dxGUID | 32 | 78675378 | 2 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 0 | 82.0746132848044 | 1853 | 1.18618456556935 | 2198 | 64.311304670126 | 327022 | 0 | 0 | 33 | 33 | 33 | 空 | | aahaDX 可用 | IX_aahaDXavailables_dxGUID | 32 | 78675378 | 2 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 1 | 100 | 18 | 1 | 18 | 61.8304423029405 | 2198 | 0 | 0 | 39 | 39 | 39 | 空 | | aahaDX 可用 | IX_aahaDXavailables_dxGUID | 32 | 78675378 | 2 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 2 | 0 | 1 | 1 | 1 | 9.09315542377069 | 18 | 0 | 0 | 39 | 39 | 39 | 空 | | aahaDX 可用 | IX_aahaDXavailables_itemGUID | 32 | 78675378 | 3 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 0 | 99.1295442908346 | 1952 | 1.00051229508197 | 1953 | 72.3821349147517 | 327022 | 0 | 0 | 33 | 33 | 33 | 空 | | aahaDX 可用 | IX_aahaDXavailables_itemGUID | 32 | 78675378 | 3 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 1 | 100 | 16 | 1 | 16 | 61.805819125278 | 1953 | 0 | 0 | 39 | 39 | 39 | 空 | | aahaDX 可用 | IX_aahaDXavailables_itemGUID | 32 | 78675378 | 3 | 1 | 非聚集索引 | IN_ROW_DATA | 3 | 2 | 0 | 1 | 1 | 1 | 8.08005930318755 | 16 | 0 | 0 | 39 | 39 | 39 | 空 | | aahaDX 可用 | PK_aahaDX 可用 | 32 | 78675378 | 1 | 1 | 聚集索引 | IN_ROW_DATA | 3 | 0 | 87.7104377104377 | 2640 | 1.125 | 2970 | 80.2371756856931 | 327022 | 0 | 0 | 57 | 57 | 57 | 空 | | aahaDX 可用 | PK_aahaDX 可用 | 32 | 78675378 | 1 | 1 | 聚集索引 | IN_ROW_DATA | 3 | 1 | 94.4444444444444 | 18 | 1 | 18 | 50.9389671361502 | 2970 | 0 | 0 | 23 | 23 | 23 | 空 | | aahaDX 可用 | PK_aahaDX 可用 | 32 | 78675378 | 1 | 1 | 聚集索引 | IN_ROW_DATA | 3 | 2 | 0 | 1 | 1 | 1 | 5.53496417099086 | 18 | 0 | 0 | 23 | 23 | 23 | 空 | +------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------++--------+ | 行 | +--------+ | 327022 | +--------+為什麼我的索引沒有得到碎片整理？
重建索引時設置 MAXDOP(1)。這將只使用 1 個 CPU，雖然它需要更長的時間，但碎片會少得多。
我懷疑目前，在索引“相當小”的情況下，操作正在過度並行。使用的每個執行緒都會導致額外的碎片，因為每個執行緒只對自己的頁面進行排序，並且對於較小的索引，這可能會更加明顯。
小型索引通常不會進行碎片整理，因為基本上沒有意義。由於對像很小，因此移動頁面基本上沒有任何好處。