关于SQL 2008数据库压缩后收缩（database shrink）的测试实验和性能比较
如果您正在面临压缩数据库的问题，那么这篇文章很可能帮助启发您。
数据库收缩性能问题可能会涉及一个关于栈内页面压缩的漏洞https://support.microsoft.com/kb/963658这篇文章讨论中数据库收缩之后可能页面压缩（page compression）会被移除，也许后续开发会完善这功能，让收缩之后页面压缩重建。
总而言之，聚集索引是在压缩和收缩方面最好用的工具
测试实验环境参数
数据库Database：
database_name database_size unallocated space
------------------ ------------------ ------------------
test1 26619.56 MB 15833.65 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
10418984 KB 6820040 KB 3571560 KB 27384 KB
包含一个表格: table1
Sp_spaceused table1
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
table1 62113305 10417560 KB 6819464 KB 3570824 KB 27272 KB
Sp_helpindex table1:
i_table_1 clustered located on PRIMARY SalesOrderDetailID
i_table1_1 nonclustered located on PRIMARY RowNumber, SalesOrderID, CarrierTrackingNumber, rowguid
SELECT PageCompressedAgain = page_count FROM sys.dm_db_index_physical_stats (
DB_ID (),
OBJECT_ID ('table1'),
NULL, NULL, 'SAMPLED');
852433
439130
delete from table1 where RowNumber%47 =1
(1321559 row(s) affected)
测试脚本:
print getdate()
dbcc shrinkfile(1)
print getdate()
测试1:
聚集索引条件下，行压缩 （ cluster index + row compression ）
Compression –log 4.85 GB (5,218,172,928 bytes)
SELECT PageCompressedAgain = page_count FROM sys.dm_db_index_physical_stats (
DB_ID (),
OBJECT_ID ('table1'),
NULL, NULL, 'SAMPLED');
561638
439130
Sp_spaceused table1
table1 60791746 8046304 KB 4493104 KB 3552312 KB 888 KB
shrink log 1.40 GB (1,511,325,696 bytes)
收缩之后:
PageCompressedAgain
--------------------
561638
439130
table1 60791746 8046528 KB 4493104 KB 3552288 KB 1136 KB
database_name database_size unallocated space
------------------- ------------------ ------------------
test1 9300.81 MB 0.19 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
8047936 KB 4493688 KB 3552960 KB 1288 KB
Mar 26 2010 3:46PM
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
12 1 1006016 256 1006000 1006000
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Mar 26 2010 3:52PM
耗时6分钟.
测试2:
在聚集索引条件下，页压缩（cluter index + page compression ）
Compression LOG 1.16 GB (1,248,985,088 bytes)
Shrink LOG 1.70 GB (1,828,716,544 bytes)
SELECT PageCompressedAgain = page_count FROM sys.dm_db_index_physical_stats (
DB_ID (),
OBJECT_ID ('table1'),
NULL, NULL, 'SAMPLED');
PageCompressedAgain
--------------------
133567
439130
Shrink:
Mar 29 2010 7:15AM
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
12 1 577032 256 577016 577016
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Mar 29 2010 7:25AM
耗时10 分钟
测试3:
删除聚集索引后做行压缩，之后收缩数据文件
Compression LOG 7.82 GB (8,404,074,496 bytes)
SELECT PageCompressedAgain = page_count FROM sys.dm_db_index_physical_stats (
DB_ID (),
OBJECT_ID ('table1'),
NULL, NULL, 'SAMPLED');
PageCompressedAgain
--------------------
541300
392331
Shrink --log 1.87 GB (2,011,627,520 bytes)
PageCompressedAgain
--------------------
541600
451801
Mar 26 2010 4:29PM
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
12 1 999512 256 999496 999496
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Mar 26 2010 7:04PM
耗时2小时35分钟
测试 4
删除聚集索引后做页压缩
compression log 140MB
shrink log 2.06 GB (2,212,823,040 bytes)
SELECT PageCompressedAgain = page_count FROM sys.dm_db_index_physical_stats (
DB_ID (),
OBJECT_ID ('table1'),
NULL, NULL, 'SAMPLED');
PageCompressedAgain
--------------------
127000
437707
Mar 29 2010 5:55PM
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
12 1 571048 256 571032 571032
Mar 29 2010 8:10PM
耗时2小时10分钟.
结论
1. 无论是row compression还是page compression ，有clustered index的情况下，shrink的性能都比没有clustered index的要快几十倍。
2. Row compression 的过程生成的log是page compression的4-5倍。
3. 在同样的compression模式下，没有clustered index的数据库shrink所生成的log 要略多于有clustered index的数据库。