Delen via

Gepartitioneerde tabellen en indexen

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

De gegevens van gepartitioneerde tabellen en indexen zijn onderverdeeld in eenheden die kunnen worden verdeeld over meer dan één bestandsgroep in een database of zijn opgeslagen in één bestandsgroep. Wanneer er meerdere bestanden in een bestandsgroep bestaan, worden gegevens verspreid over bestanden met behulp van het proportionele vulalgoritme. De gegevens worden horizontaal gepartitioneerd, zodat groepen rijen worden toegewezen aan afzonderlijke partities. Alle partities van één index of tabel moeten zich in dezelfde database bevinden. De tabel of index wordt behandeld als één logische entiteit wanneer query's of updates worden uitgevoerd op de gegevens.

Voordelen van partitioneren

Het partitioneren van grote tabellen of indexen kan de volgende beheerbaarheid en prestatievoordelen hebben.

  • U kunt snel en efficiënt subsets van gegevens overdragen of openen, terwijl u de integriteit van een gegevensverzameling behoudt. Een bewerking zoals het laden van gegevens van een OLTP naar een OLAP-systeem duurt bijvoorbeeld slechts enkele seconden, in plaats van de minuten en uren die de bewerking duurt wanneer de gegevens niet worden gepartitioneerd.

  • U kunt sneller onderhouds- of gegevensretentiebewerkingen uitvoeren op een of meer partities. De bewerkingen zijn efficiënter omdat ze alleen gericht zijn op deze gegevenssubsets, in plaats van op de hele tabel. U kunt er bijvoorbeeld voor kiezen om gegevens in een of meer partities te comprimeren, een of meer partities van een index te herbouwen of gegevens in één partitie af te kapen. U kunt ook afzonderlijke partities uit één tabel en in een archieftabel overschakelen.

  • U kunt de queryprestaties verbeteren op basis van de typen query's die u regelmatig uitvoert. De queryoptimizer kan bijvoorbeeld equijoin-query's tussen twee of meer gepartitioneerde tabellen sneller verwerken wanneer de partitioneringskolommen dezelfde zijn als de kolommen waarop de tabellen zijn gekoppeld. Zie de sectie over query's voor meer informatie.

U kunt de prestaties verbeteren door escalatie van vergrendelingen in te schakelen op partitieniveau in plaats van een hele tabel. Dit kan de vergrendelingsconflicten op de tabel verminderen. Als u de contentie bij vergrendelingen wilt verminderen door toe te staan dat vergrendelingen escaleren naar de partitie, stelt u de LOCK_ESCALATION optie van de ALTER TABLE instructie in op AUTO.

Onderdelen en concepten

De volgende termen zijn van toepassing op tabel- en indexpartitionering.

Partition function

Een partitiefunctie is een databaseobject dat definieert hoe de rijen van een tabel of index worden toegewezen aan een set partities op basis van de waarden van een bepaalde kolom, een partitioneringskolom genoemd. Elke waarde in de partitioneringskolom is een invoer voor de partitioneringsfunctie, die een partitiewaarde retourneert.

De partitiefunctie definieert het aantal partities en de partitiegrenzen die de tabel heeft. Als u bijvoorbeeld een tabel met verkoopordergegevens hebt, kunt u de tabel partitioneren in 12 (maandelijkse) partities op basis van een datum/tijd-kolom , zoals een verkoopdatum.

Een bereiktype (LINKS of RECHTS) geeft aan hoe de grenswaarden van de partitiefunctie in de resulterende partities worden geplaatst:

  • Een LEFT-bereik geeft aan dat de grenswaarde deel uitmaakt van de linkerkant van het grenswaarde-interval wanneer intervalwaarden door de database-engine in oplopende volgorde van links naar rechts gesorteerd worden. Met andere woorden, de hoogste begrenzingswaarde wordt in een partitie opgenomen.
  • Een rechter bereik geeft aan dat de grenswaarde toebehoort aan de rechterkant van het grenswaardeinterval, wanneer de intervalwaarden door de database-engine in oplopende volgorde van links naar rechts geregeld zijn. Met andere woorden, de laagste begrenzingswaarde wordt opgenomen in elke partitie.

