主页 / dba / 问题 / 25639
Accepted
Scott Chamberlain
Asked: 2012-10-09 14:02:57 +0800 CST

为什么索引 REBUILD 不会减少页数 > 1000 的索引碎片？

  • 772

这是对本网站上现有问题“为什么索引重建不会减少索引碎片？ ”的后续行动。该问题的公认答案以及我在互联网上找到的所有其他资源都说，如果您的页数较少，碎片整理将无济于事。当百分比低于 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 |
+--------+

为什么我的索引没有得到碎片整理？

sql-server sql-server-2005

2 个回答

  1. Best Answer

    重建索引时设置 MAXDOP(1)。这将只使用 1 个 CPU，虽然它需要更长的时间，但碎片会少得多。

    我怀疑目前，在索引“相当小”的情况下，操作正在过度并行。使用的每个线程都会导致额外的碎片，因为每个线程只对自己的页面进行排序，并且对于较小的索引，这可能会更加明显。

    • 4

  2. 小型索引通常不会进行碎片整理，因为基本上没有意义。由于对象很小，因此移动页面基本上没有任何好处。

    • 0

相关问题