sp_estimate_data_compression_savings (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Gibt die aktuelle Größe des angeforderten Objekts zurück und schätzt die Objektgröße für den angeforderten Komprimierungsstatus. Die Komprimierung kann für ganze Tabellen oder Teile von Tabellen ermittelt werden. Dazu gehören Heaps, gruppierte Indizes, nicht gruppierte Indizes, Spaltenspeicherindizes, indizierte Ansichten und Tabellen- und Indexpartitionen. Die Objekte können mithilfe von Zeilen-, Seiten-, Spaltenspeicher- oder Spaltenspeicherarchivkomprimierung komprimiert werden. Wenn die Tabelle, der Index oder die Partition bereits komprimiert ist, können Sie dieses Verfahren verwenden, um die Größe der Tabelle, des Indexes oder der Partition zu schätzen, wenn sie erneut komprimiert oder ohne Komprimierung gespeichert wird.

Die sys.sp_estimate_data_compression_savings gespeicherte Systemprozedur ist in Azure SQL-Datenbank und Azure SQL verwaltete Instanz verfügbar.

Ab SQL Server 2022 (16.x) können Sie XML-Auszeilendaten in Spalten mithilfe des XML-Datentyps komprimieren und die Speicher- und Speicheranforderungen reduzieren. Weitere Informationen finden Sie unter CREATE TABLE und CREATE INDEX. sp_estimate_data_compression_savings unterstützt XML-Komprimierungsschätzungen.

Hinweis

Komprimierung und sp_estimate_data_compression_savings sind in jeder Edition von SQL Server nicht verfügbar. Eine Liste der Funktionen, die von den SQL Server-Editionen unterstützt werden, finden Sie unter Editionen und unterstützte Funktionen von SQL Server 2022.

Um die Größe des Objekts zu schätzen, wenn die angeforderte Komprimierungseinstellung verwendet werden soll, wird in dieser gespeicherten Prozedur das Quellobjekt beispielt und diese Daten in eine entsprechende Tabelle und einen in tempdbdiesem Index erstellten Index geladen. Die in der Tabelle oder den Index tempdb erstellte Tabelle wird dann in die angeforderte Einstellung komprimiert, und die geschätzten Komprimierungseinsparungen werden berechnet.

Verwenden Sie die ALTER TABLE- oder ALTER INDEX-Anweisungen, um den Komprimierungszustand einer Tabelle, eines Indexes oder einer Partition zu ändern. Allgemeine Informationen zur Komprimierung finden Sie unter "Datenkomprimierung".

Hinweis

Wenn die vorhandenen Daten fragmentiert sind, können Sie ihre Größe möglicherweise ohne Komprimierung verringern, indem Sie den Index neu erstellen. Für Indizes wird der Füllfaktor während der Neuerstellung des Indexes angewendet. Dadurch könnte die Größe des Indexes zunehmen.

Transact-SQL-Syntaxkonventionen

Syntax

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 ]
[ ; ]

Argumente

[ @schema_name = ] N'schema_name'

Der Name des Datenbankschemas, das die Tabellen- oder indizierte Ansicht enthält. @schema_name ist "sysname" ohne Standard. Wenn @schema_name ist NULL, wird das Standardschema des aktuellen Benutzers verwendet.

[ @object_name = ] N'object_name'

Der Name der Tabellen- oder indizierten Ansicht, auf der sich der Index befindet. @object_name ist "sysname" ohne Standard.

[ @index_id = ] index_id

Die ID des Indexes. @index_id ist int und kann einer der folgenden Werte sein:

  • die ID-Nummer eines Indexes
  • NULL
  • 0 wenn object_id ein Heap ist

Geben Sie an, NULLum Informationen für alle Indizes für eine Basistabelle oder -ansicht zurückzugeben. Wenn Sie angebenNULL, müssen Sie auch für @partition_number angebenNULL.

[ @partition_number = ] partition_number

Die Partitionsnummer im Objekt. @partition_number ist int und kann einen der folgenden Werte aufweisen:

  • die Partitionsnummer eines Indexes oder Heaps
  • NULL
  • 1 für einen nicht partitionierten Index oder Heap

Um die Partition anzugeben, können Sie auch die $PARTITION-Funktion angeben. Geben Sie an, NULLum Informationen für alle Partitionen des eigenen Objekts zurückzugeben.

[ @data_compression = ] N'data_compression'

Gibt den Typ der komprimierung an, die ausgewertet werden soll. @data_compression ist nvarchar(60) und kann einen der folgenden Werte aufweisen:

  • NONE
  • ROW
  • PAGE
  • COLUMNSTORE
  • COLUMNSTORE_ARCHIVE

Für SQL Server 2022 (16.x) und höhere Versionen NULL ist auch ein möglicher Wert. @data_compression kann nicht seinNULL, wenn @xml_compression istNULL.