Als LINKS of RECHTS niet is opgegeven, is het linkerbereik de standaardwaarde.

Met de volgende partitiefunctie wordt bijvoorbeeld een tabel of index gepartitioneerd in 12 partities, één voor elke maand van de waarden van een jaar in een datetime-kolom. Er wordt een RECHTS-bereik gebruikt, waarmee wordt aangegeven dat grenswaarden in elke partitie fungeren als lagere begrenzingswaarden. RIGHT-bereiken zijn vaak eenvoudiger om mee te werken bij het partitioneren van een tabel gebaseerd op een kolom met de gegevenstypen datetime of datetime2, omdat rijen met een waarde van 0:00 uur worden opgeslagen in dezelfde partitie als rijen met latere waarden op dezelfde dag. En als u het gegevenstype datum gebruikt en partities van een maand of meer gebruikt, houdt een RIGHT-bereik de eerste dag van de maand in dezelfde partitie als latere dagen in die maand. Dit helpt bij het nauwkeurig elimineren van partities bij het uitvoeren van query's op een hele dag aan gegevens.

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',
               '2022-05-01', '2022-06-01', '2022-07-01', '2022-08-01',
               '2022-09-01', '2022-10-01', '2022-11-01', '2022-12-01');

In de volgende tabel ziet u hoe een tabel of index die gebruikmaakt van deze partitiefunctie voor het partitioneren van kolom datecol wordt gepartitioneerd. 1 februari is het eerste grenspunt dat in de functie is gedefinieerd, dus fungeert het als de ondergrens van partitie 2.

Partition 1 2 ... 11 12
Values datecol<2022-02-01 12:00AM datecol>= 2022-02-01 12:00AM AND datecol<2022-03-01 12:00AM datecol>= 2022-11-01 12:00AM EN kolom1<2022-12-01 12:00AM datecol>= 2022-12-01 12:00AM

Voor zowel BEREIK LINKS als BEREIK RECHTS heeft de meest linkse partitie de minimumwaarde van het gegevenstype als ondergrens en heeft de meest rechtse partitie de maximumwaarde van het gegevenstype als bovengrens.

Meer voorbeelden van de partitiefuncties LINKS en RECHTS in CREATE PARTITION FUNCTION.

Partition scheme

Een partitieschema is een databaseobject waarmee de partities van een partitiefunctie worden toegewezen aan één bestandsgroep of aan meerdere bestandsgroepen.

Zoek een voorbeeldsyntaxis voor het maken van partitieschema's in CREATE PARTITION SCHEME.

Filegroups

De primaire reden voor het plaatsen van uw partities op meerdere bestandsgroepen is ervoor te zorgen dat u onafhankelijk back-up- en herstelbewerkingen op partities kunt uitvoeren. Dit komt doordat u back-ups kunt uitvoeren op afzonderlijke bestandsgroepen. Wanneer u gelaagde opslag gebruikt, kunt u met behulp van meerdere bestandsgroepen specifieke partities toewijzen aan specifieke opslaglagen, bijvoorbeeld om oudere en minder vaak gebruikte partities op tragere en goedkopere opslag te plaatsen. Alle andere partitioneringsvoordelen zijn van toepassing, ongeacht het aantal gebruikte bestandsgroepen of de plaatsing van partities voor specifieke bestandsgroepen.

Het beheren van bestanden en bestandsgroepen voor gepartitioneerde tabellen kan in de loop van de tijd aanzienlijke complexiteit aan beheertaken toevoegen. Als uw back-up- en herstelprocedures niet profiteren van het gebruik van meerdere bestandsgroepen, wordt één bestandsgroep voor alle partities aanbevolen. Dezelfde regels voor het ontwerpen van bestanden en bestandsgroepen zijn van toepassing op gepartitioneerde objecten als van toepassing op niet-gepartitioneerde objecten.

Zoek voorbeeldcode voor het maken van bestandsgroepen voor SQL Server en Azure SQL Managed Instance in ALTER DATABASE (Transact-SQL) Bestands- en bestandsgroepopties.

