建立壓縮資料表及索引

使用資料列和頁面壓縮時的考量

當您使用資料列和頁面壓縮時，請注意以下考量事項：

• 壓縮僅適用於 SQL Server 2008 Enterprise 和 Developer 版本。

• 壓縮可讓更多的資料列儲存在頁面上，但是不會變更資料表或索引的資料列大小上限。

• 當資料列大小上限加上壓縮負擔超過 8060 個位元組的資料列大小上限時，資料表將無法啟用壓縮。例如，由於額外的壓縮負擔之緣故，所以具有資料行 c1char(8000) 和 c2char(53) 的資料表無法加以壓縮。當使用 Vardecimal 儲存格式時，將會在啟用此格式時執行資料列大小檢查。對於資料列和頁面壓縮而言，最初壓縮物件時會執行資料列大小檢查，然後在插入或修改每一個資料列時加以檢查。壓縮會強制執行下列兩個規則：

  • 固定長度類型的更新一定要成功。

  • 停用資料壓縮一定要成功。即使壓縮的資料列適合頁面大小，這表示它小於 8060 個位元組；如果它未壓縮，SQL Server 會防止不適合資料列大小的更新。

• 當指定了資料分割清單時，壓縮類型可以在個別資料分割上設定為 ROW、PAGE 或 NONE。如果未指定資料分割的清單，將會設定所有資料分割，並包含陳述式中所指定的資料壓縮屬性。在建立資料表或索引時，除非另外指定，否則資料壓縮會設定為 NONE。在修改資料表時，除非另外指定，否則會保留現有的壓縮。

• 如果您指定資料分割清單或超出範圍的資料分割，將會產生錯誤。

• 非叢集索引不會繼承資料表的壓縮屬性。若要壓縮索引，您必須明確設定索引的壓縮屬性。根據預設，當建立索引時，索引的壓縮設定將會設定為 NONE。

• 在堆積上建立叢集索引時，此叢集索引會繼承堆積的壓縮狀態，除非指定了替代的壓縮狀態。

• 當堆積設定了頁面層級壓縮時，頁面只會以下列方式接收頁面層級壓縮：

  • 大量匯入資料，並且啟用大量最佳化。

  • 使用 INSERT INTO ...WITH (TABLOCK) 語法插入資料。

  • 執行 ALTER TABLE ...REBUILD 陳述式並指定 PAGE 壓縮選項來重建資料表。

• 重建堆積之前，配置在堆積中成為 DML 作業一部分的新頁面將不會使用 PAGE 壓縮。您可以透過移除並重新套用壓縮，或建立並移除叢集索引，重建堆積。

• 變更堆積的壓縮設定需要重建資料表上的所有非叢集索引，好讓它們擁有指向堆積內新資料列位置的指標。

• 您可以在線上或離線時啟用或停用 ROW 或 PAGE 壓縮。在堆積上啟用壓縮對於線上作業而言是單一執行緒的作業。

• 啟用或停用資料列或頁面壓縮的磁碟空間需求與建立或重建索引的需求相同。對於分割的資料而言，您可以一次啟用或停用一個資料分割的壓縮來減少所需的空間。

• 若要決定資料分割資料表中資料分割的壓縮狀態，請查詢 sys.partitions 目錄檢視的 data_compression 資料行。

• 當您壓縮索引時，可以在壓縮資料列和頁面的情況下壓縮分葉層級頁面。非分葉層級頁面不會收到頁面壓縮。

• 由於大數值資料類型的大小之緣故，這些類型有時會單獨儲存在特殊用途的頁面上，與一般資料列的資料分開。資料壓縮不適用於個別儲存的資料。

• 在 SQL Server 2005 中實作 Vardecimal 儲存格式的資料表將會在升級時保留此設定。您可以將資料列壓縮套用到具有 Vardecimal 儲存格式的資料表。 但是，由於資料列壓縮是 Vardecimal 儲存格式的超集，所以沒有理由保留 Vardecimal 儲存格式。當您將 Vardecimal 儲存格式結合資料列壓縮時，十進位值不會取得額外的壓縮。您可以將頁面壓縮套用到具有 Vardecimal 儲存格式的資料表；但是，Vardecimal 儲存格式資料行可能不會封存其他壓縮。

  [!附註]

  SQL Server 2008 支援 Vardecimal 儲存格式；但是，由於資料列層級的壓縮會達成相同的目標，所以 Vardecimal 儲存格式已被取代。未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。

壓縮實作

如需資料壓縮實作的摘要，請參閱＜資料列壓縮實作＞和＜頁面壓縮實作＞。

預估壓縮所節省的空間

若要判斷變更壓縮狀態如何影響資料表或索引，請使用 sp_estimate_data_compression_savings 預存程序。sp_estimate_data_compression_savings 預存程序只能在支援資料壓縮的 SQL Server 版本中使用。

壓縮對資料分割資料表和索引有何影響

當您搭配資料分割資料表和索引使用資料壓縮時，請注意以下考量事項：

• 分割範圍

  當使用 ALTER PARTITION 陳述式分割資料分割時，兩個資料分割都會繼承原始資料分割的資料壓縮屬性。

• 合併範圍

  當合併兩個資料分割時，所產生的資料分割會繼承目標資料分割的資料壓縮屬性。

• 切換資料分割

  若要切換資料分割，此資料分割的資料壓縮屬性必須符合資料表的壓縮屬性。

• 重建一個資料分割或所有資料分割

  您可以使用兩種語法變化來修改資料分割資料表或索引的壓縮：

  • 下列語法只會重建參考的資料分割：

    ALTER TABLE <table_name> 
    REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  <option>)
    

  • 下列語法會將現有的壓縮設定用於任何未參考的資料分割，藉以重建整個資料表：

    ALTER TABLE <table_name> 
    REBUILD PARTITION = ALL 
    WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),
    ... )
    

  資料分割索引會遵循使用 ALTER INDEX 的相同原則。

