Share via

Gegevenscompressie

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

SQL Server, Azure SQL Database en Azure SQL Managed Instance ondersteunen rij- en paginacompressie voor rowstore-tabellen en indexen, en ondersteunen columnstore- en columnstore-archiveringscompressie voor columnstore-tabellen en -indexen.

Voor rijopslagtabellen en -indexen gebruikt u de functie voor gegevenscompressie om de grootte van de database te verminderen. Naast het besparen van ruimte, kan gegevenscompressie helpen de prestaties van I/O-intensieve werkbelastingen te verbeteren, omdat de gegevens worden opgeslagen in minder pagina's en query's minder pagina's van schijf moeten lezen. Er zijn echter extra CPU-resources vereist op de databaseserver om de gegevens te comprimeren en te decomprimeren, terwijl gegevens worden uitgewisseld met de app. U kunt rij- en paginacompressie configureren voor de volgende databaseobjecten:

  • Een hele tabel die is opgeslagen als een heap.
  • Een hele tabel die is opgeslagen als een geclusterde index.
  • Een hele niet-geclusterde index.
  • Een hele geïndexeerde weergave.
  • Voor gepartitioneerde tabellen en indexen kunt u de compressieoptie voor elke partitie configureren en de verschillende partities van een object hoeven niet dezelfde compressie-instelling te hebben.

Voor columnstore-tabellen en -indexen gebruiken alle columnstore-tabellen en -indexen altijd columnstore-compressie en kan dit niet door de gebruiker worden geconfigureerd. Gebruik columnstore-archiveringscompressie om de gegevensgrootte verder te verminderen voor situaties waarin u extra tijd en CPU-resources kunt veroorloven om de gegevens op te slaan en op te halen. U kunt columnstore-archiveringscompressie configureren voor de volgende databaseobjecten:

  • Een hele columnstore-tabel of een hele geclusterde columnstore-index. Omdat een columnstore-tabel wordt opgeslagen als een geclusterde columnstore-index, hebben beide benaderingen dezelfde resultaten.
  • Een hele niet-geclusterde columnstore-index.
  • Voor gepartitioneerde columnstore-tabellen en columnstore-indexen kunt u de archiveringscompressieoptie voor elke partitie configureren en hoeven de verschillende partities niet dezelfde instelling voor archiveringscompressie te hebben.

Opmerking

Gegevens kunnen ook worden gecomprimeerd met behulp van de GZIP-algoritmeindeling. Dit is een extra stap en is het meest geschikt voor het comprimeren van delen van de gegevens bij het archiveren van oude gegevens voor langetermijnopslag. Gegevens die zijn gecomprimeerd met behulp van de COMPRESS functie, kunnen niet worden geïndexeerd. Zie COMPRESS (Transact-SQL)voor meer informatie.

Overwegingen voor rij- en paginacompressie

