Udostępnij za pośrednictwem

  • Artykuł

Tworzenie skompresowanego tabel i indeksów

SQL Server 2008 obsługuje kompresję wiersza i strona dla tabel i indeksów. Kompresję danych można skonfigurować dla następujących obiektów bazy danych:

  • Całej tabela jest przechowywana jako sterty.

  • Całej tabela jest przechowywana jako indeks klastrowany.

  • Całość indeks nieklastrowany.

  • Całość indeksowany widok.

  • Dla tabel podzielonym na partycje i indeksy opcja kompresji można skonfigurować dla każdej partycji i różnych partycji obiektu nie muszą mieć takie same ustawienia kompresji.

Ustawienie kompresji tabela nie jest automatycznie stosowane do jego zbudowania indeksów nie klastrowanych.Każdy indeks musi być zestaw indywidualnie.Kompresja nie jest dostępna dla tabele systemowe.Tabele i indeksy mogą być skompresowane, utworzone za pomocą Tworzenie tabeli i CREATE INDEX instrukcji.Do zmiany stanu kompresji tabela, indeks lub partycji, użyj ALTER tabela lub Zmianę INDEKSU instrukcji.

Ostrzeżenie

Jeśli istniejące dane jest pofragmentowany, można zmniejszyć rozmiar indeksu przebudowywanie indeksu zamiast kompresji.Współczynnik wypełnienia indeksu będą stosowane podczas przebudowy indeksu, który potencjalnie może zwiększyć rozmiar indeksu.Aby uzyskać więcej informacji, zobacz Współczynnik wypełnienia.

Zagadnienia dotyczące podczas używania wiersza i strony kompresji

Gdy używasz kompresji wiersza i strona, należy pamiętać, następujące kwestie:

  • Kompresja jest dostępne tylko w SQL Server 2008 wersje Enterprise i Developer.

  • Kompresji można zezwolić na więcej wierszy, które mają być przechowywane strona, ale nie zmienia maksymalny rozmiar wiersza tabela lub indeksu.

  • tabela nie można włączyć kompresję, gdy maksymalny rozmiar wiersza plus narzutów kompresji przekracza maksymalny rozmiar wiersza 8060 bajtów.For example, a table that has the columns c1 char(8000) and c2 char(53) cannot be compressed because of the additional compression overhead.Gdy używany jest format przechowywania vardecimal, sprawdzanie rozmiar wiersza jest wykonywane, gdy jest włączony format.Kompresji wiersza i strona wyboru rozmiar wiersza jest wykonywane, gdy obiekt jest początkowo skompresowany i następnie sprawdzane każdy wiersz został wstawiony lub zmodyfikowany.Kompresja wymusza następujące dwie reguły:

    • Aktualizacja typ stałej długości zawsze musi zakończyć się sukcesem.

    • Wyłączenie kompresji danych należy zawsze powiodła się.Nawet jeśli skompresowany wiersza mieści się strona, co oznacza, że jest 8060 bajtów; SQL Serverzapobiega aktualizacji, które nie dopasowanie w wierszu po kompresji.

  • Gdy określono listę partycji zestaw typ kompresji do wiersza, strony lub brak na poszczególnych partycji.Jeśli na liście partycji nie zostanie określony, wszystkie partycje są zestaw z właściwość kompresji danych określona w instrukcja.Po utworzeniu tabela lub indeksie kompresja danych jest zestaw Brak chyba że ustalono inaczej.Podczas modyfikowania tabela istniejącej kompresji jest zachowywany, chyba że ustalono inaczej.

  • Jeśli określić listę partycji lub partycji, która jest z zakres, zostanie wygenerowany błąd.

  • Ponownego zbudowania indeksów nie dziedziczą właściwość kompresji tabela.Aby skompresować indeksów, należy jawnie zestaw właściwość kompresji indeksów.Domyślnie kompresja zestawwyrównywania dla indeksów będzie zestaw Brak podczas tworzenia indeksu.

  • Po utworzeniu indeksu klastrowanego w sterty indeks klastrowany dziedziczy stanu kompresji sterty nie określono stanu kompresji alternatywnych.

  • Po skonfigurowaniu sterty kompresji poziom strona, strona wyświetlany kompresji poziom strona tylko w następujący sposób:

    • Danych zostanie wstawiony za pomocą składni WSTAW luzem.

    • Wstawiane dane przy użyciu INSERT INTO...ZE składnią (TABLOCK).

    • Wykonując ALTER tabela jest odbudowywany tabela...ODBUDUJ instrukcja z opcją kompresji strony.

  • Nowych stron, przydzielane w sterty jako część operacje DML nie będzie używać kompresji stronę, aż odbudowany sterty.Odbuduj sterty przez usunięcie i ponowne stosowanie kompresji lub tworzenie i usuwanie indeks klastrowany.

  • Zmiana ustawienia kompresji sterty wymaga wszystkich zbudowania indeksów w tabela przebudowania, tak aby miały wskaźniki do nowych lokalizacji wiersza w stercie.

  • Można włączyć lub wyłączyć kompresję wiersza lub strony online lub offline.Włączanie kompresji w sterty jest pojedynczym wątku dla operacji online.

  • Wymagania dotyczące miejsca na dysku do włączania lub wyłączania kompresji wiersza lub strona są takie same, jak w przypadku tworzenia lub odbudowanie indeksu.Dla danych podzielonym na partycje, można zmniejszyć ilość miejsca, wymagane przez włączenie lub wyłączenie kompresji dla jednej partycji na czas.

  • Do określania stanu kompresji partycji w tabela partycjonowana, kwerendy data_compression kolumna sys.partitions wykazu widoku.

  • Podczas kompresji indeksy, liść-mogą być skompresowane strona poziom kompresji wiersza i strona.strona poziom non–liść strona kompresji nie jest wyświetlany.

  • Ze względu na ich rozmiar typów danych dużą wartość są czasami przechowywane oddzielnie od normalnych wiersz danych na stronach specjalnego przeznaczenia.Kompresja danych nie jest dostępna dla danych, które są przechowywane oddzielnie.

  • Formatowanie tabel, które wdrożone Magazyn vardecimal w SQL Server 2005 zachowa ustawienie po uaktualnieniu.Można zastosować kompresji wiersz do tabela, która ma format magazynu vardecimal. Jednak ponieważ kompresja wiersza jest nadzbiorem formatu vardecimal, nie ma żadnego powodu, aby zachować format przechowywania vardecimal.Wartości dziesiętne uzyskać nie dodatkowych kompresji przy łączeniu vardecimal formatu z kompresją wiersza.Kompresja strona można zastosować do tabela, która ma format przechowywania vardecimal; jednak vardecimal magazynu Formatuj kolumny prawdopodobnie nie spowoduje osiągnięcia dodatkowych kompresji.

    Ostrzeżenie

    SQL Server 2008 obsługuje format przechowywania vardecimal; Jednakże ponieważ wiersz poziom kompresji uzyskuje te same cele, format przechowywania vardecimal została zaniechana.Ta funkcja zostanie usunięta z przyszłej wersji programu Microsoft SQL Server. Należy unikać stosowania tej funkcji w nowych projektach oraz zaplanować modyfikację aplikacji, w których obecnie jest używana ta funkcja.

