Udostępnij za pośrednictwem

  • Artykuł

Creating Compressed Tables and Indexes

SQL Server 2008 obsługuje kompresję wiersza i strona dla tabel i indeksów.Kompresja danych mogą być skonfigurowane dla następujących obiektów bazy danych:

  • Całej tabela, które są przechowywane w postaci stosu.

  • Całej tabela, który jest przechowywany jako indeks klastrowany.

  • Całe nieklastrowany indeks.

  • Widok całej indeksowany.

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

Ustawienie kompresji tabela nie jest automatycznie stosowane do jej ponownego zbudowania indeksów nie klastrowanych.Każdy indeks musi być zestaw indywidualnie.Kompresja nie jest dostępna dla tabele systemowe.Tabele oraz indeksy mogą być skompresowane, utworzone przy użyciu TWORZENIE tabela and TWORZENIE INDEKSU instrukcji.Aby zmienić stan kompresji użycia tabela, indeks lub partycji, ALTER tabela or ZMIANA INDEKSU instrukcji.

Uwaga

Jeśli jest pofragmentowany istniejących danych, można zmniejszyć rozmiar indeksu przebudowywanie indeksu zamiast korzystać z kompresji.Współczynnik wypełnienia indeksu będą stosowane podczas odbudowywania indeksu, który potencjalnie może zwiększyć rozmiar indeksu.Aby uzyskać więcej informacji zobaczFill Factor.

Uwagi dotyczące kiedy użytkownik Użyj wiersza i strona kompresji

Korzystając z kompresji wiersza i strona, weź pod uwagę następujące kwestie:

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

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

  • Tabela nie może być włączony do kompresji, jeśli rozmiar maksymalny wiersza plus dodatkowe obciążenie związane z kompresji przekracza rozmiar maksymalny wiersza 8060 bajtów.Na przykład tabela zawierającej kolumny c1 char(8000) i c2 char(53) Nie można skompresować ze względu na koszty dodatkowe kompresji. Gdy używany jest format przechowywania vardecimal, sprawdzanie rozmiar wiersza jest wykonywane, gdy jest włączony format.Kompresji wiersza i strona sprawdzanie rozmiar wiersza jest wykonywane, gdy obiekt jest początkowo skompresowany i następnie sprawdzać, jak każdy wiersz jest wstawione lub zmodyfikowane.Kompresja wymuszają następujące dwie reguły:

    • Zawsze musi powiodła się aktualizacja typ stałej długości.

    • Wyłączanie kompresji danych należy zawsze powiodła się.Nawet wtedy, gdy wiersz skompresowanych mieści się strona, co oznacza, że jest on 8060 bajtów; SQL Server zapobiega aktualizacji, które mogą nie mieścić się w wierszu, gdy jest on bez kompresji.

  • Po określeniu listę partycji typu kompresji zestaw na wiersz, strona lub brak w poszczególnych partycji.Jeśli na liście partycji nie zostanie określony, wszystkie partycje są zestaw z właściwość kompresji danych, która jest określona w instrukcja.Po utworzeniu tabela lub indeksu, kompresji danych to zestaw / / / None, chyba że określono inaczej.Podczas modyfikowania tabela istniejącej kompresji jest zachowywany, o ile nie określono inaczej.

  • Jeżeli podano listę partycji lub partycji, która jest poza zakresem, zostanie wygenerowany błąd.

  • Ponownego zbudowania indeksów nie klastrowanych nie dziedziczą właściwość kompresji w tabela.Aby skompresować indeksów, należy jawnie zestaw właściwość kompresji indeksów.Domyślnie ustawienia kompresji dla indeksów zostanie ustawiona wartość NONE podczas tworzenia indeksu.

  • Po utworzeniu indeks klastrowany w sterty indeks klastrowany dziedziczy stanu kompresji sterty, pod warunkiem, że nie określono stan kompresji alternatywnych.

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

    • Za pomocą składni BULK INSERT jest wstawiane dane.

    • Wstawiane dane za pomocą INSERT INTO...ZE składnią (TABLOCK).

    • Tabela jest ponownie przez wykonywanie instrukcji ALTER TABLE...ODBUDOWANIE instrukcja przy użyciu opcji kompresji strona.

  • Nowe strona przydzielonych w sterty jako część operacje DML nie będzie używać kompresji strona, aż do sterty jest ponownie.Odbudowanie sterty przez usunięcie i ponowne stosowanie kompresji lub tworzenie i usuwanie indeks klastrowany.

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

  • Można włączyć lub wyłączyć kompresję wiersz lub strona w trybie online lub offline.Włączanie kompresji w sterty jest pojedynczym wątku dla operacji w trybie 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 przebudowywanie indeksu.W przypadku danych podzielonym na partycje można zmniejszyć miejsca, które są wymagane przez włączenie lub wyłączenie kompresji dla jednej partycji naraz.

  • Aby sprawdzić stan kompresji partycji tabela partycjonowana, kwerendy kolumna data_compression sys.partitions widoku wykazu.

  • Jeżeli kompresujesz indeksów strona poziom liść mogą być skompresowane z kompresją wiersza i strona.strona poziom Non–liść kompresji strona nie jest wyświetlany.

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

  • Tabele, które wdrożone format przechowywania vardecimal w SQL Server 2005 zachowa ustawienie po uaktualnieniu. Można zastosować kompresji wierszy do tabela w formacie vardecimal magazynowania.Jednak ponieważ kompresji wiersza jest podzbiorem vardecimal formatu magazynu, ma powodów zachować format przechowywania vardecimal.Wartości dziesiętne uzyskanie dodatkowych Kompresja nie vardecimal formatu magazynu w przypadku łączenia z kompresją wiersza.Kompresja strona można zastosować do tabela, która ma format przechowywania vardecimal; jednak kolumn format przechowywania vardecimal prawdopodobnie nie osiągną dodatkowe kompresji.

    Uwaga

    SQL Server 2008 obsługuje format przechowywania vardecimal; jednak, ponieważ kompresowanie poziom wiersza uzyskuje tych samych celów, format przechowywania vardecimal została zaniechana.This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Kompresja implementacji

