Architectuurhandleiding voor pagina's en gebieden

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

De pagina is de fundamentele eenheid van gegevensopslag in SQL Server. Een gebied is een verzameling van acht fysiek aaneengesloten pagina's. Gebieden helpen pagina's efficiënt te beheren. In deze handleiding worden de gegevensstructuren beschreven die worden gebruikt voor het beheren van pagina's en gebieden in alle versies van SQL Server. Inzicht in de architectuur van pagina's en gebieden is belangrijk voor het ontwerpen en ontwikkelen van databases die efficiënt presteren.

Pagina's en gebieden

De fundamentele eenheid van gegevensopslag in SQL Server is de pagina. De schijfruimte die is toegewezen aan een gegevensbestand (.mdf of .ndf) in een database, wordt logisch verdeeld in pagina's die aaneengesloten zijn van 0 tot n. Schijf-I/O-bewerkingen worden uitgevoerd op paginaniveau. Sql Server leest of schrijft hele gegevenspagina's.

Gebieden zijn een verzameling van acht fysiek aaneengesloten pagina's en worden gebruikt om de pagina's efficiënt te beheren. Alle pagina's zijn ingedeeld in gebieden.

Pagina's

In een gewoon boek wordt alle inhoud op pagina's geschreven. Net als bij een boek schrijft SQL Server alle gegevensrijen op pagina's en hebben alle gegevenspagina's dezelfde grootte: 8 kB. In een boek bevatten de meeste pagina's de gegevens - de hoofdinhoud van het boek - en sommige pagina's bevatten metagegevens over de inhoud (bijvoorbeeld de inhoudsopgave en index). SQL Server is nogmaals niet anders: de meeste pagina's bevatten werkelijke rijen met gegevens die door gebruikers zijn opgeslagen; deze worden gegevenspagina's en tekst-/afbeeldingspagina's genoemd (voor speciale gevallen). De indexpagina's bevatten indexverwijzingen over waar de gegevens zich bevinden. Ten slotte zijn er systeempagina's die verschillende metagegevens over de organisatie van de gegevens opslaan.

Elke pagina begint met een header van 96 bytes die wordt gebruikt om systeeminformatie over de pagina op te slaan. Deze informatie omvat het paginanummer, het paginatype, de hoeveelheid vrije ruimte op de pagina en de toewijzingseenheid-id van het object dat eigenaar is van de pagina.

In de volgende tabel ziet u de paginatypen die worden gebruikt in de gegevensbestanden van een SQL Server-database.

Gegevensrijen worden serieel op de pagina opgeslagen, direct na de koptekst. Een rijverschiltabel begint aan het einde van de pagina en elke rijverschiltabel bevat één vermelding voor elke rij op de pagina. Elke rijverschilvermelding slaat op hoe ver de eerste byte van de rij zich vanaf het begin van de pagina bevindt. De functie van de rijverschiltabel is dus om SQL Server te helpen snel rijen op een pagina te vinden. De vermeldingen in de rijverschiltabel bevinden zich in omgekeerde volgorde van de volgorde van de rijen op de pagina.

Ondersteuning voor grote rijen

Rijen kunnen geen pagina's omvatten; delen van de rij kunnen echter van de pagina van de rij worden verplaatst, zodat de rij erg groot kan zijn. De maximale hoeveelheid gegevens en overhead die zich in één rij op een pagina bevindt, is 8060 bytes. Dit omvat niet de gegevens die zijn opgeslagen in het paginatype tekst/afbeelding.

Deze beperking is beperkt voor tabellen die varchar, nvarchar, varbinary of sql_variant kolommen bevatten. Wanneer de totale rijgrootte van alle vaste en variabele kolommen in een tabel de limiet van 8.060 bytes overschrijdt, verplaatst SQL Server dynamisch een of meer kolommen met variabele lengte naar pagina's in de ROW_OVERFLOW_DATA toewijzingseenheid, te beginnen met de kolom met de grootste breedte.