Implementacja kompresji

Podsumowanie wykonania kompresji danych, zobacz Implementacja kompresji wiersza, Implementacja kompresji strony, i Implementacja Kompresja Unicode.

Szacowanie oszczędności kompresji

Aby określić, jak zmiana stanu kompresji będą wpływać na tabela lub indeksu, użyj sp_estimate_data_compression_savings procedura składowana. sp_estimate_data_compression_savings procedura składowana jest dostępne tylko w wersjach SQL Server , obsługuje kompresję danych.

Wpływ kompresji na partycje, tabel i indeksów

Podczas korzystania z kompresji danych z tabel podzielonym na partycje i indeksów, należy pamiętać o następujące kwestie:

  • Dzielenie zakres

    Partycje są podzielone przy użyciu instrukcja ALTER PARTYCJI, zarówno partycje dziedziczyły atrybut kompresji danych oryginalnego partycji.

  • Scalanie zakres

    Gdy scalane są dwie partycje, wynikowy partycji dziedziczy atrybut kompresji danych obiekt docelowy partycji.

  • Przełączanie partycji

    Aby przełączyć się na partycji, właściwość kompresji danych partycji musi odpowiadać kompresji właściwość tabela.

  • Odbudowywanie jedną partycję lub wszystkie partycje

    Istnieją dwa warianty składni, których można zmodyfikować kompresji tabela partycjonowana lub indeksu:

    • Następująca składnia odbudowuje odwołanie partycji:

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

    • Następująca składnia odbudowuje całej tabela przy użyciu kompresji istniejące ustawienie wszystkie partycje, które nie pochodzą:

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

    Indeksy podzielonym na partycje wykonaj sama zasada przy użyciu INDEKSU ALTER.

  • Upuszczanie podzielonym na partycje indeks klastrowany

    Po upuszczeniu indeks klastrowany odpowiednie partycje sterty zachować ich ustawienie kompresji danych, chyba że modyfikacji schematu partycjonowanie na partycje.Po zmianie schematu partycjonowanie na partycje do nieskompresowany odbudowa wszystkie partycje.Aby usunąć indeks klastrowany i zmienić partycjonowanie schemat wymaga następujących kroków:

    1.Drop the clustered index.

    2.Modify the table by using the ALTER TABLE ...REBUILD ...option that specifies the compression option.

    Upuść indeks klastrowany w trybie OFFLINE jest bardzo szybko operacji, ponieważ są usuwane tylko wyższe poziomy indeksów klastrowanych.Po upuszczeniu indeks klastrowany w trybie ONLINE, SQL Server należy odbudować sterty dwa razy, raz w kroku 1, a raz w kroku 2.