Partitioning column

De kolom van een tabel of index die een partitiefunctie gebruikt om de tabel of index te partitioneren. De volgende overwegingen zijn van toepassing bij het selecteren van een partitioneringskolom:

  • Berekende kolommen die deelnemen aan een partitiefunctie moeten expliciet als PERSISTENT worden gemaakt.
    • Aangezien slechts één kolom kan worden gebruikt als de partitiekolom, kan de samenvoeging van meerdere kolommen met een berekende kolom nuttig zijn.
  • Kolommen van alle gegevenstypen die geldig zijn voor gebruik als indexsleutelkolommen kunnen worden gebruikt als een partitioneringskolom, met uitzondering van tijdstempel.
  • Kolommen van lob-gegevenstypen (large object), zoals ntext, tekst, afbeelding, xml, varchar(max), nvarchar(max), en varbinary(max), kunnen niet worden opgegeven.
  • Door de gebruiker gedefinieerde gebruikers- en aliasgegevenstypekolommen van Microsoft .NET Framework Common Language Runtime (CLR) kunnen niet worden opgegeven.

Als u een object wilt partitioneren, geeft u het partitieschema en de partitioneringskolom op in de instructies CREATE TABLE, ALTER TABLE en CREATE INDEX .

Bij het maken van een niet-geclusterde index, als partition_scheme_name of bestandsgroep niet is opgegeven en de tabel is gepartitioneerd, wordt de index in hetzelfde partitieschema geplaatst, met behulp van dezelfde partitioneringskolom, als de onderliggende tabel. Als u wilt wijzigen hoe een bestaande index wordt gepartitioneerd, gebruikt u CREATE INDEX met de component DROP_EXISTING. Hiermee kunt u een niet-gepartitioneerde index partitioneren, een niet-gepartitioneerde index maken of het partitieschema van de index wijzigen.

Aligned index

Een index die is gebouwd op hetzelfde partitieschema als de bijbehorende tabel. Wanneer een tabel en de bijbehorende indexen zijn uitgelijnd, kan de database-engine snel en efficiënt partities in of uit de tabel schakelen terwijl de partitiestructuur van zowel de tabel als de bijbehorende indexen behouden blijft. Een index hoeft niet deel te nemen aan dezelfde benoemde partitiefunctie om te worden uitgelijnd met de basistabel. De partitiefunctie van de index en de basistabel moeten echter in wezen hetzelfde zijn, in dat:

  • De argumenten van de partitiefuncties hebben hetzelfde gegevenstype.
  • Ze definiëren hetzelfde aantal partities.
  • Ze definiëren dezelfde grenswaarden voor partities.

Geclusterde indexen partitioneren

Bij het partitioneren van een geclusterde index moet de clusteringsleutel de partitioneringskolom bevatten. Bij het partitioneren van een niet-unieke geclusterde index en de partitioneringskolom niet expliciet is opgegeven in de clusteringsleutel, voegt de database-engine de partitioneringskolom standaard toe aan de lijst met geclusterde indexsleutels. Als de geclusterde index uniek is, moet u expliciet opgeven dat de geclusterde indexsleutel de partitiekolom bevat. Zie Ontwerprichtlijnen voor geclusterde indexen voor meer informatie over geclusterde indexen en indexarchitectuur.

Niet-geclusterde indexen partitioneren

Bij het partitioneren van een unieke niet-geclusterde index moet de indexsleutel de partitioneringskolom bevatten. Wanneer u een niet-geclusterde index partitioneert, voegt de database-engine de partitiekolom standaard toe als een niet-sleutelkolom (opgenomen) van de index om ervoor te zorgen dat de index is uitgelijnd met de basistabel. De database-engine voegt de partitioneringskolom niet aan de index toe als deze al in de index aanwezig is. Zie Ontwerprichtlijnen voor niet-geclusterde indexen en indexen voor meer informatie over niet-geclusterde indexarchitectuur.

Nonaligned index