Dit wordt gedaan wanneer een invoeg- of bijwerkbewerking de totale grootte van de rij groter maakt dan de limiet van 8.060 bytes. Wanneer een kolom wordt verplaatst naar een pagina in de ROW_OVERFLOW_DATA toewijzingseenheid, wordt er een 24-byte-aanwijzer op de oorspronkelijke pagina in de IN_ROW_DATA toewijzingseenheid behouden. Als een volgende bewerking de rijgrootte verkleint, verplaatst SQL Server de kolommen dynamisch terug naar de oorspronkelijke gegevenspagina.

Overwegingen voor rijoverloop

Een rij kan niet op meerdere pagina's staan en kan overlopen als de gecombineerde grootte van gegevenstypevelden met variabele lengte de limiet van 8060 bytes overschrijdt. Ter illustratie kan een tabel worden gemaakt met twee kolommen: één varchar(7000) en een andere varchar (2000). Beide kolommen zijn afzonderlijk groter dan 8060 bytes, maar gecombineerd kunnen ze dit doen als de volledige breedte van elke kolom wordt gevuld. SQL Server kan de kolom varchar(7000) variabele lengte dynamisch verplaatsen naar pagina's in de ROW_OVERFLOW_DATA toewijzingseenheid. Wanneer u varchar, nvarchar, varbinary of sql_variant of door de gebruiker gedefinieerde CLR-kolommen combineert die groter zijn dan 8.060 bytes per rij, moet u rekening houden met het volgende:

• Het dynamisch verplaatsen van grote records naar een andere pagina vindt plaats omdat records worden uitgebreid op basis van updatebewerkingen. Bijwerkbewerkingen die records verkorten, kunnen ertoe leiden dat records worden teruggezet naar de oorspronkelijke pagina in de IN_ROW_DATA toewijzingseenheid.

  Het uitvoeren van query's en het uitvoeren van andere selectiebewerkingen, zoals sorteringen of joins op grote records die rij-overloopgegevens bevatten, vertraagt de verwerkingstijd, omdat deze records synchroon worden verwerkt in plaats van asynchroon.

  Wanneer u daarom een tabel ontwerpt met meerdere kolommen met varchar, nvarchar, varbinary of sql_variant of door de gebruiker gedefinieerde CLR-kolommen, moet u rekening houden met het percentage rijen dat waarschijnlijk zal stromen en de frequentie waarmee deze overloopgegevens waarschijnlijk worden opgevraagd. Als er waarschijnlijk frequente query's zijn op veel rijen met rijoverloopgegevens, kunt u overwegen om de tabel te normaliseren zodat sommige kolommen naar een andere tabel worden verplaatst. Dit kan vervolgens worden opgevraagd in een asynchrone JOIN bewerking.

• De lengte van afzonderlijke kolommen moet nog steeds binnen de limiet van 8000 bytes vallen voor varchar, nvarchar, varbinary of sql_variant en door de gebruiker gedefinieerde CLR-typekolommen. Alleen de gecombineerde lengte kan de rijlimiet van 8.060 bytes van een tabel overschrijden.

• De som van andere gegevenstypekolommen, inclusief teken - en nchar-gegevens , moet binnen de rijlimiet van 8060 bytes vallen. Grote objectgegevens zijn ook uitgesloten van de rijlimiet van 8.060 bytes.

• De indexsleutel van een geclusterde index kan geen varchar-kolommen bevatten met bestaande gegevens in de ROW_OVERFLOW_DATA toewijzingseenheid. Als een geclusterde index wordt gemaakt op een varchar-kolom en de bestaande gegevens zich in de IN_ROW_DATA toewijzingseenheid bevinden, mislukken volgende invoeg- of bijwerkacties in de kolom die de gegevens buiten de rij pushen. Zie de architectuur en ontwerphandleiding voor SQL Server- en Azure SQL-indexen voor meer informatie over toewijzingseenheden.

• U kunt kolommen opnemen die rij-overloopgegevens bevatten als sleutel- of niet-sleutelkolommen van een niet-geclusterde index.