[ @xml_compression = ] xml_compression

Gilt für: SQL Server 2022 (16.x) und höhere Versionen, Azure SQL-Datenbank und Azure SQL verwaltete Instanz

Gibt an, ob Einsparungen für die XML-Komprimierung berechnet werden sollen. @xml_compression ist Bit und kann einer der folgenden Werte sein:

  • NULL (Standard)
  • 0
  • 1

@xml_compression kann nicht seinNULL, wenn @data_compression istNULL.

Rückgabecodewerte

0 (erfolgreich) oder 1 Fehler.

Resultset

Das folgende Resultset wird zurückgegeben, damit Informationen zur aktuellen und geschätzten Größe von Tabelle, Index oder Partition bereitgestellt werden.

Spaltenname Datentyp Beschreibung
object_name sysname Der Name der Tabelle oder indizierten Sicht.
schema_name sysname Das Schema der Tabelle oder indizierten Sicht.
index_id int Index-ID eines Index:

0 = Heap
1 = Gruppierter Index
>1 = Nicht gruppierter Index
partition_number int Partitionsnummer. Gibt 1 für eine nicht partitionierte Tabelle oder einen Index zurück.
size_with_current_compression_setting (KB) bigint Die Größe der angeforderten, vorhandenen Tabelle, des Indexes oder der Partition.
size_with_requested_compression_setting (KB) bigint Geschätzte Größe der Tabelle, des Indexes oder der Partition, die die angeforderte Komprimierungseinstellung verwendet; und gegebenenfalls den vorhandenen Füllfaktor, und vorausgesetzt, es gibt keine Fragmentierung.
sample_size_with_current_compression_setting (KB) bigint Die Größe der Stichprobe mit der aktuellen Komprimierungseinstellung. Diese Größe umfasst jede Fragmentierung.
sample_size_with_requested_compression_setting (KB) bigint Die Größe der Stichprobe, die mithilfe der angeforderten Komprimierungseinstellung erstellt wird, mit vorhandenem Füllfaktor (sofern zutreffend) und ohne Fragmentierung.

Hinweise

Wird verwendet sp_estimate_data_compression_savings , um die Einsparungen zu schätzen, die auftreten können, wenn Sie eine Tabelle oder Partition für Zeile, Seite, Spaltenspeicher, Spaltenspeicherarchiv oder XML-Komprimierung aktivieren. Wenn beispielsweise die durchschnittliche Größe der Zeile um 40 Prozent reduziert werden kann, können Sie die Größe des Objekts möglicherweise um 40 Prozent verringern. Möglicherweise erzielen Sie keine Platzeinsparung, weil dies vom Füllfaktor und von der Zeilengröße abhängt. Wenn Sie beispielsweise eine Zeile mit einer Länge von 8.000 Bytes haben und ihre Größe um 40 Prozent verringern, können Sie immer noch nur eine Zeile auf einer Datenseite anpassen. Es gibt keine Einsparungen.

Wenn die Ergebnisse der Ausführung sp_estimate_data_compression_savings in einer nicht komprimierten Tabelle oder einem Index darauf hindeuten, dass die Größe erhöht wird, bedeutet dies, dass viele Zeilen fast die gesamte Genauigkeit der Datentypen verwenden, und das Hinzufügen des geringen Aufwands, der für das komprimierte Format erforderlich ist, ist mehr als die Einsparungen bei der Komprimierung. Aktivieren Sie in diesem seltenen Fall keine Komprimierung.

Wenn eine Tabelle bereits für die Komprimierung aktiviert ist, können sp_estimate_data_compression_savings Sie die durchschnittliche Größe der Zeile schätzen, wenn die Tabelle nicht komprimiert ist.

Während dieses Vorgangs wird eine Freigegebene Absichtssperre (Intent Shared, IS) für die Tabelle abgerufen. Wenn eine IS-Sperre nicht abgerufen werden kann, wird die Prozedur blockiert. Die Tabelle wird unter der Standardmäßigen Isolationsstufe für lesesicherte Lesevorgänge gescannt.

Wenn die angeforderte Komprimierungseinstellung mit der aktuellen Komprimierungseinstellung übereinstimmt, gibt die gespeicherte Prozedur die geschätzte Größe ohne Datenfragmentierung zurück, wobei der vorhandene Füllfaktor für Indizes des Quellobjekts verwendet wird.

Wenn der Index oder die Partitions-ID nicht vorhanden ist, werden keine Ergebnisse zurückgegeben.

Berechtigungen

Erfordert SELECT Berechtigungen für die Tabelle und VIEW DEFINITION für die Datenbank, VIEW DATABASE STATE die die Tabelle und die Datei tempdbenthält.

Begrenzungen

