sp_estimate_data_compression_savings (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure 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 der Komprimierung von Zeilen, Seiten, Spaltenspeicher oder Spaltenspeicher-Archiv 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.

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

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".

Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

[ @schema_name = ] 'schema_name'

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

[ @object_name = ] 'object_name'

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

[ @index_id = ] index_id

Die ID des Indexes. index_id ist int und kann einen der folgenden Werte aufweisen: die ID-Nummer eines Indexes, NULL oder 0, wenn object_id ein Heap ist. Geben Sie NULL an, wenn Informationen zu allen Indizes für eine Basistabelle oder Sicht zurückgegeben werden sollen. Wenn Sie NULL angeben, müssen Sie auch NULL für partition_number angeben.

[ @partition_number = ] partition_number

Die Partitionsnummer im Objekt. partition_number ist int und kann eines der folgenden Werte sein: die Partitionsnummer eines Indexes oder Heaps, NULL oder 1 für einen nicht partitionierten Index oder Heap.

Um die Partition anzugeben, können Sie auch die $PARTITION-Funktion angeben. Geben Sie NULL an, wenn Informationen zu allen Partitionen des besitzenden Objekts zurückgegeben werden sollen.

[ @data_compression = ] 'data_compression'

Der Typ der komprimierung, die ausgewertet werden soll. data_compression kann einer der folgenden Werte sein: NONE, ROW, PAGE, COLUMNSTORE oder COLUMNSTORE_ARCHIVE.

Für SQL Server 2022 (16.x) und höher ist NULL auch ein möglicher Wert. data_compression kann nicht NULL sein, wenn xml_compression NULL ist.

[ @xml_compression = ] xml_compression

Gilt für: SQL Server 2022 (16.x) und höher, Azure SQL-Datenbank, Azure SQL Managed Instance

Gibt an, ob Einsparungen für die XML-Komprimierung berechnet werden sollen. xml_compression bit und kann NULL, 0 oder 1 sein. Der -Standardwert ist NULL.

xml_compression kann nicht NULL sein, wenn data_compression NULL ist.

Rückgabecodewerte

0 (Erfolg) 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.

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 und verwendet den vorhandenen Füllfaktor für Indizes für das Quellobjekt.

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

Vor SQL Server 2019 (15.x) gilt dieses Verfahren nicht für Spaltenspeicherindizes und akzeptierte daher die Datenkomprimierungsparameter COLUMNSTORE und COLUMNSTORE_ARCHIVE nicht. Ab SQL Server 2019 (15.x) und 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 wird die Datenkomprimierungsparameter COLUMNSTORE und COLUMNSTORE_ARCHIVE nicht unterstützt, sp_estimate_data_compression_savings wenn speicheroptimierte TempDB-Metadaten aktiviert sind.

SQL Server 2022 (16.x) Release Candidate (RC) 0 schätzt keine Einsparungen für XML-Indizes.

Ü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 Optionen COLUMNSTORE und COLUMNSTORE_ARCHIVE dieser Prozedur den Typ des Spaltenspeicherindexes, der für die Komprimierungsgröße verwendet wird. In der folgenden Tabelle sind die Referenzobjekte dargestellt, die verwendet werden, um komprimierungseinsparungen für jeden Quellobjekttyp zu schätzen, wenn der @data_compression Parameter entweder auf COLUMNSTORE oder COLUMNSTORE_ARCHIVE festgelegt ist.

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

Beispiele

.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.

USE AdventureWorks2022;
GO
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)

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.

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

Zugehöriger Inhalt

• CREATE TABLE (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• sys.partitions (Transact-SQL)
• Datenbank-Engine gespeicherten Prozeduren (Transact-SQL)
• Implementierung von Unicode-Komprimierung