SORT_IN_TEMPDB optie voor indexen

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

Wanneer u een index maakt of herbouwt, kunt u de optie SORT_IN_TEMPDB op AAN instellen, kunt u de SQL Server Database Engine omleiden om tempdb te gebruiken om de tussenliggende sorteerresultaten op te slaan die worden gebruikt om de index te bouwen. Hoewel deze optie de hoeveelheid tijdelijke schijfruimte verhoogt die wordt gebruikt om een index te maken, kan de optie de tijd verkorten die nodig is om een index te maken of opnieuw te bouwen wanneer tempdb zich op een set schijven bevindt die afwijken van die van de gebruikersdatabase. Zie Configureer de geheugentoewijzing voor het maken van index Server Configuration Option voor meer informatie over tempdb.

Fasen van de indexopbouw

Terwijl de database-engine een index bouwt, doorloopt deze de volgende fasen:

• De database-engine scant eerst de gegevenspagina's van de basistabel om sleutelwaarden op te halen en bouwt een indexbladrij voor elke gegevensrij. Wanneer de interne sorteerbuffers zijn gevuld met bladindexvermeldingen, worden de vermeldingen gesorteerd en naar schijf geschreven als een tussenliggende sorteeruitvoering. De database-engine hervat vervolgens de scan van de gegevenspagina totdat de sorteerbuffers opnieuw zijn gevuld. Dit patroon van het scannen van meerdere gegevenspagina's, gevolgd door het sorteren en schrijven van een sorteeruitvoering, gaat door totdat alle rijen van de basistabel zijn verwerkt.

  In een geclusterde index zijn de bladrijen van de index de gegevensrijen van de tabel; Daarom bevatten de tussenliggende sorteeruitvoeringen alle gegevensrijen. In een niet-geclusterde index kunnen de bladrijen niet-sleutelkolommen bevatten, maar zijn over het algemeen kleiner dan een geclusterde index. Als de indexsleutels groot zijn, of als er meerdere niet-sleutelkolommen in de index zijn opgenomen, kan een niet-geclusterde sorteerrun groot zijn. Zie Indexen maken met opgenomen kolommen voor meer informatie over het opnemen van niet-sleutelkolommen.

• De database-engine voegt de gesorteerde uitvoeringen van indexbladrijen samen in één, gesorteerde stroom. Het sort merge-onderdeel van de database-engine begint met de eerste pagina van elke sorteeruitvoering, zoekt de laagste sleutel op alle pagina's en geeft die bladrij door aan het onderdeel index maken. De volgende laagste sleutel wordt verwerkt en vervolgens de volgende, enzovoort. Wanneer de laatste bladbladindexrij wordt geëxtraheerd uit een sorteeruitvoeringspagina, wordt het proces verplaatst naar de volgende pagina van die sorteeruitvoering. Wanneer alle pagina's in een sorteeruitvoeringslaag zijn verwerkt, wordt de omvang vrijgemaakt. Omdat elke bladindexrij wordt doorgegeven aan het onderdeel index maken, wordt deze opgenomen in een bladindexpagina in de buffer. Elke bladpagina wordt beschreven zodra deze is gevuld. Naarmate bladpagina's worden geschreven, bouwt de database-engine ook de bovenste niveaus van de index. Elke indexpagina op het hoogste niveau wordt geschreven wanneer deze wordt ingevuld.

optie voor SORT_IN_TEMPDB

Wanneer SORT_IN_TEMPDB is ingesteld op UIT, worden de sorteeruitvoeringen standaard opgeslagen in de doelbestandsgroep. Tijdens de eerste fase van het maken van de index verplaatsen de afwisselende leesbewerkingen van de basistabelpagina's en schrijfbewerkingen van de sorteerbewerkingen de lees-/schrijfkoppen van de schijf van het ene gebied naar het andere. De hoofden bevinden zich in het gebied van de gegevenspagina terwijl de gegevenspagina's worden gescand. Ze worden verplaatst naar een vrije ruimtegebied wanneer de sorteerbuffers worden opgevuld en de huidige sorteeruitvoering naar de schijf moet worden geschreven en vervolgens teruggaan naar het gebied van de gegevenspagina wanneer de scan van de tabelpagina wordt hervat. De lees-/schrijfkopverplaatsing is groter in de tweede fase. Op dat moment bestaat het sorteerproces meestal uit het afwisselend lezen van elk sorteeruitvoeringsgebied. Zowel de sorteerbewerkingen als de nieuwe indexpagina's zijn ingebouwd in de doelbestandsgroep. Dit betekent dat de database-engine tegelijkertijd leesbewerkingen verspreidt over de sorteeruitvoeringen. Het moet periodiek naar de index gaan om nieuwe indexpagina's te schrijven terwijl ze worden gevuld.

