收缩数据库会导致严重的索引碎片

收缩数据文件会导致严重的索引碎片.

 

生产环境中有个job是定期收缩数据库，最近发现性能急剧下降，检查发现是多数Index存有严重的碎片,但每周都有一个Rebuild Index

的job,理论上不该出现严重的碎片问题。最后才发现时dbcc shrinkdatabase的原因。以下是问题重现步骤：

 

 

CREATE DATABASE DB_Shrink_Demo GO USE DB_Shrink_Demo GO CREATE TABLE tb1 (id int identity(1,1) primary key,c1 char(6000)) GO CREATE TABLE tb2 (id int identity(1,1) primary key,c1 char(6000)) GO INSERT INTO tb1(c1) VALUES('abc') GO 1000 INSERT INTO tb2(c1) VALUES('abc') GO 1000 SELECT * FROM sys.dm_db_index_physical_stats(db_id('DB_Shrink_Demo'),object_id('tb2'),1,DEFAULT,'DETAILED')

结果如下：此时avg_fragmentation_in_percent值为0.9.

 

 

再看页面分配情况：

可以见到tb1,tb2几乎没有任何碎片,页面分配很整齐.

 

 

 

干掉tb1,腾出可用空间.

 

TRUNCATE TABLE tb1 GO DBCC SHRINKDATABASE (DB_Shrink_Demo) GO SELECT * FROM sys.dm_db_index_physical_stats(db_id('DB_Shrink_Demo'),object_id('tb2'),1,DEFAULT,'DETAILED')

结果如下：

 

此时avg_fragmentation_in_percent值已经变成为98.1!!!

 

再看页面分配情况：

表面看上去没什么问题，页面分配也很完整，但是依顺序打开页面查看具体资料时却出现了重大问题，索引顺序完全反了！！！

 

 

176页(id =1000)：

 

 

177页（id=999):

 

 

结论：

DBCC ShrinkDatabase收缩数据文件时，会依据GAM页面资讯尽量将尾部的数据页面往前面的空余页面挪动，由于Index页面之间是有Double-link双向链接的，这样就会破坏页面物理跟索引逻辑的关系，导致了索引碎片的产生。

 

 

 

 

以上，仅用于自我备忘。