SQLSERVER频繁重建索引 sqlserver 重建索引

SQLSERVER 在日常DBA工作中有一项叫索引整理

一般整理的多为非聚集索引

问题：聚集索引是否需要整理？在什么情况下需要整理？整理的效果如何？有没有负面作用？

测试环境:WIN2003+SQL2008R2
测试表:wkf_test 存放12767550条记录,wkf_test_all表是该表的备份

 

1.首先来次DBCC结果如下:
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 64860
- 扫描区数..............................: 8142
- 区切换次数..............................: 8145
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 99.53% [8108:8146]
- 逻辑扫描碎片 ..................: 0.38%
- 区扫描碎片 ..................: 0.07%
- 每页的平均可用字节数........................: 20.6
- 平均页密度(满).....................: 99.75%

2.删除1部分连续数据，注意看页数相应减少，页密度保持不动
delete from dbo.wkf_test where id between 50 and 2767550
表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 50803
- 扫描区数..............................: 6377
- 区切换次数..............................: 6377
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 99.58% [6351:6378]
- 逻辑扫描碎片 ..................: 0.38%
- 区扫描碎片 ..................: 0.02%
- 每页的平均可用字节数........................: 20.8
- 平均页密度(满).....................: 99.74%

3.删除奇数数据，亮点来了，页数保持不变的同时，页密度大幅下降 '（此时数据表性能很差）'
delete from dbo.wkf_test where id%2=0
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 50803
- 扫描区数..............................: 6377
- 区切换次数..............................: 6377
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 99.58% [6351:6378]
- 逻辑扫描碎片 ..................: 0.38%
- 区扫描碎片 ..................: 0.02%
- 每页的平均可用字节数........................: 4060.2
- 平均页密度(满).....................: 49.84%

4.REBUILD一下聚集索引,以90的填充率后,页数大量下降(约50%)，密度90
ALTER INDEX ALL ON wkf_test REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON,ONLINE = ON);
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 28064
- 扫描区数..............................: 3546
- 区切换次数..............................: 3569
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 98.26% [3508:3570]
- 逻辑扫描碎片 ..................: 0.38%
- 区扫描碎片 ..................: 1.83%
- 每页的平均可用字节数........................: 793.8
- 平均页密度(满).....................: 90.19%

5.再把奇数数据补回来。--运行时间 01:36

set identity_insert [dbo].[wkf_test] on 
INSERT INTO [dbo].[wkf_test](id,[usernmae],[tablename]) 
SELECT * from wkf_test_all where id%2=0
set identity_insert [dbo].[wkf_test] off

6.此时的DBCC结果显示 数据表页数再次暴涨.很显然数据是追上去的而不是塞上去的,古怪的现象是数据页变得稀了。注意这个细节，马上的步骤会有怪异的现象发生
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 82431
- 扫描区数..............................: 10338
- 区切换次数..............................: 59658
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 17.27% [10304:59659]
- 逻辑扫描碎片 ..................: 68.19%
- 区扫描碎片 ..................: 0.29%
- 每页的平均可用字节数........................: 2429.9
- 平均页密度(满).....................: 69.98%

7.好吧，现在再把刚才"追"上去的数据再删了，理论上应该回退到4的结果。但是现实情况是页数不变。继续变稀。理解一下：后被的数据不被SQLSERVER理解为连续的方式"追"上去的
delete from dbo.wkf_test where id%2=0
表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 75403
- 扫描区数..............................: 9485
- 区切换次数..............................: 58521
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 16.11% [9426:58522]
- 逻辑扫描碎片 ..................: 73.90%
- 区扫描碎片 ..................: 0.69%
- 每页的平均可用字节数........................: 5377.4
- 平均页密度(满).....................: 33.56%

8.那么我们再把数据补回去一次，很好，SQLSERVER发现了上次的“空”，把数据原生态补回去了。而且INSERT性能明显强过整理过的那次（时间是第一次耗时的1/3）

set identity_insert [dbo].[wkf_test] on 
INSERT INTO [dbo].[wkf_test]
(id,
[usernmae]
,[tablename]) 
SELECT * from wkf_test_all where id%2=0
set identity_insert [dbo].[wkf_test] off
--00:33
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...

表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 82431
- 扫描区数..............................: 10337
- 区切换次数..............................: 59660
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 17.27% [10304:59661]
- 逻辑扫描碎片 ..................: 68.22%
- 区扫描碎片 ..................: 0.30%
- 每页的平均可用字节数........................: 2429.9
- 平均页密度(满).....................: 69.98%

9.再REBULID一下.这个没什么说的了，顺利到指定的90

ALTER INDEX ALL ON wkf_test
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,ONLINE = ON);
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...

表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 63932
- 扫描区数..............................: 8078
- 区切换次数..............................: 8097
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 98.69% [7992:8098]
- 逻辑扫描碎片 ..................: 0.25%
- 区扫描碎片 ..................: 4.51%
- 每页的平均可用字节数........................: 790.4
- 平均页密度(满).....................: 90.23%

10.delete from dbo.wkf_test where id between 50 and 10767550
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 11234
- 扫描区数..............................: 1420
- 区切换次数..............................: 1421
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 98.80% [1405:1422]
- 逻辑扫描碎片 ..................: 0.27%
- 区扫描碎片 ..................: 14.37%
- 每页的平均可用字节数........................: 791.6
- 平均页密度(满).....................: 90.22%

总结如下：
以IDENTITY(1,1)为聚集索引时。
如果连续的删除大量索引在这些索引以有序的方式进行排列时(是不是有点绕口？)会被数据库引擎识别，将这一串页从页链中删除。无需做聚集索引的整理工作
如果不连续的删除，则数据页被保存，页密度被稀释
如果整理后填充了原有的页空间，则数据会追加到新页中，而不是分裂旧页，此时INSERT效率会下降，因为要新开页
反之如果删除后没有整理释放页空间，则补回来的数据会加到原有的位置（可能会引起页分裂），在补回去的数据不超过旧有数据的情况下。INSERT效率会比较快