Aby wyświetlić podsumowanie wykonania kompresji danych zobacz Row Compression Implementation i Strona kompresji implementacji.

Szacowanie niższe kompresji

Aby ustalić, jak zmiana stanu kompresji wpłynie na tabela lub indeksu, należy użyć sp_estimate_data_compression_savings procedura przechowywana.procedura przechowywana sp_estimate_data_compression_savings jest dostępna tylko w wersji systemu SQL Server który obsługuje kompresję danych.

Wpływ kompresja podzielony na partycje, tabele oraz indeksy

Podczas korzystania z kompresji danych z tabelami podzielonym na partycje i indeksów, należy zwrócić uwagę następujące kwestie:

  • Dzielenie zakres

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

  • Scalanie zakres

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

  • Przełączanie partycji

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

  • Odbudowywanie jedną partycję lub wszystkie partycje

    Istnieją dwie odmiany składni, można użyć do zmodyfikowania kompresji tabela partycjonowana lub indeksu:

    • Następująca składnia odtwarza tylko odwołania partycji:

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

    • Następująca składnia odbudowania całej tabela za pomocą kompresji istniejące ustawienie dla dowolnej partycji, które się nie odwołuje się do:

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

    Indeksy podzielonym na partycje, postępuj zgodnie z tej samej zasadzie przy użyciu instrukcji ALTER indeks.

  • Upuszczanie podzielonym na partycje indeks klastrowany

    Po upuszczeniu indeks klastrowany odpowiednie partycje sterty zachować swoje ustawienia kompresji danych, chyba że zostanie zmodyfikowany schemat partycjonowanie na partycje.Po zmianie schematu partycjonowanie na partycje, wszystkie partycje są przebudowywane do stanu bez kompresji.Aby usunąć indeks klastrowany i zmienić schemat partycjonowanie na partycje 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.

    OFFLINE upuścić indeks klastrowany jest operacją bardzo szybko, ponieważ tylko wyższe poziomy indeksów klastrowanych są usuwane.Jeżeli indeks klastrowany zostanie usunięte w trybie ONLINE, SQL Server należy odbudować sterty dwa razy, raz w kroku 1, a raz w kroku 2.

Wpływ kompresja replikacja

Podczas korzystania z kompresji danych z replikacją, należy zwrócić uwagę następujące kwestie:

  • migawka Agent generując skryptu początkowego schematu nowego schematu użyje te same ustawienia kompresji dla tabela i jej indeksów.Nie można włączyć kompresję tylko tabela i nie indeksu.

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

    Agent dystrybucji nie sprawdza niedziałający-poziom subskrybentów, gdy ma ona zastosowanie skryptów.Zaznaczenie replikacja, kompresji, tworzenie tabela na niedziałający-subskrybentów poziom nie powiedzie się.przypadek braku topologia mieszana nie należy włączać replikacja kompresji.

  • Do replikacja łączenia publikacja poziom zgodności zastępuje opcje schematu i określa obiektów schematu, który ma być przetwarzane przez skrypty.Aby uzyskać więcej informacji na temat poziom zgodności zobacz Using Multiple Versions of SQL Server in a Replication Topology.

    przypadek braku topologia mieszana Jeśli nie jest wymagane do obsługi nowych opcji kompresji, poziom zgodności publikacja należy ustawić na wersja subskrybent niskiego poziomu.W razie potrzeby można kompresować tabel na subskrybent po ich utworzeniu.

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

Opcje użytkownika

Replikowanie partycji schematu dla tabela lub indeksu

Replikowanie ustawienia kompresji

Zachowanie wykonywanie wykonywanie skryptów

Aby replikować schemat partycji i włączyć kompresji w subskrybent na partycji.

Prawda

Prawda

Skrypty, ustawienia kompresji i schemat partycji.

Aby replikować schemat partycji, ale nie kompresuj danych na subskrybent.

Prawda

Fałsz

Skrypty schemat partycji, ale nie ustawienia kompresji dla partycji.