Wanneer u rij- en paginacompressie gebruikt, moet u rekening houden met de volgende overwegingen:

  • De details van gegevenscompressie kunnen zonder voorafgaande kennisgeving veranderen in servicepacks of volgende releases.

  • Compressie is beschikbaar in Azure SQL Database

  • Compressie is niet beschikbaar in elke editie van SQL Server. Zie de lijst met edities en ondersteunde functies aan het einde van deze sectie voor meer informatie.

  • Compressie is niet beschikbaar voor systeemtabellen.

  • Met compressie kunnen meer rijen op een pagina worden opgeslagen, maar wordt de maximale rijgrootte van een tabel of index niet gewijzigd.

  • Een tabel kan niet worden ingeschakeld voor compressie wanneer de maximale rijgrootte plus de compressieoverhead de maximale rijgrootte van 8.060 bytes overschrijdt. Een tabel met de kolommen c1 CHAR(8000) en c2 CHAR(53) kan bijvoorbeeld niet worden gecomprimeerd vanwege de extra compressieoverhead. Wanneer de vardecimal-indeling opslag wordt gebruikt, wordt de rijgroottecontrole uitgevoerd wanneer de indeling is ingeschakeld. Voor rij- en paginacompressie wordt de controle van rijgrootte uitgevoerd wanneer het object in eerste instantie wordt gecomprimeerd en vervolgens wordt gecontroleerd wanneer elke rij wordt ingevoegd of gewijzigd. Compressie dwingt de volgende twee regels af:

    • Een update naar een type met een vaste lengte moet altijd slagen.
    • Het uitschakelen van gegevenscompressie moet altijd lukken. Zelfs als de gecomprimeerde rij op de pagina past, wat betekent dat deze kleiner is dan 8.060 bytes; SQL Server voorkomt dat updates die niet in de rij passen wanneer deze niet zijn gecomprimeerd.

  • Gegevens buiten de rij worden niet gecomprimeerd bij het inschakelen van gegevenscompressie. Een XML-record die groter is dan 8060 bytes, maakt bijvoorbeeld gebruik van pagina's buiten rij, die niet zijn gecomprimeerd.

  • Verschillende gegevenstypen worden niet beïnvloed door gegevenscompressie. Zie Hoe rijcompressie van invloed is op opslagvoor meer informatie.

  • Wanneer een lijst met partities is opgegeven, kan het compressietype worden ingesteld op ROW, PAGEof NONE op afzonderlijke partities. Als de lijst met partities niet is opgegeven, worden alle partities ingesteld met de eigenschap voor gegevenscompressie die is opgegeven in de instructie. Wanneer een tabel of index wordt gemaakt, wordt gegevenscompressie ingesteld op NONE, tenzij anders is opgegeven. Wanneer een tabel wordt gewijzigd, blijft de bestaande compressie behouden, tenzij anders is opgegeven.

  • Als u een lijst met partities of een partitie opgeeft die buiten het bereik valt, wordt er een fout gegenereerd.

  • Niet-geclusterde indexen nemen de compressie-eigenschap van de tabel niet over. Als u indexen wilt comprimeren, moet u expliciet de compressie-eigenschap van de indexen instellen. Standaard is de compressie-instelling voor indexen ingesteld op NONE wanneer de index wordt gemaakt.

  • Wanneer een geclusterde index wordt gemaakt op een heap, neemt de geclusterde index de compressiestatus van de heap over, tenzij een alternatieve compressiestatus is opgegeven.

  • Wanneer een heap is geconfigureerd voor compressie op paginaniveau, ontvangen pagina's alleen compressie op paginaniveau op de volgende manieren:

    • Gegevens worden bulksgewijs geïmporteerd met bulkoptimalisaties ingeschakeld.
    • Gegevens worden ingevoegd met behulp van INSERT INTO ... WITH (TABLOCK) syntaxis en de tabel heeft geen niet-geclusterde index.
    • Een tabel wordt opnieuw opgebouwd door de ALTER TABLE ... REBUILD instructie uit te voeren met de PAGE compressieoptie.

  • Nieuwe pagina's die als onderdeel van DML-bewerkingen zijn toegewezen, gebruiken geen PAGE compressie totdat de heap opnieuw wordt opgebouwd. Bouw de heap opnieuw op door compressie te verwijderen en opnieuw toe te past, of door een geclusterde index te maken en te verwijderen.

  • Als u de compressie-instelling van een heap wijzigt, moeten alle niet-geclusterde indexen in de tabel opnieuw worden opgebouwd, zodat ze verwijzen naar de nieuwe rijlocaties in de heap.

  • U kunt de compressie van ROW of PAGE online of offline in- of uitschakelen. Het inschakelen van compressie op een heap is één thread voor een online bewerking.

  • De schijfruimtevereisten voor het in- of uitschakelen van rij- of paginacompressie zijn hetzelfde als voor het maken of herbouwen van een index. Voor gepartitioneerde gegevens kunt u de benodigde ruimte verminderen door compressie voor één partitie tegelijk in of uit te schakelen.

  • Als u de compressiestatus van partities in een gepartitioneerde tabel wilt bepalen, voert u een query uit op de data_compression kolom van de sys.partitions catalogusweergave.

  • Wanneer u indexen comprimeert, kunnen pagina's op bladniveau worden gecomprimeerd met zowel rij- als paginacompressie. Pagina's die niet op bladbladniveau zijn, ontvangen geen paginacompressie.

  • Vanwege hun grootte worden gegevenstypen met een grote waarde soms afzonderlijk opgeslagen van de normale rijgegevens op pagina's met speciale doeleinden. Gegevenscompressie is niet beschikbaar voor de gegevens die afzonderlijk worden opgeslagen.

  • Tabellen die de vardecimale opslagindeling in SQL Server 2005 (9.x) hebben geïmplementeerd, behouden die instelling wanneer de upgrade wordt uitgevoerd. U kunt rijcompressie toepassen op een tabel met de vardecimale opslagindeling. Omdat rijcompressie echter een superset is van de vardecimale opslagindeling, is er geen reden om de vardecimale opslagindeling te behouden. Decimale waarden krijgen geen extra compressie wanneer u de vardecimale opslagindeling combineert met rijcompressie. U kunt paginacompressie toepassen op een tabel met de vardecimale opslagindeling; De kolommen met de vardecimale opslagindeling bereiken waarschijnlijk geen extra compressie.

    Opmerking

    Alle ondersteunde versies van SQL Server ondersteunen de vardecimale opslagindeling; Omdat gegevenscompressie echter dezelfde doelstellingen bereikt, wordt de vardecimale opslagindeling afgeschaft. Deze functie wordt verwijderd in een toekomstige versie van SQL Server. Vermijd het gebruik van deze functie in nieuwe ontwikkelwerkzaamheden en plan om toepassingen te wijzigen die momenteel gebruikmaken van deze functie.