Wpływ kompresji replikacji

Kiedy używasz kompresji danych z replikacja, należy zwrócić uwagę na następujące kwestie:

  • Gdy Agent migawki generuje skrypt wstępny schemat, nowy schemat będzie używać tych samych ustawień kompresji w tabela i jej indeksów.Nie można włączyć kompresję tylko tabela i nie indeksu.

  • Dla replikacja transakcyjna artykuł opcja schemat określa, jakie obiekty zależne i właściwości muszą być inicjowane przez skrypty.Aby uzyskać więcej informacji, zobacz sp_addarticle.

    Nie sprawdza agenta dystrybucji niedziałający-poziom abonentów, gdy dotyczy skryptów.Replikacja kompresji jest zaznaczone, tworzenie tabela na niedziałający-subskrybentów poziom nie powiedzie się.W przypadek mieszanych topologii, nie należy włączać replikacja kompresji.

  • Dla replikacja scalająca publikacja poziom zgodności zastępuje opcje schematu i określa obiektów schematu, które będą inicjowane przez skrypty.Aby uzyskać więcej informacji na temat poziom zgodności, zobacz W topologii replikacji przy użyciu wielu wersji programu SQL Server.

    W odniesieniu do mieszanych topologii, jeśli nie jest wymagany do obsługi nowych opcji kompresji, poziom zgodności publikacja powinny być zestaw wersja niskiego poziomu subskrybenta.Jeśli jest to wymagane, można skompresować tabel na subskrybenta po ich utworzeniu.

W poniższej tabela przedstawiono ustawienia replikacja, sterujące kompresji podczas replikacja.

Zamiarem użytkownika

Replikuj schemat partycji dla tabela lub indeksu

Replikowanie ustawień kompresji

Zachowanie skryptów

Aby replikować schemat partycji i włączenia kompresji subskrybenta na partycji.

Prawda

Prawda

Skrypty schemat partycji i ustawienia kompresji.

Aby replikować schemat partycji, ale nie kompresuj danych subskrybenta.

Prawda

Fałsz

Skrypty schemat partycji, ale nie ustawienia kompresji dla partycji.

Nie replikuj schemat partycji i nie kompresuj danych subskrybenta.

Fałsz

Fałsz

Ustawienia partycji lub kompresji nie skryptów.

Kompresowanie tabela na subskrybenta, jeżeli wszystkie partycje są kompresowane Wydawca, ale nie replikować schemat partycji.

Fałsz

Prawda

Sprawdza, czy wszystkie partycje są włączone dla kompresji.

Skrypty poza kompresji poziom tabela.

Kompresja wpływ innych SQL Server składników

Kompresja występuje w aparat magazynu i przedstawiania danych do większości innych składników SQL Server w stanie nieskompresowane.Ogranicza skutki kompresji na inne składniki do następującego:

  • Luzem importu i eksportu operacji

    Po wyeksportowaniu danych, nawet w format macierzysty, dane są dane wyjściowe w formacie wiersza bez kompresji.Może to spowodować rozmiar wyeksportowany plik danych będzie znacznie większy niż źródło danych.

    Podczas importowania danych, jeśli w tabela miejsce docelowe została włączona kompresja, przekonwertowaniu danych przez aparat magazynu w formacie skompresowanym wiersza.Może to spowodować zwiększone użycie Procesora w porównaniu z podczas importowania danych do tabela bez kompresji.

    Gdy dane są przywożone do sterty kompresji strona luzem, operacji import zbiorczy próbuje skompresować dane z kompresją strona po wstawieniu danych.

  • Kompresja nie wpływa na kopia zapasowa i przywracanie.

  • Kompresja nie wpływa na wysyłanie dziennika.

  • Włączanie kompresji może spowodować planów kwerend zmienić, ponieważ dane są przechowywane przy użyciu innej liczby stron i liczbę wierszy na strona.

  • Kompresja danych jest obsługiwany przez SQL Server Management Studio przez Kreatora kompresji danych.