Een niet-uitgelijnde index wordt anders gepartitioneerd dan de bijbehorende tabel. Dat wil gezegd, de index heeft een ander partitieschema dat deze op een afzonderlijke bestandsgroep of set bestandsgroepen van de basistabel plaatst. Het ontwerpen van een niet-uitgelijnde gepartitioneerde index kan handig zijn in de volgende gevallen:

  • De basistabel is niet gepartitioneerd.
  • De indexsleutel is uniek en bevat niet de partitiekolom van de tabel.
  • U wilt dat de basistabel deelneemt aan gecolloceerde joins met meer tabellen waarbij verschillende joinkolommen worden gebruikt.

Partition elimination

Het proces waarmee de queryoptimalisatie alleen toegang heeft tot de relevante partities om te voldoen aan de filtercriteria van de query.

Meer informatie over partitieverwijdering en gerelateerde concepten in verbeteringen voor queryverwerking in gepartitioneerde tabellen en indexen.

Limitations

  • Vóór SQL Server 2016 (13.x) SP1 waren gepartitioneerde tabellen en indexen niet beschikbaar in elke editie van SQL Server. Zie Edities en ondersteunde functies van SQL Server 2022 voor een lijst met functies die worden ondersteund door de edities van SQL Server.

  • Gepartitioneerde tabellen en indexen zijn beschikbaar in alle servicelagen van Azure SQL Database, SQL Database in Fabric en Azure SQL Managed Instance.

    • In Azure SQL Database en SQL Database in Fabric moeten alle partities op de PRIMARY bestandsgroep worden geplaatst, omdat alleen de PRIMARY bestandsgroep is opgegeven.

  • Tabelpartitionering is beschikbaar in toegewezen SQL-pools in Azure Synapse Analytics, met enkele syntaxisverschillen. Meer informatie vindt u in Partitioneringstabellen in toegewezen SQL-pool.

  • Het bereik van een partitiefunctie en -schema is beperkt tot de database waarin ze zijn gemaakt. In de database bevinden partitiefuncties zich in een afzonderlijke naamruimte van andere functies.

  • Als rijen in een gepartitioneerde tabel NULL's in de partitioneringskolom bevatten, worden deze rijen op de meest linkse partitie geplaatst. Als NULL echter is opgegeven als de eerste grenswaarde en BEREIK RECHTS is opgegeven in de definitie van de partitiefunctie, blijft de meest linkse partitie leeg en worden NULL's in de tweede partitie geplaatst.

  • De database-engine ondersteunt standaard maximaal 15.000 partities. In versies ouder dan SQL Server 2012 (11.x) was het aantal partities standaard beperkt tot 1000.

Performance guidelines

De database-engine ondersteunt maximaal 15.000 partities per tabel of index. Het gebruik van meer dan 1000 partities heeft echter gevolgen voor geheugen, gepartitioneerde indexbewerkingen, DBCC-opdrachten en query's. In deze sectie worden de gevolgen voor de prestaties van het gebruik van meer dan 1000 partities beschreven en worden zo nodig tijdelijke oplossingen geboden.

Met maximaal 15.000 partities toegestaan per gepartitioneerde tabel of index kunt u gegevens opslaan voor lange duur in één tabel. U moet gegevens echter alleen bewaren zolang deze nodig zijn en een balans behouden tussen prestaties en het aantal partities.

Geheugengebruik en richtlijnen

U wordt aangeraden ten minste 16 GB RAM-geheugen te gebruiken als een groot aantal partities in gebruik is. Als het systeem onvoldoende geheugen heeft, kunnen DML-instructies (Data Manipulation Language), DDL-instructies (Data Definition Language) en andere bewerkingen mislukken vanwege onvoldoende geheugen. Systemen met 16 GB RAM-geheugen waarop veel geheugenintensieve processen worden uitgevoerd, hebben mogelijk onvoldoende geheugen voor bewerkingen die worden uitgevoerd op een groot aantal partities. Daarom, hoe meer geheugen u meer dan 16 GB hebt, hoe minder waarschijnlijk u prestatie- en geheugenproblemen ondervindt.