Zie voor een lijst met functies die worden ondersteund door de edities van SQL Server in Windows:

Columnstore- en columnstore-archiefcompressie

Columnstore-tabellen en -indexen worden altijd opgeslagen met columnstore-compressie. U kunt de grootte van columnstore-gegevens verder verkleinen door een extra compressie te configureren die archiveringscompressie wordt genoemd. Sql Server voert het Microsoft XPRESS-compressiealgoritme uit op de gegevens om archiveringscompressie uit te voeren. Voeg archiveringscompressie toe of verwijder deze met behulp van de volgende typen gegevenscompressie:

  • Gebruik COLUMNSTORE_ARCHIVE gegevenscompressie om columnstore-gegevens met archiveringscompressie te comprimeren.
  • Gegevenscompressie gebruiken COLUMNSTORE om archiveringscompressie te decomprimeren. De resulterende gegevens blijven gecomprimeerd met columnstore-compressie.

Als u archiveringscompressie wilt toevoegen, gebruikt u ALTER TABLE (Transact-SQL) of ALTER INDEX (Transact-SQL) met de REBUILD optie en DATA COMPRESSION = COLUMNSTORE_ARCHIVE.

Voorbeeld:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4)
);

Als u archiveringscompressie wilt verwijderen en de gegevens wilt herstellen naar columnstore-compressie, gebruikt u ALTER TABLE (Transact-SQL) of ALTER INDEX (Transact-SQL) met de REBUILD optie en DATA COMPRESSION = COLUMNSTORE.

Voorbeeld:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
     DATA_COMPRESSION = COLUMNSTORE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)
);

In dit volgende voorbeeld wordt de gegevenscompressie ingesteld op columnstore op sommige partities en op columnstore-archivering op andere partities.

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
        ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE
        ON PARTITIONS (1, 2, 3)
);

Prestatie

Wanneer u columnstore-indexen comprimeert met archiveringscompressie, zorgt dit ervoor dat de index langzamer presteert dan columnstore-indexen die niet over de archiveringscompressie beschikken. Gebruik archiveringscompressie alleen als u zich kunt veroorloven extra tijd en CPU-resources te gebruiken om de gegevens te comprimeren en op te halen.

Het voordeel van archiveringscompressie is beperkte opslag, wat handig is voor gegevens die niet vaak worden geopend. Als u bijvoorbeeld een partitie voor elke maand met gegevens hebt en de meeste van uw activiteiten voor de meest recente maanden is, kunt u oudere maanden archiveren om de opslagvereisten te verminderen.

Metagegevens

De volgende systeemweergaven bevatten informatie over gegevenscompressie voor geclusterde indexen:

De procedure sp_estimate_data_compression_savings (Transact-SQL) kan ook van toepassing zijn op columnstore-indexen.

Invloed op gepartitioneerde tabellen en indexen

Wanneer u gegevenscompressie met gepartitioneerde tabellen en indexen gebruikt, moet u rekening houden met de volgende overwegingen:

  • Wanneer partities worden gesplitst met behulp van de ALTER PARTITION instructie, nemen beide partities het kenmerk voor gegevenscompressie van de oorspronkelijke partitie over.

  • Wanneer twee partities worden samengevoegd, neemt de resulterende partitie het kenmerk voor gegevenscompressie van de doelpartitie over.

  • Als u een partitie wilt overschakelen, moet de eigenschap voor gegevenscompressie van de partitie overeenkomen met de compressie-eigenschap van de tabel.

  • Er zijn twee syntaxisvariaties die u kunt gebruiken om de compressie van een gepartitioneerde tabel of index te wijzigen:

    • Met de volgende syntaxis wordt alleen de partitie waarnaar wordt verwezen, opnieuw opgebouwd:

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

    • Met de volgende syntaxis wordt de hele tabel opnieuw opgebouwd met behulp van de bestaande compressie-instelling voor partities waarnaar niet wordt verwezen:

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

    Gepartitioneerde indexen volgen hetzelfde principe met behulp van ALTER INDEX.

  • Wanneer een geclusterde index wordt verwijderd, behouden de bijbehorende heap-partities hun instelling voor gegevenscompressie, tenzij het partitioneringsschema wordt gewijzigd. Als het partitioneringsschema wordt gewijzigd, worden alle partities opnieuw opgebouwd naar een niet-gecomprimeerde status. Als u een geclusterde index wilt verwijderen en het partitioneringsschema wilt wijzigen, moet u de volgende stappen uitvoeren:

    1. Verwijder de geclusterde index.
    2. Wijzig de tabel met behulp van de ALTER TABLE ... REBUILD optie waarmee de compressieoptie wordt opgegeven.

    Een geclusterde index OFFLINE verwijderen is een snelle bewerking, omdat alleen de bovenste niveaus van geclusterde indexen worden verwijderd. Wanneer een geclusterde index wordt verwijderd ONLINE, moet SQL Server de heap twee keer opnieuw opbouwen, één keer voor stap 1 en één keer voor stap 2.