• De recordgroottelimiet voor tabellen die sparsekolommen gebruiken, is 8018 bytes. Wanneer de geconverteerde gegevens plus bestaande recordgegevens groter zijn dan 8018 bytes, wordt MSSQLSERVER ERROR 576 geretourneerd. Wanneer kolommen worden geconverteerd tussen sparse- en niet-parseringstypen, bewaart de database-engine een kopie van de huidige recordgegevens. Hiermee verdubbelt u tijdelijk de opslag die nodig is voor de record.

• Gebruik de sys.dm_db_index_physical_stats dynamische beheerfunctie om informatie te verkrijgen over tabellen of indexen die mogelijk rijoverloopgegevens bevatten.

Mate

Gebieden zijn de basiseenheid waarin ruimte wordt beheerd. Een omvang is acht fysiek aaneengesloten pagina's of 64 kB. Dit betekent dat SQL Server-databases 16 gebieden per megabyte hebben.

SQL Server heeft twee typen gebieden:

• Uniforme gebieden zijn eigendom van één object; alle acht pagina's in de mate kunnen alleen worden gebruikt door het object dat eigenaar is.
• Gemengde gebieden worden gedeeld door maximaal acht objecten. Elk van de acht pagina's in de omvang kan eigendom zijn van een ander object.

Tot en met SQL Server 2014 (12.x) wijst de database-engine geen volledige gebieden toe aan tabellen met kleine hoeveelheden gegevens. Een nieuwe tabel of index wijst in het algemeen pagina's uit gemengde gebieden toe. Wanneer de tabel of index toeneemt tot het punt dat deze acht pagina's bevat, schakelt deze over naar uniforme gebieden voor volgende toewijzingen. Als u een index maakt voor een bestaande tabel met voldoende rijen om acht pagina's in de index te genereren, zijn alle toewijzingen aan de index uniform.

Vanaf SQL Server 2016 (13.x) is de standaardwaarde voor de meeste toewijzingen in een gebruikersdatabase en tempdb wordt gebruikgemaakt van uniforme gebieden, met uitzondering van toewijzingen die behoren tot de eerste acht pagina's van een IAM-keten. Toewijzingen voor master, msdben model databases behouden nog steeds het vorige gedrag.

Vanaf SQL Server 2012 (11.x) kan de sys.dm_db_database_page_allocations systeemfunctie paginatoewijzingsgegevens rapporteren voor een database, tabel, index en partitie.

Vanaf SQL Server 2019 (15.x) is de sys.dm_db_page_info systeemfunctie beschikbaar en wordt informatie geretourneerd over een pagina in een database. De functie retourneert één rij die de koptekstinformatie van de pagina bevat, inclusief de object_id, index_iden partition_id. Deze functie vervangt de noodzaak om in de meeste gevallen te gebruiken DBCC PAGE .

Omvangstoewijzingen en vrije ruimte beheren

De SQL Server-gegevensstructuren die omvangtoewijzingen beheren en vrije ruimte bijhouden, hebben een relatief eenvoudige structuur. Dit heeft de volgende voordelen:

• De informatie over vrije ruimte is dicht verpakt, dus relatief weinig pagina's bevatten deze informatie.

  Dit verhoogt de snelheid door het aantal leesbewerkingen op de schijf te verminderen dat nodig is om toewijzingsgegevens op te halen. Dit verhoogt ook de kans dat de toewijzingspagina's in het geheugen blijven en geen leesbewerkingen vereisen.

• De meeste toewijzingsgegevens worden niet aan elkaar gekoppeld. Dit vereenvoudigt het onderhoud van de toewijzingsgegevens.

  Elke paginatoewijzing of deallocatie kan snel worden uitgevoerd. Dit vermindert de conflicten tussen gelijktijdige taken die pagina's moeten toewijzen of de toewijzing ervan ongedaan moeten maken.

Toewijzingen van gebieden beheren

SQL Server maakt gebruik van twee typen toewijzingstoewijzingen om de toewijzing van gebieden vast te leggen:

• Global Allocation Map (GAM)

  GAM-pagina's registreren in welke mate er is toegewezen. Elke GAM omvat 64.000 gebieden, of bijna 4 gigabyte (GB) aan gegevens. De GAM heeft 1 bits voor elke mate in het interval dat wordt behandeld. Als de bit is 1, is de omvang vrij; als de bit is 0, wordt de omvang toegewezen.

