為何使用SQL SERVER Data Compression會讓查詢效能變慢與資料空間上升

啟用資料表Page壓縮反而讓查詢效能下降與資料空間上升？

情況：

在公司遇到一個很奇怪的現象， A是舊機器，B是新機器，B的CPU、記憶體與Storage性能上都比A好上很多，同一支程式的執行結果A機器比B機器較能還好(1秒VS 2分鐘)，當下檢查伺服器設定與更新資料表的統計資訊，在確定幾個比較重要的設定與統計資訊沒問題之後，再次執行同一支程式後結果還是相同。

發現：

於是開始比對兩邊資料庫的環境，結果發現某個資料表的資料筆數相同，但資料空間卻差異很大，在A的資料空間不到1MB，在B將近16MB，檢查相關設定之後發現B的資料表啟用了Page壓縮，在使用sp_estimate_data_compression_savings評估之後，當Table沒有啟用壓縮後，資料容量變回不到1MB，再次測試的結果兩邊執行的結果皆為1秒左右。

思考：

當下的疑問是為何啟用資料表壓縮後，資料量反而會變大呢?查了一下MSDN，發現由於B機器是使用Page壓縮，而Page壓縮使用的是Prefix Compression與Dictionary Compression兩種壓縮技術，使用這兩種技術壓縮會產生metadata，如下圖，

原始Table

使用Prefix Compression壓縮產生的metadata

使用Dictionary Compression技術產生的metadata

簡而言之，壓縮資料時會產生metadata，而產生metadata的大於原本要壓縮的資料時，就會產生啟用資料壓縮時資料容量(MB)大於尚未壓縮的情況。

解決方案：

那麼要如何避免這種慘劇發生呢?答案是使用sp_estimate_data_compression_savings這是Stored Procedure來評估啟用資料壓縮後空間容量的變化。

1.      啟用資料壓縮後資料容量上升

首先來模擬資料啟用壓縮資料會讓空間變大的情境，壓縮資料通常是資料長的很像壓縮比才會提升，所以使用NEWID()這種函數，讓產生內容都是唯一值，如此啟用資料壓縮後資料容量會變大。

測試SCRIPT如下

USE MASTER

GO

ALTER DATABASE TEST SET RECOVERY SIMPLE

GO

CHECKPOINT

USE TEST

SET NOCOUNT ON

GO

IF EXISTS ( SELECT t.name FROM sys.tables t

                        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

                        WHERE t.name = 'TestTable' AND s.name = 'dbo'

                  )

BEGIN

        DROP TABLE dbo.TestTable

END

GO

CREATE TABLE dbo.TestTable (

 C1 CHAR(36) )

GO

INSERT INTO TestTable SELECT NEWID()

GO 20000

檢視建立的資料表空間內容，資料表空間為0.875MB，資料壓縮類型為None(就是不壓縮)。

我們使用sp_estimate_data_compression_savings來評估壓縮前後的空間容量。

由上圖可知啟用資料表壓縮，資料空間反而比壓縮前還大。

接下來進入啟用PAGE壓縮，壓縮SCRIPT如下

USE [Test]

ALTER TABLE [dbo].[TestTable] REBUILD PARTITION = ALL

WITH

(DATA_COMPRESSION = PAGE

)

啟用PAGE壓縮後，我們發現資料空間確實比壓縮前還大。

那會有甚麼影響呢?有的，資料空間變大，意味著要用到的PAGE變多，使用的記憶體回提升，這代表了邏輯存取會提高，進而讓查詢效能降低。

我們來看看驗證結果吧：

當不啟用資料壓縮時

測試SCRIPT

USE [Test]

GO

SET STATISTICS TIME ON

SET STATISTICS IO ON

GO

ALTER TABLE [dbo].[TestTable] REBUILD PARTITION = ALL

WITH

(DATA_COMPRESSION = NONE

)

GO

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

SELECT * FROM [dbo].[TestTable]

執行結果

尚未啟用資料壓縮時，邏輯讀取為112，執行時間為184

當啟用資料壓縮為Page時

啟用資料壓縮為PAGE時，邏輯讀取為114，執行時間為194

結論：

啟用資料表的資料壓縮大部份都可以提升效能和減少資料空間，但也有特例，所以在啟用壓縮之前，要使用sp_estimate_data_compression_savings去評估啟用資料表壓縮後空間的變化，才不會弄巧成拙。

後話：

如果各位測試差距不明顯的話，可以將資料數目從2萬變成5萬看看，雖然說知道會有狀況，不過遇到公司提升快16MB的情況還是第一次遇到。最重要的是我產生全資料庫所有資料表資料壓縮的SCRIPT需要修改了。Orz