Aby uruchomić Kreatora kompresji danych

  • W Eksploratorze obiektów, kliknij prawym przyciskiem myszy tabela, indeks lub indeksowany widok, wskaż Magazyn, a następnie kliknij przycisk Kompresuj.

Monitorowanie kompresji

Monitorowanie kompresji całego wystąpienie SQL Server, użyj Page compression attempts/sec i Pages compressed/sec liczników SQL Server, Access Methods Object.

Uzyskanie strona statystyki kompresji dla poszczególnych partycji, kwerenda sys.dm_db_index_operational_stats funkcja dynamicznego zarządzania.

Przykłady

Następujące przykłady tabele podzielonym na partycje i wymagają bazy danych zawierającej aplikacjami.Tworzenie bazy danych zawierającej aplikacjami, należy wykonać następujące instrukcja.

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

Aby przełączyć się do nowej bazy danych:

USE TestDatabase
GO

A.Tworzenie tabela używa kompresji wiersza

Poniższy przykład tworzy tabela i ustawia kompresji ROW.

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

B.Tworzenie tabela używa kompresji strona

Poniższy przykład utworzyć tabela i ustawia kompresji PAGE.

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

C.Ustawianie opcji DATA_COMPRESSION na podzielonym na partycjetabela

W poniższym przykładzie użyto TestDatabase tabela, który jest tworzony przy użyciu kodu wcześniej w tej sekcji.Przykład tworzy funkcja partycji i systemu, a następnie tworzy tabela partycjonowana i określa opcje kompresji dla partycji tabeli.W tym przykładzie partycji 1 jest skonfigurowany dla ROW pozostałe partycje i kompresji, są konfigurowane dla PAGE kompresji.

Aby utworzyć funkcja partycji:

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

Aby utworzyć schemat partycji:

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

Aby utworzyć tabela partycjonowana, zostały skompresowane partycji:

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.Ustawianie opcji DATA_COMPRESSION na podzielonym na partycjetabela

Poniższy przykład używa bazy danych, który jest używany w przykładzie C.Przykład tworzy tabela przy użyciu składni dla partycji nieciągłe.

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.Modyfikowanie tabela, aby zmienić kompresji

W poniższym przykładzie zmieniany kompresji nonpartitioned tabela, która jest tworzona w przykładzie A.

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

F.Modyfikowanie kompresji jedną partycję w podzielonym na partycjetabela

W poniższym przykładzie zmieniany kompresji tabela partycjonowana, która jest tworzona w przykładzie C.REBUILD PARTITION = 1 Składni powoduje tylko numer partycji 1 wymagają przebudowania.

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

Operację używa następującej składni alternatywny powoduje, że wszystkie partycje tabela wymagają przebudowania.

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

G.Modyfikowanie kompresji kilka partycji w podzielonym na partycjetabela

REBUILD PARTITION = ... Składni można odbudować tylko jedna partycja.Odbudować więcej niż jedną partycję, należy wykonać instrukcje wielu lub wykonać poniższy przykład odbudować wszystkie partycje przy użyciu bieżących ustawień kompresji dla nieokreślonego partycji.

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

H.Modyfikowanie kompresji w indeksie

Poniższy przykład używa tabela, która jest tworzona w przykładzie a i tworzy indeks dla kolumna C2.

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

Wykonanie następującego kodu zmiany indeksu strona kompresji:

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

I.Modyfikowanie kompresji jednej partycji indeks partycjonowany

Poniższy przykład tworzy indeks dla tabela partycjonowana że używa wierszy kompresji na wszystkich partycjach indeksu.

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

Aby utworzyć indeks wtedy ustawienia kompresji różnych zastosowań dla różnych partycjach, ON PARTITIONS składni.Poniższy przykład tworzy indeks dla tabela partycjonowana że używa wierszy kompresji na partycji 1 Indeks i strona kompresji na partycjach 2 do 4 indeks.

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

W poniższym przykładzie zmieniany kompresji indeks partycjonowany.

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

J.Modyfikowanie kompresji kilka partycji indeks partycjonowany

REBUILD PARTITION = ... Składni można odbudować tylko jedna partycja.Odbudować więcej niż jedną partycję, należy wykonać instrukcje wielu lub wykonać poniższy przykład odbudować wszystkie partycje przy użyciu bieżących ustawień kompresji dla nieokreślonego partycji.

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