Hoe compressie van invloed is op replicatie

Wanneer u gegevenscompressie met replicatie gebruikt, moet u rekening houden met de volgende overwegingen:

  • Wanneer de momentopnameagent het eerste schemascript genereert, gebruikt het nieuwe schema dezelfde compressie-instellingen voor zowel de tabel als de bijbehorende indexen. Compressie kan niet alleen worden ingeschakeld voor de tabel en niet voor de index.

  • Voor transactionele replicatie bepaalt de artikelschemaoptie welke afhankelijke objecten en eigenschappen moeten worden gescript. Zie sp_addarticle voor meer informatie.

    De distributieagent controleert niet op abonnees op down-level wanneer scripts worden toegepast. Als de replicatie van compressie is geselecteerd, mislukt het maken van de tabel voor abonnees op lager niveau. Schakel voor een gemengde topologie de replicatie van compressie niet in.

  • Voor samenvoegreplicatie overschrijft het compatibiliteitsniveau van de publicatie-instelling de schemaopties en bepaalt welke schemaobjecten worden gescript.

    Als voor een gemengde topologie de nieuwe compressieopties niet hoeven te worden ondersteund, moet het compatibiliteitsniveau van de publicatie worden ingesteld op de versie Abonnee op lager niveau. Als dit nodig is, comprimeert u tabellen op de server van de abonnee nadat ze zijn aangemaakt.

In de volgende tabel ziet u de replicatie-instellingen die de compressie tijdens de replicatie regelen.

Gebruikersintentie Partitieschema repliceren voor een tabel of index Compressie-instellingen repliceren Scriptgedrag
Om het partitieschema te repliceren en compressie op de Subscriber op de partitie in te schakelen. Klopt Klopt Beheert zowel het partitieschema als de compressie-instellingen.
Het partitieschema repliceren, maar de gegevens niet comprimeren op de ontvanger. Klopt Onwaar Scripts het partitieschema uit, maar niet de compressie-instellingen voor de partitie.
Het partitieschema niet repliceren en de gegevens niet comprimeren op de abonnee. Onwaar Onwaar Deelt geen partitie- of compressie-instellingen uit.
Om de tabel op de abonnee te comprimeren wanneer alle partities zijn gecomprimeerd in de publicatiedatabase, maar niet om het partitieschema te repliceren. Onwaar Klopt Controleert of alle partities zijn ingeschakeld voor compressie.

Schakelt compressie op tabelniveau uit met scripts.

Effect op andere SQL Server-onderdelen

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Compressie vindt plaats in de database-engine en de gegevens worden gepresenteerd aan de meeste andere onderdelen van SQL Server met een niet-gecomprimeerde status. Dit beperkt de effecten van compressie op de andere onderdelen tot de volgende factoren:

  • Bulkimport- en exportbewerkingen
    • Wanneer gegevens worden geëxporteerd, zelfs in native formaat, worden de gegevens gepresenteerd in de niet-gecomprimeerde rijindeling. Dit kan ertoe leiden dat het geëxporteerde gegevensbestand aanzienlijk groter is dan de brongegevens.
    • Wanneer gegevens worden geïmporteerd en de doeltabel is ingeschakeld voor compressie, worden de gegevens door de database-engine geconverteerd naar gecomprimeerde rijindeling. Dit kan een verhoogd CPU-gebruik veroorzaken in vergelijking met wanneer gegevens in een niet-gecomprimeerde tabel worden geïmporteerd.
    • Wanneer gegevens bulksgewijs worden geïmporteerd in een heap met paginacompressie, probeert de bulkimportbewerking de gegevens te comprimeren met paginacompressie wanneer de gegevens worden ingevoegd.
  • Compressie heeft geen invloed op back-up en herstel.
  • Compressie heeft geen invloed op logboekverzending.
  • Gegevenscompressie is niet compatibel met sparsekolommen. Daarom kunnen tabellen met sparse kolommen niet worden gecomprimeerd en kunnen sparse kolommen niet worden toegevoegd aan een gecomprimeerde tabel.
  • Het inschakelen van compressie kan ertoe leiden dat queryplannen worden gewijzigd omdat de gegevens worden opgeslagen met behulp van een ander aantal pagina's en het aantal rijen per pagina.

Verwante inhoud

  • Last updated on