Geheugenbeperkingen kunnen van invloed zijn op de prestaties of de mogelijkheid van de database-engine om een gepartitioneerde index te bouwen. Dit is met name het geval wanneer de index niet is uitgelijnd met de basistabel of niet is uitgelijnd met de geclusterde index, als de tabel al een geclusterde index heeft.

In SQL Server en Azure SQL Managed Instance kunt u de index create memory (KB) optie serverconfiguratie verhogen. Zie Serverconfiguratie: index geheugen maken voor meer informatie.

Voor Azure SQL Database kunt u overwegen om het serviceniveaudoelstelling voor de database in Azure Portal tijdelijk of permanent te verhogen om meer geheugen toe te wijzen.

Gepartitioneerde indexbewerkingen

Het maken en herbouwen van niet-uitgelijnde indexen in een tabel met meer dan 1000 partities is mogelijk, maar wordt niet ondersteund. Dit kan leiden tot verminderde prestaties of overmatig geheugenverbruik tijdens deze bewerkingen.

Het maken en herbouwen van uitgelijnde indexen kan langer duren naarmate het aantal partities toeneemt. Het is raadzaam om niet meerdere opdrachten voor het maken en herbouwen van indexen tegelijk uit te voeren, omdat u mogelijk prestatie- en geheugenproblemen ondervindt.

Wanneer de database-engine sorteert om gepartitioneerde indexen te bouwen, wordt eerst één sorteertabel voor elke partitie gemaakt. Vervolgens worden de sorteertabellen gebouwd in de respectieve bestandsgroep van elke partitie of in tempdb als de SORT_IN_TEMPDB indexoptie is opgegeven. Elke sorteertabel vereist een minimale hoeveelheid geheugen om te bouwen. Wanneer u een gepartitioneerde index bouwt die is uitgelijnd met de basistabel, worden sorteertabellen één voor één gemaakt met minder geheugen. Wanneer u echter een niet-uitgelijnde gepartitioneerde index bouwt, worden de sorteertabellen tegelijkertijd gebouwd. Als gevolg hiervan moet er voldoende geheugen zijn om deze gelijktijdige sorteringen te verwerken. Hoe groter het aantal partities, hoe meer geheugen nodig is. De minimale grootte voor elke sorteertabel, voor elke partitie, is 40 pagina's, met 8 kilobytes per pagina. Een niet-uitgelijnde gepartitioneerde index met 100 partities vereist bijvoorbeeld voldoende geheugen om 4.000 (40 * 100) pagina's tegelijk te sorteren. Als dit geheugen beschikbaar is, zal de buildbewerking slagen, maar de prestaties kunnen eronder lijden. Als dit geheugen niet beschikbaar is, mislukt de buildbewerking. Een uitgelijnde gepartitioneerde index met 100 partities vereist ook alleen voldoende geheugen om 40 pagina's te sorteren, omdat de sorteringen niet tegelijkertijd worden uitgevoerd.

Voor zowel uitgelijnde als niet-uitgelijnde indexen kan de geheugenvereiste groter zijn als de database-engine queryparallelisme gebruikt voor de buildbewerking op een computer met meerdere processors. Dit komt doordat hoe groter de mate van parallelle uitvoering (DOP), hoe groter de geheugenvereiste. Als de database-engine bijvoorbeeld DOP instelt op 4, vereist een niet-uitgelijnde gepartitioneerde index met 100 partities voldoende geheugen voor vier processors om tegelijkertijd 4000 pagina's of 16.000 pagina's te sorteren. Als de gepartitioneerde index is uitgelijnd, wordt de geheugenvereiste beperkt tot vier processors die 40 pagina's sorteren of 160 (4 * 40) pagina's. U kunt de optie MAXDOP-index gebruiken om de mate van parallelle uitvoering handmatig te verminderen.

DBCC commands

Met een groter aantal partities kunnen DBCC-opdrachten zoals DBCC CHECKDB en DBCC CHECKTABLE langer duren naarmate het aantal partities toeneemt.

Queries

Na het partitioneren van een tabel of index kunnen query's die partitieverwijdering gebruiken vergelijkbare of verbeterde prestaties hebben met een groter aantal partities. Het uitvoeren van query's die geen partitieverwijdering gebruiken, kan langer duren naarmate het aantal partities toeneemt.