Aby nie replikować schemat partycji i nie kompresuj danych na subskrybent.

Fałsz

Fałsz

Ustawienia partycji lub kompresja nie skryptów.

Do tabela na subskrybent kompresji, jeśli wszystkie partycje są kompresowane w wydawcę, ale nie replikować schemat partycji.

Fałsz

Prawda

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

Skrypty poza kompresji poziom tabela.

Wpływ kompresja na inne składniki programu SQL Server

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

  • Zbiorcza 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ć, że rozmiar pliku dokumentu będzie znacznie większy niż urządzenie źródłowe danych.

    Podczas importowania danych, jeśli w tabela miejsce docelowe zostało włączone dla kompresji, danych jest konwertowany na format skompresowanego wiersza przez aparat magazynu.Może to spowodować zwiększone użycie PROCESORA i podczas importowania danych do tabela programu bez kompresji.

    Gdy dane są zbiorcze importowane do sterty kompresji strona, operacji import zbiorczy próbuje skompresować dane przy użyciu kompresji strona po wstawieniu danych.

  • Kompresja nie mają wpływu na kopię zapasową i przywrócić.

  • Kompresja nie ma wpływu na wysyłanie dziennika.

  • Włączanie kompresji może spowodować, że planów kwerend zmienić, ponieważ dane są przechowywane przy użyciu różnych liczbę stron i liczbę wierszy na każdej stronie.

  • Kompresja danych jest obsługiwana przez SQL Server Management Studio za pomocą Kreator kompresji danych.

Aby uruchomić Kreatora kompresji danych

  • W Eksploratorze obiektów kliknij prawym przyciskiem myszy tabela, indeks lub widok indeksowany, punkt do Magazyn, a następnie kliknij przycisk Kompresja.

Monitorowanie kompresji

Aby monitorować kompresji całego wystąpienie SQL Server, za pomocą kompresji stron próby/s i strona skompresowane s liczniki programu SQL Server, metody dostępu do obiektów.

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

Przykłady

Niektóre z poniższych przykładów za pomocą tabel podzielonym na partycje i wymagają bazy danych zawierającej filegroups.Aby utworzyć bazę danych, który ma filegroups, należy wykonać następującą instrukcję.

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

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

USE TestDatabase
GO

A.Tworzenie tabela, która używa kompresji wierszy

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

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

B.Tworzenie tabela, która używa kompresji strona

W poniższym przykładzie utworzyć tabela i ustawienie kompresji PAGE.

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

C.Ustawienie opcji DATA_COMPRESSION dla tabela partycjonowana

W poniższym przykładzie użyto TestDatabase Tabela jest tworzona za pomocą kodu podane wcześniej w tej sekcji. W przykładzie 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 skonfigurowano pod kątem ROW Kompresja, a pozostałe partycje są konfigurowane dla PAGE kompresja.

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

Do utworzenia tabela partycjonowana, które 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.Ustawienie opcji DATA_COMPRESSION dla tabela partycjonowana

W poniższym przykładzie użyto bazy danych, który jest używany w przykładzie C.W przykładzie jest tworzona tabela przy użyciu składni dla nieciągłe partycji.

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 zmienia kompresji nonpartitioned tabela, która jest tworzona w przykładzie A.

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

F.Modyfikowanie kompresji jednej partycji tabela partycjonowana

W poniższym przykładzie zmienia kompresji podzielonym na partycje tabela, która jest tworzona w przykładzie C.The REBUILD PARTITION = 1 syntax causes only partition number 1 to be rebuilt.

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

Tej samej operacji, która jest używana następująca składnia alternatywne powoduje, że wszystkie partycje w tabela, która ma zostać ponownie zbudowana.

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

G.Modyfikowanie kompresji kilka partycji tabela partycjonowana

The REBUILD PARTITION = ... syntax can rebuild only one partition.Aby odbudować więcej niż jedną partycję, należy wykonać instrukcje wielu lub wykonanie w poniższym przykładzie, aby ponownie utworzyć 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

W poniższym przykładzie zastosowano 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

Wykonaj następujący kod w celu indeks zostanie zmieniony strona kompresji:

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

I.Modyfikowanie kompresji tylko jedną partycję w indeks partycjonowany

Poniższy przykład tworzy indeks dla tabela partycjonowana, która używa kompresji wierszy na wszystkich partycjach plik indeksu.

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

Aby utworzyć indeks tak, to znaczy ustawienia kompresji różnych zastosowań na różnych partycjach, należy użyć ON PARTITIONS Składnia. Poniższy przykład tworzy indeks dla tabela partycjonowana, która używa kompresji wierszy na partycji 1 strona indeksu i kompresji na partycje 2 do 4 indeksu.

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 zmienia kompresję indeks partycjonowany.

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

J.Modyfikowanie kompresji kilka partycji w indeksie podzielonym na partycje

The REBUILD PARTITION = ... syntax can rebuild only one partition.Aby odbudować więcej niż jedną partycję, należy wykonać instrukcje wielu lub wykonanie w poniższym przykładzie, aby ponownie utworzyć 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