關於SQL 2008資料庫壓縮後收縮（database shrink）的測試實驗和效能比較

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE如果您正在面臨壓縮資料庫的問題，那麼這篇文章很可能幫助啟發您。

資料庫收縮效能問題可能會涉及一個關於棧內頁面壓縮的漏洞http://support.microsoft.com/kb/963658這篇文章討論中資料庫收縮之後可能頁面壓縮（page compression）會被移除，也許後續開發會完善這功能，讓收縮之後頁面壓縮重建。

總而言之，聚集索引是在壓縮和收縮方面最好用的工具

 

測試實驗環境引數

資料庫Database：

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE

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

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE

name     reserved      data         index_size     unused     rows

-----  -------------  ------------  ------------  ---------  --------

table1  10417560 KB    6819464 KB    3570824 KB    27272 KB  62113305

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE

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

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE

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

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE

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

測試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

測試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

測試 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

結論

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的資料庫。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25175503/viewspace-704935/，如需轉載，請註明出處，否則將追究法律責任。