Udostępnij za pomocą

sp_estimate_data_compression_savings (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBaza danych SQL w usłudze Microsoft Fabric

Zwraca aktualny rozmiar żądanego obiektu i szacuje rozmiar obiektu dla żądanego stanu kompresji. Kompresję można ocenić dla całych tabel lub ich części. Obejmuje to kopce, indeksy klastrowane, indeksy nieklasterowe, indeksy magazynu kolumn, widoki indeksowane oraz partycje tabel i indeksów. Obiekty można kompresować za pomocą kompresji wierszowej, strony, columnstore lub columnstore. Jeśli tabela, indeks lub partycja są już skompresowane, możesz użyć tej procedury, aby oszacować rozmiar tabeli, indeksu lub partycji, jeśli są one ponownie kompresowane lub przechowywane bez kompresji.

Procedura sys.sp_estimate_data_compression_savings przechowywana w systemie jest dostępna w Azure SQL Database oraz Azure SQL Managed Instance.

Począwszy od SQL Server 2022 (16.x), możesz kompresować dane XML poza wierszami w kolumnach za pomocą typu xml , co zmniejsza wymagania dotyczące pamięci i przechowywania. Aby uzyskać więcej informacji, zobacz CREATE TABLE i CREATE INDEX. sp_estimate_data_compression_savings obsługuje estymację kompresji XML.

Uwaga / Notatka

Kompresja i sp_estimate_data_compression_savings nie są dostępne we wszystkich edycjach SQL Server. Aby uzyskać listę funkcji obsługiwanych przez wersje programu SQL Server, zobacz Editions i obsługiwane funkcje programu SQL Server 2022.

Aby oszacować rozmiar obiektu, jeśli użyje żądanego ustawienia kompresji, ta procedura składowana próbkuje obiekt źródłowy i ładuje te dane do równoważnej tabeli i indeksu utworzonego w .tempdb Tabela lub indeks utworzony w jest tempdb następnie kompresowany do żądanego ustawienia, a szacowane oszczędności kompresji są obliczane.

Aby zmienić stan kompresji tabeli, indeksu lub partycji, użyj instrukcji ALTER TABLE lub ALTER INDEX . Ogólne informacje o kompresji można znaleźć w artykule Kompresja danych.

Uwaga / Notatka

Jeśli istniejące dane są fragmentowane, możesz być w stanie zmniejszyć ich rozmiar bez kompresji, odbudowując indeks. W przypadku indeksów współczynnik wypełnienia będzie stosowany podczas przebudowy indeksu. To może zwiększyć rozmiar indeksu.

Transact-SQL konwencje składni

Składnia

sp_estimate_data_compression_savings
    [ @schema_name = ] N'schema_name'
    , [ @object_name = ] N'object_name'
    , [ @index_id = ] index_id
    , [ @partition_number = ] partition_number
    , [ @data_compression = ] N'data_compression'
    [ , [ @xml_compression = ] xml_compression ]
[ ; ]

Arguments

[ @schema_name = ] N'nazwa_schematu'

Nazwa schematu bazy danych zawierającego tabelę lub widok indeksowany. @schema_name to sysname, bez domyślnego zapisu. Jeśli @schema_name , NULLużywa się domyślnego schematu aktualnego użytkownika.

[ @object_name = ] N'object_name'

Nazwa tabeli lub widoku indeksowego, na którym znajduje się indeks. @object_name to nazwa sysname, bez domyślnego ustawienia.

[ @index_id = ] index_id

Identyfikator indeksu. @index_id jest int i może mieć jedną z następujących wartości:

  • numer ID indeksu
  • NULL
  • 0 jeśli object_id jest kupą

Aby zwrócić informacje dla wszystkich indeksów dla tabeli bazowej lub widoku, określ NULL. Jeśli określisz NULL, musisz również określić NULL@partition_number.

[ @partition_number = ] partition_number

Numer partycji w obiekcie. @partition_number jest int i może mieć jedną z następujących wartości:

  • numer partycji indeksu lub kopca
  • NULL
  • 1 dla indeksu lub kopca bez partycjonowania

Aby określić partycję, możesz także określić funkcję $PARTITION . Aby zwrócić informacje dla wszystkich partycji obiektu będącego właścicielem, określmy NULL.

[ @data_compression = ] N'data_compression'

Określa rodzaj kompresji, którą ma się ocenić. @data_compression to nvarchar(60) i może mieć jedną z następujących wartości:

  • NONE
  • ROW
  • PAGE
  • COLUMNSTORE
  • COLUMNSTORE_ARCHIVE

Dla SQL Server 2022 (16.x) i późniejszych wersji NULL również jest możliwa wartość. @data_compression nie może być NULL , jeśli @xml_compression tak NULL.

[ @xml_compression = ] xml_compression

Dotyczy do: SQL Server 2022 (16.x) i nowszych wersji, Azure SQL Database oraz Azure SQL Managed Instance

Określa, czy obliczać oszczędności dla kompresji XML. @xml_compression jest bitem i może mieć jedną z następujących wartości:

  • NULL (ustawienie domyślne)
  • 0
  • 1

@xml_compression nie może byćNULL, jeśli @data_compression jest .NULL

Zwracanie wartości kodu

0 (powodzenie) lub 1 (niepowodzenie).

Zestaw wyników

Poniższy zbiór wyników jest zwracany, aby podać aktualny i szacowany rozmiar tabeli, indeksu lub partycji.

Nazwa kolumny Typ danych Description
object_name sysname Nazwa tabeli lub widoku indeksowanego.
schema_name sysname Schemat tabeli lub widoku indeksowanego.
index_id int Identyfikator indeksu:

0 = Sterta
1 = Indeks klastrowany
>1 = Indeks nieklastrowany
partition_number int Numer partycji. Zwraca 1 dla tabeli lub indeksu niepodzielonego.
size_with_current_compression_setting (KB) bigint Rozmiar żądanej tabeli, indeksu lub partycji w obecnej formie.
size_with_requested_compression_setting (KB) bigint Szacowany rozmiar tabeli, indeksu lub partycji wykorzystującej żądane ustawienie kompresji; i, jeśli to możliwe, istniejący współczynnik wypełnienia, zakładając, że nie ma fragmentacji.
sample_size_with_current_compression_setting (KB) bigint Rozmiar próbki przy aktualnym ustawieniu kompresji. Ten rozmiar obejmuje wszelkie fragmentacje.
sample_size_with_requested_compression_setting (KB) bigint Rozmiar próbki tworzonej przy użyciu żądanego ustawienia kompresji; oraz, jeśli dotyczy, istniejący współczynnik wypełnienia i brak fragmentacji.

Uwagi

Użyj go sp_estimate_data_compression_savings do oszacowania oszczędności, które mogą nastąpić, gdy włączysz tabelę lub partycję dla wiersza, strony, magazynu kolumn, archiwum kolumn lub kompresji XML. Na przykład, jeśli średni rozmiar wiersza można zmniejszyć o 40 procent, możesz potencjalnie zmniejszyć rozmiar obiektu o 40 procent. Możesz nie otrzymać oszczędności miejsca, ponieważ zależy to od współczynnika wypełnienia i wielkości rzędu. Na przykład, jeśli masz wiersz o długości 8 000 bajtów i zmniejszysz jego rozmiar o 40 procent, nadal możesz zmieścić tylko jeden wiersz na stronie danych. Nie ma żadnych oszczędności.

Jeśli wyniki uruchamiania sp_estimate_data_compression_savings na nieskompresowanej tabeli lub indeksie wskazują, że rozmiar wzrośnie, oznacza to, że wiele wierszy wykorzystuje niemal całą precyzję typów danych, a dodanie niewielkiego narzutu potrzebnego do skompresowanego formatu jest większe niż oszczędności kompresji. W tych rzadkich przypadkach nie włączaj kompresji.

Jeśli tabela jest już włączona do kompresji, możesz użyć do sp_estimate_data_compression_savings oszacowania średniego rozmiaru wiersza, jeśli tabela nie jest skompresowana.

Podczas tej operacji na stole uzyskiwana jest blokada z zamiarem współdzielonym (IS). Jeśli blokady IS nie można uzyskać, procedura zostaje zablokowana. Tabela jest skanowana pod domyślnym poziomem zadeklarowanej izolacji.

Jeśli żądane ustawienie kompresji jest takie samo jak obecne ustawienie kompresji, procedura przechowywana zwraca szacowany rozmiar bez fragmentacji danych, wykorzystując istniejący współczynnik wypełnienia dla indeksów obiektu źródłowego.

Jeśli indeks lub identyfikator partycji nie istnieje, nie zwraca się żadnych wyników.

Permissions

Wymaga SELECT zgody na tabelę, VIEW DATABASE STATE bazę VIEW DEFINITION danych zawierającą tabelę oraz na .tempdb

Ograniczenia

W SQL Server 2017 (14.x) i wcześniejszych wersjach procedura ta nie dotyczyła indeksów columnstore, dlatego nie akceptowała parametrów COLUMNSTORE kompresji danych oraz COLUMNSTORE_ARCHIVE. W wersjach SQL Server 2019 (15.x) i nowszych, a także w Azure SQL Database i Azure SQL Managed Instance, indeksy columnstore mogą być używane zarówno jako obiekt źródłowy do estymacji, jak i jako żądany typ kompresji.

Gdy metadaneMemory-Optimized TempDB są włączone, nie jest wspierane tworzenie indeksów columnstore na tabelach tymczasowych. Z powodu tego ograniczenia sp_estimate_data_compression_savings nie jest obsługiwany z parametrami COLUMNSTORE kompresji danych i COLUMNSTORE_ARCHIVE Memory-Optimized gdy metadane TempDB są włączone.

Uwagi dotyczące indeksów columnstore

Począwszy od SQL Server 2019 (15.x), a także w Azure SQL Database i Azure SQL Managed Instance, obsługuje sp_estimate_compression_savings estymację zarówno w columnstore, jak i kompresję archiwum columnstore. W przeciwieństwie do kompresji stron i wierszy, zastosowanie kompresji columnstore do obiektu wymaga utworzenia nowego indeksu columnstore. Z tego powodu, korzystając COLUMNSTORE z opcji i COLUMNSTORE_ARCHIVE tej procedury, typ obiektu źródłowego dostarczanego procedurze determinuje typ indeksu columnstore używanego do skompresowanego oszacowania rozmiaru. Poniższa tabela ilustruje obiekty referencyjne używane do szacowania oszczędności kompresji dla każdego typu obiektu źródłowego, gdy parametr @data_compression jest ustawiony na albo COLUMNSTORECOLUMNSTORE_ARCHIVEalbo .

Obiekt źródłowy Obiekt odniesienia
**Kupa Klastrowany indeks kolumnowy
Indeks klastrowany Klastrowany indeks kolumnowy
Indeks nieklastrowany Indeks nieklastrowanego składu kolumn (w tym kolumny klucza oraz wszystkie kolumny przypisanego indeksu nieklastrowanego oraz kolumnę partycji tabeli, jeśli taka istnieje)
Indeks nieklastrowanego magazynu kolumn Indeks nieklastrowanego columnstore (w tym te same kolumny co podany indeks nieklastrowanego columnstore)
sklasteryzowany indeks kolumnowego magazynu danych Klastrowany indeks kolumnowy

Uwaga / Notatka

Podczas szacowania kompresji magazynu kolumn z obiektu źródłowego wiersza (indeks klastrowany, indeks nieklastrowany lub kopc), jeśli w obiekcie źródłowym są kolumny mające typ danych nieobsługiwany w indeksie magazynu kolumn, to zostanie to błędne. sp_estimate_compression_savings

Podobnie, gdy parametr @data_compression jest ustawiony na NONE, ROW, lub PAGE , a obiekt źródłowy to indeks columnstore, poniższa tabela przedstawia użyte obiekty referencyjne.

Obiekt źródłowy Obiekt odniesienia
sklasteryzowany indeks kolumnowego magazynu danych Sterta
Indeks nieklastrowanego magazynu kolumn Indeks nieklastrowany (wliczając kolumny zawarte w indeksie nieklastrowanego magazynu kolumn jako kolumny klucza oraz kolumnę partycji tabeli, jeśli taka istnieje, jako kolumnę uwzględniającą)

Uwaga / Notatka

Szacując kompresję rowstore (BRAK, WIERSZ lub STRONA) z obiektu źródłowego columnstore, upewnij się, że indeks źródłowy nie zawiera więcej niż 32 kolumny klucza, ponieważ jest to limit obsługiwany w indeksie rowstore (nieklastrowanym).

Przykłady

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2025 lub AdventureWorksDW2025, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

A. Oszacowanie oszczędności dzięki kompresji ROW

Poniższy przykład szacuje rozmiar tabeli Production.WorkOrderRouting , jeśli zostanie ona skompresowana za pomocą ROW kompresji.

EXECUTE sys.sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'ROW';
GO

B. Oszacowanie oszczędności dzięki kompresji PAGE i XML

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje

Poniższy przykład szacuje rozmiar tabeli Production.ProductModel , jeśli zostanie skompresowana za pomocą PAGE kompresji i wartość @xml_compression jest włączona.

EXECUTE sys.sp_estimate_data_compression_savings 'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO

Treści powiązane

  • Last updated on