• 卸除資料分割叢集索引

  當卸除叢集索引時，除非修改了資料分割配置，否則對應的堆積資料分割會保留其資料壓縮設定。如果資料分割配置有所變更，所有資料分割都會重建為未壓縮的狀態。若要卸除叢集索引及變更資料分割配置，您需要執行以下步驟：

  1.卸除叢集索引。

  2.使用指定壓縮選項的 ALTER TABLE ...REBUILD ... 選項來修改資料表。

  在線上卸除叢集索引將會是非常快速的作業，因為只會移除叢集索引的上層。在線上卸除叢集索引時，SQL Server 必須重建堆積兩次，一次在步驟 1，另一次在步驟 2。

壓縮將如何影響複寫

當您搭配複寫使用資料壓縮時，請注意以下考量事項：

• 當快照集代理程式產生最初的結構描述指令碼時，新的結構描述會將相同的壓縮設定用於資料表和它的索引。不能只在資料表上啟用壓縮，而不在索引上啟用壓縮。

• 如果是交易式複寫，發行項結構描述選項會判斷哪些相依的物件和屬性必須編寫指令碼。如需詳細資訊，請參閱 sp_addarticle。

  散發代理程式在套用指令碼時，不會檢查是否有下層的訂閱者。如果選取了壓縮的複寫，在下層訂閱者上建立資料表將會失敗。如果是混合拓撲，請勿啟用壓縮的複寫。

• 如果是合併式複寫，發行集相容性層級會覆寫結構描述選項，並判斷將要編寫指令碼的結構描述物件。如需有關相容性層級的詳細資訊，請參閱＜在複寫拓撲中使用多個 SQL Server 版本＞。

  在混合拓撲的情況下，如果它不必支援新的壓縮選項，則發行集相容性層級應該設定為下層的訂閱者版本。如果需要的話，請於建立資料表之後在訂閱者上壓縮資料表。

下表顯示在複寫期間控制壓縮的複寫設定。

壓縮對於其他 SQL Server 元件有何影響

壓縮會發生在儲存引擎中，而且資料會以非壓縮狀態呈現給 SQL Server 中的大多數其他元件。這樣會將壓縮對其他元件的影響限制為以下情況：

• 大量匯入及匯出作業

  當匯出資料時 (即使是原生格式)，資料為非壓縮資料列格式的輸出。這可能會造成匯出的資料檔大小比來源資料大出許多。

  當匯入資料時，如果目標資料表已啟用壓縮，則儲存引擎會將資料轉換成壓縮的資料列格式。這樣可能會造成 CPU 使用量增加 (相較於資料匯入未壓縮的資料表時)。

  將資料大量匯入具有頁面壓縮的堆積內時，大量匯入作業會在插入具有頁面壓縮的資料時，嘗試壓縮這些資料。

• 壓縮不會影響備份和還原。

• 壓縮不會影響記錄傳送。

• 資料壓縮與疏鬆資料行不相容。因此，您無法壓縮包含疏鬆資料行的資料表，也無法將疏鬆資料行加入至壓縮資料表。

• 啟用壓縮可能會造成查詢計畫變更，因為系統會使用不同的頁數以及每頁不同的資料列數來儲存資料。

• SQL Server Management Studio 會透過 [資料壓縮精靈] 來支援資料壓縮。

啟動資料壓縮精靈

• 在 [物件總管] 中，以滑鼠右鍵按一下資料表、索引或索引檢視表，並指向 [儲存體]，然後按一下 [壓縮]。

監視壓縮

若要監視整個 SQL Server 執行個體的壓縮，請使用 SQL Server, Access Methods Object 的 Page compression attempts/sec 和 Pages compressed/sec 計數器。

若要取得個別資料分割的頁面壓縮統計資料，請查詢 sys.dm_db_index_operational_stats 動態管理函數。

範例

下列其中一些範例使用資料分割資料表，而且需要有檔案群組的資料庫。若要建立有檔案群組的資料庫，請執行以下陳述式。

CREATE DATABASE TestDatabase
ON  PRIMARY
( NAME = TestDatabase,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf'),
FILEGROUP test1fg
( NAME = TestDBFile1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile1.mdf'),
FILEGROUP test2fg
( NAME = TestDBFile2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile2.ndf'),
FILEGROUP test3fg
( NAME = TestDBFile3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile3.ndf'),
FILEGROUP test4fg
( NAME = TestDBFile4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile4.ndf') ;
GO

切換至新的資料庫：

USE TestDatabase
GO

A. 建立使用資料列壓縮的資料表

CREATE TABLE T1 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW);
GO

B. 建立使用頁面壓縮的資料表

CREATE TABLE T2 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
GO

C. 在資料分割資料表上設定 DATA_COMPRESSION 選項

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO

CREATE TABLE PartitionTable1 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1),
  DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
);
GO

D. 在資料分割資料表上設定 DATA_COMPRESSION 選項

CREATE TABLE PartitionTable2 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1,3),
  DATA_COMPRESSION = NONE ON PARTITIONS (2,4)
);
GO

E. 修改資料表來變更壓縮

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

F. 修改資料分割資料表內一個資料分割的壓縮

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

G. 修改資料分割資料表內數個資料分割的壓縮

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO

H. 修改索引上的壓縮

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2) 
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

ALTER INDEX IX_INDEX_1 
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO

I. 修改資料分割索引內單一資料分割的壓縮

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1),
    DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4 ) ) ;
GO

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

J. 修改資料分割索引內數個資料分割的壓縮

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO

請參閱

參考