Stel dat een tabel 100 miljoen rijen en kolommen Abevat, Ben C.

  • In scenario 1 is de tabel onderverdeeld in 1000 partities op kolom A.
  • In scenario 2 is de tabel onderverdeeld in 10.000 partities op kolom A.

Een query op de tabel met een WHERE clausule voor filtering op kolom A voert partitie-eliminatie uit en scant één partitie. Dezelfde query kan in scenario 2 sneller worden uitgevoerd omdat er minder rijen zijn om in een partitie te scannen. Een query met een WHERE componentfilter op kolom B scant alle partities. De query kan sneller worden uitgevoerd in scenario 1 dan in scenario 2, omdat er minder partities zijn om te scannen.

Query's die gebruikmaken van operators zoals TOP of MAX/MIN op andere kolommen dan de partitioneringskolom, kunnen verminderde prestaties ervaren met partitionering, omdat alle partities moeten worden geëvalueerd.

Op dezelfde manier duurt een query die een zoekopdracht met één rij uitvoert of een kleine bereikscan langer voor een gepartitioneerde tabel dan voor een niet-gepartitioneerde tabel als het querypredicaat niet de partitioneringskolom bevat, omdat er zoveel zoekopdrachten of scans moeten worden uitgevoerd als er partities zijn. Daarom verbetert partitioneren zelden de prestaties in OLTP-systemen waarbij dergelijke query's vaak voorkomen.

Als u vaak query's uitvoert die betrekking hebben op een equijoin tussen twee of meer gepartitioneerde tabellen, moeten de bijbehorende partitioneringskolommen hetzelfde zijn als de kolommen waarop de tabellen zijn gekoppeld. Daarnaast moeten de tabellen, of hun indexen, worden gecolloceerd. Dit betekent dat ze dezelfde benoemde partitiefunctie gebruiken of dat ze verschillende partitiefuncties gebruiken die in wezen hetzelfde zijn, in dat ze:

  • Hetzelfde aantal parameters hebben dat wordt gebruikt voor partitionering en de bijbehorende parameters zijn dezelfde gegevenstypen.
  • Definieer hetzelfde aantal partities.
  • Definieer dezelfde grenswaarden voor partities.

Op deze manier kan de query-optimizer de join sneller verwerken, omdat de partities zelf kunnen worden gekoppeld. Als een query twee tabellen samenvoegt die niet gecolloceerd zijn of niet gepartitioneerd zijn op het joinveld, kan de aanwezigheid van partities de verwerking van query's vertragen in plaats van versnellen.

Het kan handig zijn om in sommige query's te gebruiken $PARTITION . Meer informatie vindt u in $PARTITION.

Zie Verbeteringen op het gebied van queryverwerking voor gepartitioneerde tabellen en indexen voor meer informatie over het verwerken van partities in queryverwerking, inclusief strategie voor parallelle uitvoering van query's voor gepartitioneerde tabellen en indexen.

Gedragswijzigingen in de berekening van statistieken tijdens gepartitioneerde indexbewerkingen

In Azure SQL Database, SQL Database in Fabric, Azure SQL Managed Instance en SQL Server 2012 (11.x) en latere versies worden statistieken niet gemaakt door alle rijen in de tabel te scannen wanneer een gepartitioneerde index wordt gemaakt of opnieuw wordt opgebouwd. In plaats daarvan gebruikt de queryoptimalisatie het standaardsamplingsalgoritmen om statistieken te genereren.

Nadat u een database hebt bijgewerkt met gepartitioneerde indexen van een versie van SQL Server lager dan 2012 (11.x), ziet u mogelijk een verschil in de histogramgegevens voor deze indexen. Deze wijziging in gedrag kan van invloed zijn op de prestaties van query's. Als u statistieken over gepartitioneerde indexen wilt verkrijgen door alle rijen in de tabel te scannen, gebruikt u CREATE STATISTICS of UPDATE STATISTICS met de FULLSCAN-component.

Related content