Delen via

sp_estimate_data_compression_savings (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Geeft de huidige grootte van het gevraagde object terug en schat de objectgrootte voor de gevraagde compressietoestand. Compressie kan worden geëvalueerd voor hele tabellen of delen van tabellen. Dit omvat heaps, geclusterde indexen, niet-geclusterde indexen, kolomopslagindexen, geïndexeerde weergaven en tabel- en indexpartities. De objecten kunnen worden gecomprimeerd door gebruik te maken van rij-, pagina-, kolomopslag- of kolomopslagarchiefcompressie. Als de tabel, index of partitie al gecomprimeerd is, kun je deze procedure gebruiken om de grootte van de tabel, index of partitie te schatten als deze opnieuw wordt gecomprimeerd of zonder compressie wordt opgeslagen.

De sys.sp_estimate_data_compression_savings systeemopgeslagen procedure is beschikbaar in Azure SQL Database en Azure SQL Managed Instance.

Vanaf SQL Server 2022 (16.x) kun je off-row XML-data comprimeren in kolommen met het xml-datatype , waardoor de opslag- en geheugenvereisten worden verminderd. Zie CREATE TABLE en CREATE INDEXvoor meer informatie. sp_estimate_data_compression_savings ondersteunt XML-compressieschattingen.

Opmerking

Compressie en sp_estimate_data_compression_savings zijn niet beschikbaar in elke editie van SQL Server. Zie -edities en ondersteunde functies van SQL Server 2022voor een lijst met functies die worden ondersteund door de edities van SQL Server.

Om de grootte van het object te schatten als het de gevraagde compressie-instelling zou gebruiken, samplet deze opgeslagen procedure het bronobject en laadt deze data in een equivalente tabel en index die in tempdbwordt aangemaakt. De tabel of index die in tempdb wordt aangemaakt, wordt vervolgens gecomprimeerd naar de gevraagde instelling en wordt de geschatte compressiebesparing berekend.

Om de compressiestatus van een tabel, index of partitie te wijzigen, gebruik je de ALTER TABLE- of ALTER INDEX-instructies . Voor algemene informatie over compressie, zie Datacompressie.

Opmerking

Als de bestaande data gefragmenteerd is, kun je de omvang ervan mogelijk verkleinen zonder compressie te gebruiken door de index opnieuw op te bouwen. Voor indexen wordt de fill factor toegepast tijdens een indexrebuild. Dit kan de omvang van de index vergroten.

Transact-SQL syntaxis-conventies

Syntaxis

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'schema_name'

De naam van het databaseschema dat de tabel of geïndexeerde weergave bevat. @schema_name is een systeemnaam, zonder standaard. Als @schema_name is NULL, wordt het standaardschema van de huidige gebruiker gebruikt.

[ @object_name = ] N'object_name'

De naam van de tabel of geïndexeerde weergave waarop de index staat. @object_name is sysname, zonder standaard.

[ @index_id = ] index_id

De id van de index. @index_id is int, en kan een van de volgende waarden zijn:

  • het ID-nummer van een index
  • NULL
  • 0 als object_id een hoop is

Om informatie terug te geven voor alle indexen van een basistabel of weergave, specificeer NULL. Als je specificeert NULL, moet je ook specificeren NULL voor @partition_number.

[ @partition_number = ] partition_number

Het partitienummer in het object. @partition_number is int, en kan een van de volgende waarden zijn:

  • het partitienummer van een index of heap
  • NULL
  • 1 voor een niet-gepartitioneerde index of heap

Om de partitie te specificeren, kun je ook de functie $PARTITION specificeren. Om informatie terug te geven voor alle partities van het eigenaar object, specificeer NULL.

[ @data_compression = ] N'data_compression'

Specificeert het type compressie dat geëvalueerd moet worden. @data_compression is nvarchar(60) en kan een van de volgende waarden zijn:

  • NONE
  • ROW
  • PAGE
  • COLUMNSTORE
  • COLUMNSTORE_ARCHIVE

Voor SQL Server 2022 (16.x) en latere versies NULL is ook een mogelijke waarde. @data_compression kan niet als NULL@xml_compression dat is NULL.

[ @xml_compression = ] xml_compression

Van toepassing op: SQL Server 2022 (16.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

Specificeert of besparingen voor XML-compressie moeten worden berekend. @xml_compression is bit, en kan een van de volgende waarden zijn:

  • NULL (standaard)
  • 0
  • 1

@xml_compression kan niet als NULL@data_compression dat is NULL.

Codewaarden retourneren

0 (geslaagd) of 1 (mislukt).

Resultaatset

De volgende resultaatset wordt teruggegeven om de huidige en geschatte grootte te geven voor de tabel, index of partitie.

Kolomnaam Gegevenstype Description
object_name sysname Naam van de tabel of de geïndexeerde weergave.
schema_name sysname Schema van de tabel of geïndexeerde weergave.
index_id int Index-ID van een index:

0 = Heap
1 = Geclusterde index
>1 = Niet-geclusterde index
partition_number int Partitienummer. Geeft terug 1 voor een niet-gepartitioneerde tabel of index.
size_with_current_compression_setting (KB) bigint De grootte van de gevraagde tabel, index of partitie zoals die momenteel bestaat.
size_with_requested_compression_setting (KB) bigint Geschatte grootte van de tabel, index of partitie die de gevraagde compressie-instelling gebruikt; en, indien van toepassing, de bestaande vulfactor, en ervan uitgaande dat er geen fragmentatie is.
sample_size_with_current_compression_setting (KB) bigint Grootte van het monster met de huidige compressie-instelling. Deze grootte omvat elke fragmentatie.
sample_size_with_requested_compression_setting (KB) bigint De grootte van de steekproef die wordt gemaakt met de gevraagde compressie-instelling; en, indien van toepassing, de bestaande opvulfactor en geen fragmentatie.

Opmerkingen

Gebruik sp_estimate_data_compression_savings deze om de besparingen te schatten die kunnen ontstaan wanneer je een tabel of partitie inschakelt voor rij-, pagina-, kolomopslag-, kolomopslagarchief- of XML-compressie. Als bijvoorbeeld de gemiddelde grootte van de rij met 40 procent kan worden verkleind, kun je mogelijk de grootte van het object met 40 procent verkleinen. Je krijgt misschien geen ruimtebesparing omdat dit afhangt van de vulfactor en de grootte van de rij. Als je bijvoorbeeld een rij hebt van 8.000 bytes en je verkleint de grootte met 40 procent, kun je nog steeds maar één rij op een datapagina passen. Er zijn geen spaargelden.

Als de resultaten van het draaien sp_estimate_data_compression_savings op een niet-gecomprimeerde tabel of index aangeven dat de grootte zal toenemen, betekent dit dat veel rijen bijna de volledige precisie van de datatypes gebruiken, en de kleine overhead die nodig is voor het gecomprimeerde formaat is meer dan de besparing door compressie. In dit zeldzame geval schakel compressie niet in.

Als een tabel al is ingeschakeld voor compressie, kun je gebruiken sp_estimate_data_compression_savings om de gemiddelde grootte van de rij te schatten als de tabel niet gecomprimeerd is.

Tijdens deze operatie wordt een intent shared (IS) lock op de tafel verkregen. Als er geen IS-lock kan worden verkregen, wordt de procedure geblokkeerd. De tabel wordt gescand onder het standaard read committed isolation-niveau.

Als de gevraagde compressie-instelling hetzelfde is als de huidige compressie-instelling, geeft de opgeslagen procedure de geschatte grootte terug zonder datafragmentatie, waarbij de bestaande fillfactor voor indexen op het bronobject wordt gebruikt.

Als de index- of partitie-ID niet bestaat, worden er geen resultaten teruggegeven.

Permissions

Vereist SELECT toestemming voor de tabel, VIEW DATABASE STATE en VIEW DEFINITION voor de database die de tabel bevat en op tempdb.

Beperkingen

In SQL Server 2017 (14.x) en eerdere versies gold deze procedure niet voor kolomopslagindexen, en accepteerde daarom de datacompressieparameters COLUMNSTORE en COLUMNSTORE_ARCHIVE. In SQL Server 2019 (15.x) en latere versies, en in Azure SQL Database en Azure SQL Managed Instance, kunnen kolomopslagindexen zowel als bronobject voor schatting als als gevraagde compressietype worden gebruikt.

Wanneer Memory-Optimized TempDB Metadata is ingeschakeld, wordt het aanmaken van kolomopslagindexen op tijdelijke tabellen niet ondersteund. Vanwege deze beperking sp_estimate_data_compression_savings wordt het niet ondersteund met de COLUMNSTORE datacompressieparameters COLUMNSTORE_ARCHIVE wanneer Memory-Optimized TempDB Metadata is ingeschakeld.

Overwegingen voor columnstore-indexen

Vanaf SQL Server 2019 (15.x), en in Azure SQL Database en Azure SQL Managed Instance, sp_estimate_compression_savings ondersteunt het het schatten van zowel columnstore als columnstore archiefcompressie. In tegenstelling tot pagina- en rijcompressie vereist het toepassen van columnstore-compressie op een object het aanmaken van een nieuwe columnstore-index. Om deze reden bepaalt bij gebruik van de COLUMNSTORE en-opties COLUMNSTORE_ARCHIVE van deze procedure het type bronobject dat aan de procedure wordt geleverd het type kolomopslagindex dat wordt gebruikt voor de schatting van de gecomprimeerde grootte. De volgende tabel illustreert de referentieobjecten die worden gebruikt om compressiebesparingen voor elk bronobjecttype te schatten wanneer de parameter @data_compression is ingesteld op ofwel COLUMNSTORE of COLUMNSTORE_ARCHIVE.

Bronobject Referentieobject
**Hoop Geclusterde columnstore-index
Geclusterde index Geclusterde columnstore-index
Niet-geclusterde index Niet-geclusterde columnstore-index (inclusief de sleutelkolommen en eventuele opgenomen kolommen van de opgegeven niet-geclusterde index, en de partitiekolom van de tabel, indien aanwezig)
Niet-geclusterde columnstore-index Niet-geclusterde columnstore-index (inclusief dezelfde kolommen als de meegeleverde niet-geclusterde columnstore-index)
Geclusterde columnstore-index Geclusterde columnstore-index

Opmerking

Bij het schatten van columnstore-compressie vanuit een rowstore-bronobject (geclusterde index, niet-geclusterde index of heap), als er kolommen in het bronobject zijn met een datatype dat niet wordt ondersteund in een columnstore-index, sp_estimate_compression_savings zullen deze falen met een fout.

Evenzo, wanneer de parameter @data_compression is ingesteld op NONE, ROW, of PAGE het bronobject een columnstore-index is, geeft de volgende tabel een overzicht van de gebruikte referentieobjecten.

Bronobject Referentieobject
Geclusterde columnstore-index Stapel
Niet-geclusterde columnstore-index Niet-geclusterde index (inclusief de kolommen die in de niet-geclusterde columnstore-index zijn opgenomen als sleutelkolommen, en de partitiekolom van de tabel, indien aanwezig, als een opgenomen kolom)

Opmerking

Bij het schatten van rowstore-compressie (GEEN, ROW of PAGE) vanuit een columnstore-bronobject, zorg er dan voor dat de bronindex niet meer dan 32 sleutelkolommen bevat, aangezien dit de limiet is die wordt ondersteund in een rowstore (niet-geclusterde) index.

Voorbeelden

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2025 of AdventureWorksDW2025 voorbeelddatabase die u kunt downloaden van de startpagina van Microsoft SQL Server Samples en Community Projects .

Eén. Schatting van besparingen met ROW-compressie

Het volgende voorbeeld schat de grootte van de Production.WorkOrderRouting tabel als deze wordt samengedrukt door ROW compressie.

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

B. Schatting van besparingen met PAGE- en XML-compressie

Van toepassing op: SQL Server 2022 (16.x) en latere versies

Het volgende voorbeeld schat de grootte van de Production.ProductModel tabel als deze wordt gecomprimeerd door PAGE compressie, en de @xml_compression-waarde is ingeschakeld.

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

Verwante inhoud

  • Last updated on