• Shared Global Allocation Map (SGAM)

  SGAM-pagina's registreren welke gebieden momenteel worden gebruikt als gemengde gebieden en hebben ook ten minste één ongebruikte pagina. Elke SGAM omvat 64.000 gebieden of bijna 4 GB aan gegevens. De SGAM heeft 1 bits voor elke mate in het interval dat wordt behandeld. Als de bit is 1, wordt de omvang gebruikt als een gemengde omvang en heeft een gratis pagina. Als de bit is 0, wordt de omvang niet gebruikt als een gemengde omvang of is het een gemengde omvang en worden alle bijbehorende pagina's gebruikt.

Elke mate heeft de volgende bitpatronen ingesteld in de GAM en SGAM, op basis van het huidige gebruik.

Dit veroorzaakt eenvoudige beheeralgoritmen.

• Om een uniforme mate toe te wijzen, zoekt de database-engine de GAM een 1 beetje en stelt deze in op 0.
• Om een gemengde omvang met gratis pagina's te vinden, zoekt de database-engine de SGAM een 1 beetje.
• Als u een gemengde mate wilt toewijzen, zoekt de database-engine naar de GAM voor een 1 bit, stelt deze in 0en stelt vervolgens ook de bijbehorende bit in de SGAM in op 1.
• Als u de toewijzing van een bepaalde omvang ongedaan wilt maken, zorgt de database-engine ervoor dat de GAM-bit is ingesteld op 1en de SGAM-bit is ingesteld op 0.

De algoritmen die intern door de database-engine worden gebruikt, zijn geavanceerder dan wat in dit artikel wordt beschreven, omdat de database-engine gegevens gelijkmatig in een database distribueert. Zelfs de echte algoritmen worden echter vereenvoudigd door het niet hoeven te beheren van gegevens over omvangstoewijzing.

Vrije ruimte bijhouden

PfS-pagina's (Vrije ruimte) voor pagina's registreren de toewijzingsstatus van elke pagina, of er een afzonderlijke pagina is toegewezen en de hoeveelheid vrije ruimte op elke pagina. De PFS heeft 1 byte voor elke pagina, geeft aan of de pagina is toegewezen, en zo ja, of deze leeg is, 1 tot 50 procent vol, 51 tot 80 procent vol, 81 tot 95 procent vol, of 96 tot 100 procent vol.

Nadat een gedeelte is toegewezen aan een object, gebruikt de database-engine de PFS-pagina's om vast te leggen welke pagina's in de mate worden toegewezen of gratis. Deze informatie wordt gebruikt wanneer de database-engine een nieuwe pagina moet toewijzen. De hoeveelheid vrije ruimte op een pagina blijft alleen behouden voor heap- en tekst-/afbeeldingspagina's. Deze wordt gebruikt wanneer de database-engine een pagina met vrije ruimte moet vinden die beschikbaar is voor het opslaan van een nieuw ingevoegde rij. Indexen vereisen niet dat de vrije paginaruimte wordt bijgehouden, omdat het punt waarop een nieuwe rij moet worden ingevoegd, wordt ingesteld door de indexsleutelwaarden.

Er wordt een nieuwe PFS-, GAM- of SGAM-pagina toegevoegd aan het gegevensbestand voor elk extra bereik dat wordt bijgehouden. Er is dus een nieuwe PFS-pagina 8088 pagina's na de eerste PFS-pagina en extra PFS-pagina's in volgende intervallen van 8.088 pagina's. Ter illustratie is pagina-id 1 een PFS-pagina, pagina-id 8088 is een PFS-pagina, pagina-id 16176 is een PFS-pagina, enzovoort.

Er is een nieuwe GAM-pagina 64.000 gebieden na de eerste GAM-pagina en deze houdt de 64.000-gebieden volgen; de reeks wordt voortgezet met intervallen van 64.000 gebieden. Op dezelfde manier is er een nieuwe SGAM-pagina 64.000 gebieden na de eerste SGAM-pagina en extra SGAM-pagina's in volgende intervallen van 64.000 gebieden.