In SQL Server 2017 (14.x) und früheren Versionen gilt dieses Verfahren nicht für Spaltenspeicherindizes, und daher wurden die Datenkomprimierungsparameter und COLUMNSTORE_ARCHIVE-parameter COLUMNSTORE nicht akzeptiert. In SQL Server 2019 (15.x) und höheren Versionen sowie in Azure SQL-Datenbank und Azure SQL verwaltete Instanz können Spaltenspeicherindizes sowohl als Quellobjekt zur Schätzung als auch als angeforderter Komprimierungstyp verwendet werden.

Wenn speicheroptimierte TempDB-Metadaten aktiviert sind, wird das Erstellen von Spaltenspeicherindizes für temporäre Tabellen nicht unterstützt. Aufgrund dieser Einschränkung werden die Parameter für COLUMNSTORE_ARCHIVE die COLUMNSTORE Datenkomprimierung nicht unterstützt, sp_estimate_data_compression_savings wenn speicheroptimierte TempDB-Metadaten aktiviert sind.

Überlegungen für Spaltenspeicherindizes

Ab SQL Server 2019 (15.x) und in Azure SQL-Datenbank und Azure SQL verwaltete Instanz sp_estimate_compression_savings unterstützt die Schätzwertung von Spaltenspeicher- und Spaltenspeicherarchivkomprimierung. Im Gegensatz zur Seiten- und Zeilenkomprimierung muss beim Anwenden der Columnstore-Komprimierung auf ein Objekt ein neuer Spaltenspeicherindex erstellt werden. Aus diesem Grund bestimmt der Typ des Quellobjekts, das für die Prozedur bereitgestellt wird, bei Verwendung der COLUMNSTORE Optionen COLUMNSTORE_ARCHIVE dieser Prozedur den Typ des Spaltenspeicherindexes, der für die komprimierte Größenschätzung verwendet wird. In der folgenden Tabelle sind die Referenzobjekte dargestellt, die verwendet werden, um die Komprimierungseinsparungen für jeden Quellobjekttyp zu schätzen, wenn der Parameter @data_compression auf einen COLUMNSTORE oder COLUMNSTORE_ARCHIVEmehrere Parameter festgelegt ist.

Quellobjekt Reference-Objekt
**Haufen Gruppierter Columnstore-Index
Gruppierter Index Gruppierter Columnstore-Index
Nicht gruppierter Index Nicht gruppierter Spaltenspeicherindex (einschließlich der Schlüsselspalten und aller eingeschlossenen Spalten des bereitgestellten nicht gruppierten Indexes und der Partitionsspalte der Tabelle, falls vorhanden)
Nicht gruppierter Spaltenspeicherindex Nicht gruppierter Columnstore-Index (einschließlich der gleichen Spalten wie der bereitgestellte nicht gruppierte Spaltenspeicherindex)
Gruppierter Columnstore-Index Gruppierter Columnstore-Index

Hinweis

Bei der Schätzung der Columnstore-Komprimierung aus einem Rowstore-Quellobjekt (gruppierter Index, nicht gruppierter Index oder Heap) tritt ein Fehler auf, wenn spalten im Quellobjekt einen Datentyp aufweisen, sp_estimate_compression_savings der in einem Columnstore-Index nicht unterstützt wird.

Wenn der @data_compression-Parameter auf NONE, ROWoder PAGE das Quellobjekt ein Columnstore-Index ist, wird in der folgenden Tabelle die verwendeten Referenzobjekte umrissen.

Quellobjekt Reference-Objekt
Gruppierter Columnstore-Index Heap
Nicht gruppierter Spaltenspeicherindex Nicht gruppierter Index (einschließlich der Spalten, die im nicht gruppierten Spaltenspeicherindex als Schlüsselspalten enthalten sind, und die Partitionsspalte der Tabelle(sofern vorhanden) als eingeschlossene Spalte)

Hinweis

Achten Sie beim Schätzen der Rowstore-Komprimierung (NONE, ROW oder PAGE) aus einem Columnstore-Quellobjekt darauf, dass der Quellindex nicht mehr als 32 Schlüsselspalten enthält, da dies der Grenzwert ist, der in einem Zeilenspeicherindex (nicht gruppiert) unterstützt wird.

Beispiele

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

A. Schätzen der Einsparungen mit ROW-Komprimierung

Im folgenden Beispiel wird die Größe der Production.WorkOrderRouting Tabelle geschätzt, wenn sie mithilfe ROW der Komprimierung komprimiert wird.

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

B. Schätzen von Einsparungen mit PAGE und XML-Komprimierung

Gilt für: SQL Server 2022 (16.x) und höhere Versionen

Im folgenden Beispiel wird die Größe der Production.ProductModel Tabelle geschätzt, wenn sie mithilfe PAGE der Komprimierung komprimiert wird und der @xml_compression Wert aktiviert ist.

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

Zugehöriger Inhalt