Als de optie SORT_IN_TEMPDB is ingesteld op AAN en tempdb zich op een afzonderlijke set schijven van de doelbestandsgroep bevindt, vinden tijdens de eerste fase de leesbewerkingen van de gegevenspagina's plaats op een andere schijf dan de schrijfbewerkingen naar het sorteerwerkgebied in tempdb. Dit betekent dat de schijfleesbewerkingen van de gegevenssleutels over het algemeen meer serieel worden voortgezet op de schijf en dat de schrijfbewerkingen naar de tempdb-schijf ook over het algemeen serieel zijn, net zoals de schrijfbewerkingen om de uiteindelijke index te bouwen. Zelfs als andere gebruikers de database gebruiken en toegang hebben tot afzonderlijke schijfadressen, is het algemene patroon van lees- en schrijfbewerkingen efficiënter wanneer SORT_IN_TEMPDB wordt opgegeven dan wanneer dat niet het is.

De optie SORT_IN_TEMPDB kan de aaneengeslotenheid van indexuitbreidingen verbeteren, met name als de CREATE INDEX-bewerking niet parallel wordt uitgevoerd. De omvang van de sorteerwerkgebieden wordt op een enigszins willekeurige wijze vrijgegeven, ongeacht hun locatie in de database. Als de sorteerwerkgebieden zich in de doelbestandsgroep bevinden, omdat de sorteerwerkgebieden worden vrijgemaakt, kunnen ze worden verkregen door de aanvragen voor gebieden die de indexstructuur bevatten terwijl deze is gebouwd. Hiermee kunnen de locaties van de indexen tot een bepaalde mate worden gerandomaliseerd. Als de sorteergrootten afzonderlijk in tempdb worden bewaard, heeft de volgorde waarin ze worden vrijgemaakt, geen effect op de locatie van de index-gebieden. Wanneer de tussenliggende sorteeruitvoeringen worden opgeslagen in tempdb in plaats van de doelbestandsgroep, is er ook meer ruimte beschikbaar in de doelbestandsgroep. Dit verhoogt de kans dat index-gebieden aaneengesloten zijn.

De optie SORT_IN_TEMPDB is alleen van invloed op de huidige verklaring. Er zijn geen metagegevensrecords die aangeven of de index al dan niet in tempdb was gesorteerd. Als u bijvoorbeeld een niet-geclusterde index maakt met behulp van de optie SORT_IN_TEMPDB en op een later tijdstip een geclusterde index maakt zonder de optie op te geven, gebruikt de database-engine de optie niet wanneer de niet-geclusterde index opnieuw wordt gemaakt.

Note

Als een sorteerbewerking niet vereist is of als de sortering in het geheugen kan worden uitgevoerd, wordt de optie SORT_IN_TEMPDB genegeerd.

Vereisten voor schijfruimte

Wanneer u de optie SORT_IN_TEMPDB instelt op AAN, moet er voldoende vrije schijfruimte beschikbaar zijn in tempdb om de tussenliggende sorteeruitvoeringen te kunnen bevatten en voldoende vrije schijfruimte in de doelbestandsgroep om de nieuwe index op te slaan. De instructie CREATE INDEX mislukt als er onvoldoende vrije ruimte is en er een reden is waarom de databases niet automatisch kunnen uitbreiden om meer ruimte te verkrijgen, zoals wanneer er geen ruimte op de schijf is of automatisch uitbreiden staat uit.