In de volgende afbeelding ziet u de volgorde van pagina's die door de database-engine worden gebruikt om gebieden toe te wijzen en te beheren.

Ruimte beheren die wordt gebruikt door objecten

Een IAM-pagina (Index Allocation Map) wijst de gebieden toe in een deel van een databasebestand van 4 GB dat wordt gebruikt door een toewijzingseenheid. Een toewijzingseenheid is een van de volgende drie typen:

• IN_ROW_DATA

  Bevat een partitie van een heap of index.

• LOB_DATA

  Bevat grote objectgegevenstypen (LOB), zoals xml, varbinary(max), en varchar(max).

• ROW_OVERFLOW_DATA

  Bevat variabele lengtegegevens die zijn opgeslagen in varchar, nvarchar, varbinary of sql_variant kolommen die de limiet van 8.060 byterijen overschrijden.

Elke partitie van een heap of index bevat ten minste een IN_ROW_DATA toewijzingseenheid. Het kan ook een LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheid bevatten, afhankelijk van het heap- of indexschema.

Een IAM-pagina heeft betrekking op een bereik van 4 GB in een bestand en is dezelfde dekking als een GAM- of SGAM-pagina. Als de toewijzingseenheid gebieden van meer dan één bestand of meer dan één bereik van 4 GB van een bestand bevat, zijn er meerdere IAM-pagina's gekoppeld in een IAM-keten. Daarom heeft elke toewijzingseenheid ten minste één IAM-pagina voor elk bestand waarvan het aantal gebieden heeft. Er kunnen ook meer dan één IAM-pagina in een bestand zijn, als het bereik van de gebieden in het bestand dat aan de toewijzingseenheid is toegewezen, groter is dan het bereik dat één IAM-pagina kan vastleggen.

IAM-pagina's worden toegewezen zoals vereist voor elke toewijzingseenheid en bevinden zich willekeurig in het bestand. De sys.system_internals_allocation_units systeemweergave verwijst naar de eerste IAM-pagina voor een toewijzingseenheid. Alle IAM-pagina's voor die toewijzingseenheid zijn gekoppeld in een IAM-keten.

Een IAM-pagina heeft een koptekst die de beginbereiken aangeeft van het bereik van gebieden die zijn toegewezen door de IAM-pagina. De IAM-pagina heeft ook een grote bitmap waarin elke bit één mate vertegenwoordigt. De eerste bit in de kaart vertegenwoordigt de eerste mate in het bereik, de tweede bit vertegenwoordigt de tweede mate, enzovoort. Als een bit is, wordt de mate waarin deze vertegenwoordigt niet toegewezen aan de toewijzingseenheid die eigenaar is 0van de IAM. Als de bit is 1, wordt de omvang ervan toegewezen aan de toewijzingseenheid die eigenaar is van de IAM-pagina.

Wanneer de database-engine een nieuwe rij moet invoegen en er geen ruimte beschikbaar is op de huidige pagina, worden de IAM- en PFS-pagina's gebruikt om een pagina te vinden die moet worden toegewezen, of, voor een heap- of tekst-/afbeeldingspagina, een pagina met voldoende ruimte om de rij vast te houden. De database-engine gebruikt de IAM-pagina's om de gebieden te vinden die aan de toewijzingseenheid zijn toegewezen. Voor elke mate doorzoekt de database-engine de PFS-pagina's om te zien of er een pagina is die kan worden gebruikt. Elke IAM- en PFS-pagina bevat veel gegevenspagina's, dus er zijn weinig IAM- en PFS-pagina's in een database. Dit betekent dat de IAM- en PFS-pagina's zich in het algemeen in het geheugen in de SQL Server-buffergroep bevinden, zodat ze snel kunnen worden doorzocht. Voor indexen wordt de invoegpositie van een nieuwe rij ingesteld door de indexsleutel, maar wanneer een nieuwe pagina nodig is, vindt het eerder beschreven proces plaats.

De database-engine wijst alleen een nieuwe mate toe aan een toewijzingseenheid wanneer een pagina in een bestaande mate niet snel kan worden gevonden, met voldoende ruimte om de rij die wordt ingevoegd te bewaren.

Proportionele opvullingstoewijzing

De database-engine wijst gebieden toe van de gebieden die beschikbaar zijn in de bestandsgroep met behulp van een proportioneel vultoewijzingsalgoritme. In dezelfde bestandsgroep met twee bestanden, als het ene bestand de vrije ruimte verdubbelt als het andere, worden twee pagina's uit het bestand toegewezen met de beschikbare ruimte voor elke pagina die uit het andere bestand is toegewezen. Dit betekent dat elk bestand in een bestandsgroep een vergelijkbaar percentage ruimte moet hebben dat wordt gebruikt.

Gewijzigde gebieden bijhouden

SQL Server maakt gebruik van twee interne gegevensstructuren voor het bijhouden van gebieden die zijn gewijzigd door bulkkopiebewerkingen en gebieden die zijn gewijzigd sinds de laatste volledige back-up. Deze gegevensstructuren versnellen differentiële back-ups aanzienlijk. Ze versnellen ook de logboekregistratie van bulkkopiebewerkingen wanneer een database gebruikmaakt van het bulksgewijs vastgelegde herstelmodel. Net als de GAM- en SGAM-pagina's zijn deze structuren bitmaps waarin elke bit één mate vertegenwoordigt.

• Differentiële gewijzigde kaart (DCM)

  Hiermee worden de gebieden bijgehouden die zijn gewijzigd sinds de laatste BACKUP DATABASE instructie. Als de bit voor een bepaalde mate is 1, is de omvang gewijzigd sinds de laatste BACKUP DATABASE instructie. Als de bit is 0, is de omvang niet gewijzigd.

  Differentiële back-ups lezen alleen de DCM-pagina's om te bepalen welke mate is gewijzigd. Dit vermindert het aantal pagina's dat een differentiële back-up moet scannen aanzienlijk. De tijdsduur waarop een differentiële back-up wordt uitgevoerd, is evenredig met het aantal gewijzigde gebieden sinds de laatste BACKUP DATABASE instructie en niet de totale grootte van de database.

• Bulksgewijs gewijzigde kaart (BCM)

  Hiermee worden de gebieden bijgehouden die zijn gewijzigd door bulksgewijs vastgelegde bewerkingen sinds de laatste BACKUP LOG instructie. Als de bit voor een bepaalde mate is 1, is de omvang gewijzigd door een bulksgewijs vastgelegde bewerking na de laatste BACKUP LOG instructie. Als de bit is 0, is de omvang niet gewijzigd door bulksgewijs vastgelegde bewerkingen.

  Hoewel BCM-pagina's in alle databases worden weergegeven, zijn ze alleen relevant wanneer de database gebruikmaakt van het bulksgewijs vastgelegde herstelmodel. Wanneer een BACKUP LOG back-upproces in dit herstelmodel wordt uitgevoerd, scant het back-upproces de BCM's op gebieden die zijn gewijzigd. Vervolgens worden deze gebieden opgenomen in de logboekback-up. Hiermee worden de bulkbewerkingen hersteld als de database wordt hersteld vanuit een databaseback-up en een reeks back-ups van transactielogboeken. BCM-pagina's zijn niet relevant in een database die gebruikmaakt van het eenvoudige herstelmodel, omdat er geen bulkbewerkingen worden vastgelegd. Ze zijn niet relevant in een database die gebruikmaakt van het volledige herstelmodel, omdat dat herstelmodel bulksgewijs geregistreerde bewerkingen behandelt als volledig vastgelegde bewerkingen.

Het interval tussen DCM-pagina's en BCM-pagina's is hetzelfde als het interval tussen de GAM- en SGAM-pagina, 64.000 gebieden. De DCM- en BCM-pagina's bevinden zich als volgt achter de GAM- en SGAM-pagina's in een fysiek bestand:

Verwante inhoud

• Basisprincipes van SQL Server I/O
• sys.allocation_units (Transact-SQL)
• Heaps (tabellen zonder geclusterde indexen)
• sys.dm_db_page_info (Transact-SQL)
• Gegevenspagina's lezen in de database-engine
• Pagina's schrijven in de database-engine