Als SORT_IN_TEMPDB is ingesteld op UIT, moet de beschikbare vrije schijfruimte in de doelbestandsgroep ongeveer de grootte van de uiteindelijke index zijn. Tijdens de eerste fase worden de sorteeruitvoeringen gebouwd en is ongeveer dezelfde hoeveelheid ruimte nodig als de uiteindelijke index. Tijdens de tweede fase wordt elke sorteeruitvoeringslaag vrijgemaakt nadat deze is verwerkt. Dit betekent dat sorteeruitvoeringsbereiken vrij zijn van ongeveer dezelfde snelheid waarmee de uiteindelijke indexpagina's worden verkregen; Daarom overschrijden de algemene ruimtevereisten niet aanzienlijk de grootte van de uiteindelijke index. Een neveneffect hiervan is dat als de hoeveelheid vrije ruimte zeer dicht bij de grootte van de uiteindelijke index ligt, de database-engine de sorteeruitvoeringsbereiken doorgaans zeer snel hergebruikt nadat ze zijn vrijgemaakt. Omdat de sorteerrun-gebieden enigszins willekeurig worden vrijgemaakt, vermindert dit de continuïteit van de index-gebieden in dit scenario. Als SORT_IN_TEMPDB is ingesteld op UIT, wordt de continuïteit van de indexbereiken verbeterd als er voldoende vrije ruimte beschikbaar is in de doelbestandsgroep zodat de indexbereiken kunnen worden toegewezen vanuit een aaneengesloten pool in plaats van uit de pas vrijgegeven sorteerbereiken.

Wanneer u een niet-geclusterde index maakt, moet u vrije ruimte beschikbaar hebben.

• Als SORT_IN_TEMPDB is ingesteld op AAN, moet er voldoende vrije ruimte in tempdb zijn om de sorteeruitvoeringen op te slaan en voldoende vrije ruimte in de doelbestandsgroep om de uiteindelijke indexstructuur op te slaan. De sorteeruitvoeringen bevatten de bladrijen van de index.

• Als SORT_IN_TEMPDB is ingesteld op UIT, moet de vrije ruimte in de doelbestandsgroep groot genoeg zijn om de uiteindelijke indexstructuur op te slaan. De continuïteit van de index kan worden verbeterd als er meer vrije ruimte beschikbaar is.

Wanneer u een geclusterde index maakt voor een tabel die geen niet-geclusterde indexen bevat, moet u beschikbaar zijn als vrije ruimte:

• Als SORT_IN_TEMPDB is ingesteld op AAN, moet er voldoende vrije ruimte in tempdb zijn om de sorteeruitvoeringen op te slaan. Deze omvatten de gegevensrijen van de tabel. Er moet voldoende vrije ruimte in de doelbestandsgroep zijn om de uiteindelijke indexstructuur op te slaan. Dit omvat de gegevensrijen van de tabel en de index B-structuur. Mogelijk moet u de schatting aanpassen voor factoren zoals het hebben van een grote sleutel of een opvulfactor met een lage waarde.

• Als SORT_IN_TEMPDB is ingesteld op UIT, moet de vrije ruimte in de doelbestandsgroep groot genoeg zijn om de uiteindelijke tabel op te slaan. Dit omvat de indexstructuur. De continuïteit van de tabel- en indexafstanden kan worden verbeterd als er meer vrije ruimte beschikbaar is.

Wanneer u een geclusterde index maakt voor een tabel met niet-geclusterde indexen, moet u ervoor zorgen dat er voldoende vrije ruimte beschikbaar is:

• Als SORT_IN_TEMPDB is ingesteld op AAN, moet er voldoende vrije ruimte in tempdb zijn om de verzameling sorteeruitvoeringen op te slaan voor de grootste index, meestal de geclusterde index, en voldoende vrije ruimte in de doelbestandsgroep om de uiteindelijke structuren van alle indexen op te slaan. Dit omvat de geclusterde index die de gegevensrijen van de tabel bevat.

• Als SORT_IN_TEMPDB is ingesteld op UIT, moet de vrije ruimte in de doelbestandsgroep groot genoeg zijn om de uiteindelijke tabel op te slaan. Dit omvat de structuren van alle indexen. De continuïteit van de tabel- en indexafstanden kan worden verbeterd als er meer vrije ruimte beschikbaar is.

Gerelateerde taken

MAAK INDEX AAN (Transact-SQL)

Indexen opnieuw ordenen en herbouwen

Verwante inhoud

ALTER INDEX (Transact-SQL)

De configuratieoptie voor het maken van een geheugenserver configureren

schijfruimtevereisten voor DDL-indexbewerkingen