Delen via

Indexonderhoud optimaliseren om queryprestaties te verbeteren en resourceverbruik te verminderen

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)SQL-database in Microsoft Fabric

In dit artikel kunt u bepalen wanneer en hoe u indexonderhoud uitvoert. Hierin worden concepten behandeld, zoals indexfragmentatie en paginadichtheid, en de invloed ervan op queryprestaties en resourceverbruik. Hierin worden indexonderhoudsmethoden beschreven, het opnieuw ordenen van een index en het herbouwen van een index, en wordt een indexonderhoudsstrategie voorgesteld die mogelijke prestatieverbeteringen in balans brengt ten opzichte van het resourceverbruik dat nodig is voor onderhoud.

Notitie

Dit artikel is niet van toepassing op een toegewezen SQL-pool in Azure Synapse Analytics. Zie Indexering van toegewezen SQL-pooltabellen in Azure Synapse Analytics voor informatie over indexonderhoud voor een toegewezen SQL-pool in Azure Synapse Analytics.

Concepten: indexfragmentatie en paginadichtheid

Wat is indexfragmentatie en hoe dit van invloed is op de prestaties:

  • In B-tree-indexen (rowstore) bestaat fragmentatie wanneer indexen pagina's bevatten waarin de logische volgorde binnen de index, op basis van de sleutelwaarden van de index, niet overeenkomt met de fysieke volgorde van indexpagina's.

    Notitie

    Documentatie maakt gebruik van de term B-tree in het algemeen in verwijzing naar indexen. In rijstore-indexen implementeert de database-engine een B+-boom. Dit geldt niet voor columnstore-indexen of indexen voor tabellen die zijn geoptimaliseerd voor geheugen. Zie de SQL Server- en Azure SQL-indexarchitectuur en ontwerphandleidingvoor meer informatie.

  • De database-engine wijzigt automatisch indexen wanneer er bewerkingen worden ingevoegd, bijgewerkt of verwijderd naar de onderliggende gegevens. De toevoeging van rijen in een tabel kan er bijvoorbeeld toe leiden dat bestaande pagina's in rijopslagindexen worden gesplitst, waardoor ruimte ontstaat voor de invoeging van nieuwe rijen. Na verloop van tijd kunnen deze wijzigingen ertoe leiden dat de gegevens in de index worden verspreid in de database (gefragmenteerd).

  • Voor query's die veel pagina's doorlezen met volledige- of bereikindexscans, kunnen sterk gefragmenteerde indexen de prestaties van query's verminderen wanneer extra I/O vereist is om de data in te lezen. In plaats van een klein aantal grote I/O-aanvragen, vereist de query een groter aantal kleine I/O-aanvragen om dezelfde hoeveelheid gegevens te lezen.

  • Wanneer het opslagsubsysteem betere sequentiële I/O-prestaties biedt dan willekeurige I/O-prestaties, kan indexfragmentatie de prestaties verminderen omdat er meer willekeurige I/O is vereist om gefragmenteerde indexen te lezen.

Wat is paginadichtheid (ook wel paginavolheid genoemd) en hoe dit van invloed is op de prestaties:

  • Elke pagina in de database kan een variabel aantal rijen bevatten. Als rijen alle ruimte op een pagina innemen, is de paginadichtheid 100%. Als een pagina leeg is, is de paginadichtheid 0%. Als een pagina met 100%-dichtheid wordt gesplitst in twee pagina's voor een nieuwe rij, is de dichtheid van de twee nieuwe pagina's ongeveer 50%.
  • Wanneer de paginadichtheid laag is, zijn er meer pagina's nodig om dezelfde hoeveelheid gegevens op te slaan. Dit betekent dat er meer I/O nodig is om deze gegevens te lezen en schrijven, en dat er meer geheugen nodig is om deze gegevens in de cache op te cachen. Wanneer het geheugen beperkt is, worden er minder pagina's in de cache opgeslagen die nodig zijn voor een query, waardoor er nog meer schijf-I/O wordt veroorzaakt. Daarom heeft een lage paginadichtheid een negatieve invloed op de prestaties.
  • Wanneer Database Engine rijen toevoegt aan een pagina tijdens het maken, herbouwen of opnieuw ordenen van indexen, wordt de pagina niet volledig gevuld als de vulfactor voor de index is ingesteld op een andere waarde dan 100 (of 0, wat gelijk is in deze context). Dit veroorzaakt een lagere paginadichtheid en voegt op dezelfde manier I/O-overhead toe en heeft een negatieve invloed op de prestaties.
  • Lage paginadichtheid kan het aantal tussenliggende B-structuurniveaus verhogen. Dit verhoogt de CPU- en I/O-kosten voor het vinden van pagina's op bladniveau in indexscans en -zoekbewerkingen.
  • Wanneer de Query Optimizer een queryplan compileert, worden de kosten van I/O in acht gebracht die nodig zijn om de gegevens te lezen die nodig zijn voor de query. Met lage paginadichtheid zijn er meer pagina's om te lezen, dus de kosten van I/O zijn hoger. Dit kan van invloed zijn op de keuze van het queryplan. Als de paginadichtheid bijvoorbeeld afneemt na verloop van tijd vanwege paginasplitsingen, kan de optimizer een ander plan voor dezelfde query compileren, met een ander prestatie- en resourceverbruiksprofiel.

Tip/Fooi

In veel workloads resulteert het verhogen van de paginadichtheid in een grotere positieve invloed op de prestaties dan het verminderen van fragmentatie.

Om te voorkomen dat de paginadichtheid onnodig wordt verlaagd, raadt Microsoft niet aan om de opvulfactor in te stellen op andere waarden dan 100 of 0, behalve in bepaalde gevallen voor indexen met een groot aantal paginasplitsingen, bijvoorbeeld veelgebruikte indexen met voorloopkolommen met niet-sequentiële GUID-waarden.

Indexfragmentatie en paginadichtheid meten

Zowel fragmentatie als paginadichtheid zijn een van de factoren waarmee u rekening moet houden bij het bepalen of indexonderhoud moet worden uitgevoerd en welke onderhoudsmethode moet worden gebruikt.

Fragmentatie wordt anders gedefinieerd voor rowstore- en columnstore indexen. Voor rowstore-indexen kunt u met sys.dm_db_index_physical_stats() de fragmentatie en paginadichtheid bepalen in een specifieke index, in alle indexen op een tabel of geïndexeerde weergave, in alle indexen in een database, of in alle indexen in alle databases. Voor gepartitioneerde indexen geeft sys.dm_db_index_physical_stats() deze informatie voor elke partitie.

De resultatenset die door sys.dm_db_index_physical_stats wordt geretourneerd, bevat de volgende kolommen:

Kolom Beschrijving
avg_fragmentation_in_percent Logische fragmentatie (niet-op-volgorde pagina's in de index).
avg_page_space_used_in_percent Gemiddelde paginadichtheid.

Voor gecomprimeerde rijgroepen in columnstore-indexen wordt fragmentatie gedefinieerd als de verhouding tussen verwijderde rijen en totaalrijen, uitgedrukt als een percentage. sys.dm_db_column_store_row_group_physical_stats kunt u bepalen hoeveel totaal- en verwijderde rijen er per rijgroep zijn in een specifieke index, alle indexen van een tabel of alle indexen in een database.

De resultatenset die door sys.dm_db_column_store_row_group_physical_stats wordt geretourneerd, bevat de volgende kolommen:

Kolom Beschrijving
total_rows Het aantal rijen dat fysiek is opgeslagen in de rijgroep. Voor gecomprimeerde rijgroepen omvat dit de rijen die zijn gemarkeerd als verwijderd.
deleted_rows Het aantal rijen dat fysiek is opgeslagen in een gecomprimeerde rijgroep die is gemarkeerd voor verwijdering. 0 voor rijgroepen die zich in de delta store bevinden.

Fragmentatie van gecomprimeerde rijgroepen in een columnstore-index kan worden berekend met behulp van deze formule:

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Fooi

Controleer voor zowel rowstore- als columnstore-indexen de index- of heapfragmentatie en de paginadichtheid nadat een groot aantal rijen is verwijderd of bijgewerkt. Als er regelmatig updates zijn, controleert u voor heaps regelmatig fragmentatie om verspreiding van doorsturen van records te voorkomen. Voor meer informatie over heaps, zie Heaps (Tabellen zonder geclusterde indexen).

Zie Voorbeelden voor voorbeeldquery's om fragmentatie en paginadichtheid te bepalen.

Onderhoudsmethoden voor indexen: opnieuw organiseren en opnieuw bouwen

U kunt indexfragmentatie verminderen en de paginadichtheid verhogen met behulp van een van de volgende methoden:

  • Reorganiseer een index opnieuw ordenen
  • Een index opnieuw samenstellen

Notitie

Voor gepartitioneerde-indexen kunt u een van de volgende methoden gebruiken voor alle partities of één partitie van een index.

Een index opnieuw ordenen

Het herstructureren van een index is minder resource-intensief dan het herbouwen van een index. Daarom moet het uw voorkeursmethode voor indexonderhoud zijn, tenzij er een specifieke reden is om index opnieuw op te bouwen. Herorganiseren is altijd een onlinebewerking. Dit betekent dat langetermijnvergrendelingen op objectniveau niet worden bewaard en dat query's of updates voor de onderliggende tabel kunnen worden voortgezet tijdens de ALTER INDEX ... REORGANIZE bewerking.

  • Voor rijopslagindexen, defragmenteert de database-engine alleen het bladniveau van geclusterde en niet-geclusterde indexen op tabellen en weergaven door de pagina's op bladniveau fysiek te ordenen zodat deze overeenkomen met de logische volgorde van de bladknooppunten (van links naar rechts). Door indexpagina's opnieuw te ordenen, worden pagina's gecomprimeerd zodat de paginadichtheid gelijk is aan de vulfactor van de index. Als u de instelling voor de vulfactor wilt weergeven, gebruikt u sys.indexes. Voor syntaxisvoorbeelden, zie Voorbeelden - Rowstore reorganiseren.
  • Wanneer u columnstore-indexengebruikt, kan het Delta-archief eindigen met meerdere kleine rijgroepen na het invoegen, bijwerken en verwijderen van gegevens in de loop van de tijd. Door een columnstore-index te herstructureren, worden rijgroepen van de delta store omgezet in gecomprimeerde rijgroepen binnen de columnstore, en worden kleinere gecomprimeerde rijgroepen gecombineerd tot grotere rijgroepen. Bij de herorganiseer-bewerking worden ook fysiek rijen verwijderd die als verwijderd zijn gemarkeerd in de columnstore. Het opnieuw ordenen van een columnstore-index kan extra CPU-resources vereisen om gegevens te comprimeren. Terwijl de bewerking wordt uitgevoerd, kan de prestatie verminderen. Zodra gegevens zijn gecomprimeerd, worden de queryprestaties echter verbeterd. Zie Voorbeelden - Columnstoreopnieuw organiseren voor syntaxisvoorbeelden.

Notitie

Vanaf SQL Server 2019 (15.x), Azure SQL Database en Azure SQL Managed Instance wordt de tuple-mover geholpen door een achtergrondsamenvoegtaak waarmee automatisch kleinere open deltarijgroepen worden gecomprimeerd die al enige tijd bestaan, zoals bepaald door een interne drempelwaarde, of gecomprimeerde rijen worden samengevoegd waaruit een groot aantal rijen is verwijderd. Dit verbetert de kwaliteit van de columnstore-index in de loop van de tijd. In de meeste gevallen maakt dit de noodzaak voor het uitgeven van ALTER INDEX ... REORGANIZE-commando's overbodig.

Tip

Als u een herorganisatie annuleert of als deze anders wordt onderbroken, wordt de voortgang die naar dat punt is gemaakt, behouden in de database. Als u grote indexen opnieuw wilt ordenen, kan de bewerking meerdere keren worden gestart en gestopt totdat deze is voltooid.

Een index opnieuw samenstellen

Het opnieuw opbouwen van een index verwijdert de index en maakt deze opnieuw aan. Afhankelijk van het type index en de versie van de database-engine, kan een herbouwbewerking offline of online worden uitgevoerd. Het opnieuw opbouwen van een offline index duurt meestal minder dan een online herbouwbewerking, maar bevat vergrendelingen op objectniveau voor de duur van de herbouwbewerking, waardoor query's geen toegang hebben tot de tabel of weergave.

Een online index opnieuw opbouwen vereist geen vergrendelingen op objectniveau tot het einde van de bewerking, wanneer een vergrendeling gedurende een korte duur moet worden bewaard om de herbouw te voltooien. Afhankelijk van de versie van de database-engine kan een online herbouw van een index worden gestart als een hervatbare bewerking. Een hervatbare herbouw van indexen kan worden onderbroken, zodat de voortgang tot dat punt behouden blijft. Een hervatbare herbouwbewerking kan worden hervat nadat deze is gepauzeerd of onderbroken, of afgebroken worden als het voltooien van de herbouw onnodig is.

Zie ALTER INDEX REBUILDvoor Transact-SQL syntaxis. Zie Indexbewerkingen online uitvoerenvoor meer informatie over het opnieuw samenstellen van online indexen.

Notitie

Hoewel een index online opnieuw wordt opgebouwd, moet elke wijziging van gegevens in geïndexeerde kolommen een extra kopie van de index bijwerken. Dit kan leiden tot een kleine prestatievermindering van instructies voor het wijzigen van gegevens tijdens het online opnieuw opbouwen.

Als een online hervatbare indexbewerking wordt onderbroken, blijft deze invloed op de prestaties behouden totdat de hervatbare bewerking is voltooid of afgebroken. Als u niet van plan bent een hervatbare indexbewerking te voltooien, moet u deze afbreken in plaats van deze te onderbreken.

Fooi

Afhankelijk van de beschikbare resources en werkbelastingpatronen kan het opgeven van een hogere waarde dan de standaardwaarde MAXDOP in de instructie ALTER INDEX REBUILD-instructie de duur van herbouw verkorten ten koste van een hoger CPU-gebruik.

  • Het herbouwen van rowstore-indexenverwijdert fragmentatie in alle niveaus van de index en compacteert pagina's op basis van de opgegeven of huidige opvulfactor. Wanneer ALL is opgegeven, worden alle indexen in de tabel verwijderd en opnieuw opgebouwd in één bewerking. Wanneer indexen met 128 of meer gebieden opnieuw worden opgebouwd, worden de vrijmakingen van pagina's en het verkrijgen van de bijbehorende vergrendelingen uitgesteld totdat de herbouw is voltooid. Zie voor voorbeelden van syntaxis Voorbeelden - Rowstore opnieuw opbouwen.

  • Voor columnstore-indexenwordt fragmentatie verwijderd, worden alle delta store-rijen naar de columnstore verplaatst en worden rijen die voor verwijdering zijn gemarkeerd fysiek verwijderd. Zie voor syntaxisvoorbeelden Voorbeelden - Columnstore herbouwen.

    Fooi

    Vanaf SQL Server 2016 (13.x) is het herbouwen van de columnstore-index meestal niet nodig omdat REORGANIZE de essentiële functies van een herbouwbewerking uitvoert als een onlinebewerking.

Gebruik indexherbouw om gegevensbeschadiging te herstellen.

Vóór SQL Server 2008 (10.0.x) kon u soms een niet-geclusterde rijopslagindex herbouwen om inconsistenties te corrigeren vanwege beschadiging van gegevens in de index.

U kunt dergelijke inconsistenties nog steeds herstellen in de niet-geclusterde index door een niet-geclusterde index offline opnieuw te bouwen. U kunt niet-geclusterde indexconsistentie echter niet herstellen door de index online opnieuw op te bouwen, omdat het online-herbouwmechanisme gebruikmaakt van de bestaande niet-geclusterde index als basis voor het opnieuw opbouwen en dus de inconsistentie overdraagt. Het offline opnieuw samenstellen van de index kan soms een scan van de geclusterde index (of heap) afdwingen en dus de inconsistente gegevens in de niet-geclusterde index vervangen door de gegevens uit de geclusterde index of heap.

Als u ervoor wilt zorgen dat de geclusterde index of heap wordt gebruikt als gegevensbron, verwijdert u de niet-geclusterde index en maakt u deze opnieuw in plaats van deze opnieuw te bouwen. Net als bij eerdere versies kunt u herstellen van inconsistenties door de betrokken gegevens te herstellen vanuit een back-up. Mogelijk kunt u echter inconsistenties in niet-geclusterde indexen herstellen door deze offline te herstellen of opnieuw te maken. Zie DBCC CHECKDB (Transact-SQL)voor meer informatie.

Automatisch index- en statistiekenbeheer

Gebruik oplossingen zoals Adaptive Index Defragmenteren om indexfragmentatie en statistiekenupdates automatisch te beheren voor een of meer databases. Met deze procedure wordt automatisch gekozen of u een index wilt herbouwen of opnieuw ordenen op basis van het fragmentatieniveau, onder andere parameters, en statistieken wilt bijwerken met een lineaire drempelwaarde.

Overwegingen die specifiek zijn voor het herbouwen en opnieuw ordenen van rijstore-indexen

De volgende scenario's zorgen ervoor dat alle niet-geclusterde indexen van een rijopslag in een tabel automatisch opnieuw worden opgebouwd:

  • Een geclusterde index maken in een tabel, inclusief het opnieuw maken van de geclusterde index met een andere sleutel met behulp van CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • Een geclusterde index verwijderen, waardoor de tabel wordt opgeslagen als een heap

In de volgende scenario's worden niet automatisch alle niet-geclusterde rijenopslagindexen in dezelfde tabel opnieuw opgebouwd:

  • Een geclusterde index opnieuw opbouwen
  • De geclusterde indexopslag wijzigen, zoals het toepassen van een partitioneringsschema of het verplaatsen van de geclusterde index naar een andere bestandsgroep

Belangrijk

Een index kan niet opnieuw worden ingedeeld of opnieuw worden opgebouwd als de bestandsgroep waarop deze zich bevindt offline of alleen-lezen is. Wanneer het trefwoord ALL is opgegeven en een of meer indexen zich in een offline- of alleen-lezen bestandsgroep bevinden, mislukt de instructie.

Terwijl een index opnieuw wordt opgebouwd, moet de fysieke media voldoende ruimte hebben om twee kopieën van de index op te slaan. Wanneer het opnieuw opbouwen is voltooid, verwijdert de database-engine de oorspronkelijke index.

Wanneer ALL is opgegeven met de ALTER INDEX ... REORGANIZE-instructie, worden geclusterde, niet-geclusterde en XML-indexen in de tabel opnieuw geordend.

Het herbouwen of opnieuw ordenen van kleine rijopslagindexen vermindert meestal geen fragmentatie. Tot en met SQL Server 2014 (12.x) wijst de SQL Server Database Engine ruimte toe met behulp van gemengde extenties. Daarom worden pagina's met kleine indexen soms opgeslagen in gemengde gebieden, waardoor dergelijke indexen impliciet worden gefragmenteerd. Gemengde gebieden worden gedeeld door maximaal acht objecten, waardoor de fragmentatie in een kleine index mogelijk niet wordt verminderd nadat deze opnieuw is georganiseerd of opnieuw is opgebouwd.

Overwegingen die specifiek zijn voor het herbouwen van een columnstore-index

Bij het herbouwen van een columnstore-index leest de database-engine alle gegevens uit de oorspronkelijke columnstore-index, inclusief de Delta Store. Het combineert gegevens in nieuwe rijgroepen en comprimeert alle rijgroepen in columnstore. De database-engine defragmenteert de columnstore door rijen die als verwijderd zijn gemarkeerd, fysiek te verwijderen.

Notitie

Vanaf SQL Server 2019 (15.x) wordt de tuple-mover geholpen door een samenvoegtaak op de achtergrond waarmee automatisch kleinere open Delta Store-rijgroepen worden gecomprimeerd die al enige tijd bestaan, zoals bepaald door een interne drempelwaarde, of worden gecomprimeerde rijgroepen samengevoegd waarbij een groot aantal rijen is verwijderd. Dit verbetert de kwaliteit van de columnstore-index in de loop van de tijd. Zie Columnstore-indexen: Overzichtvoor meer informatie over columnstore-termen en -concepten.

Een partitie opnieuw opbouwen in plaats van de hele tabel

Het opnieuw opbouwen van de hele tabel duurt lang als de index groot is en voldoende schijfruimte vereist om een extra kopie van de hele index op te slaan tijdens het opnieuw opbouwen.

Voor gepartitioneerde tabellen hoeft u de hele columnstore-index niet opnieuw op te bouwen als fragmentatie alleen aanwezig is in sommige partities, bijvoorbeeld in partities waarin UPDATE, DELETEof MERGE instructies een groot aantal rijen hebben beïnvloed.

Het opnieuw opbouwen van een partitie na het laden of wijzigen van gegevens zorgt ervoor dat alle gegevens worden opgeslagen in gecomprimeerde rijgroepen in columnstore. Wanneer het gegevensbelastingsproces gegevens invoegt in een partitie met behulp van batches die kleiner zijn dan 102.400 rijen, kan de partitie eindigen met meerdere geopende rijgroepen in Delta Store. Bij het heropbouwen worden alle rijen van de delta store verplaatst naar gecomprimeerde rijgroepen in de columnstore.

Overwegingen die specifiek zijn voor het opnieuw orden van een columnstore-index

Bij het opnieuw ordenen van een columnstore-index comprimeert de database-engine elke gesloten rijgroep in Delta Store in columnstore als een gecomprimeerde rijgroep. Vanaf SQL Server 2016 (13.x) en in Azure SQL Database voert de REORGANIZE opdracht de volgende extra defragmentatieoptimalisaties online uit:

  • Hiermee verwijdert u fysiek rijen uit een rijgroep wanneer 10% of meer van de rijen logisch zijn verwijderd. Als een gecomprimeerde rijgroep van 1 miljoen rijen bijvoorbeeld 100.000 rijen heeft verwijderd, verwijdert de Database Engine de verwijderde rijen en comprimeert de rijgroep met 900.000 rijen, waardoor de opslagvoetafdruk wordt verminderd.
  • Combineert een of meer gecomprimeerde rijgroepen om rijen per rijgroep te verhogen, tot maximaal 1.048.576 rijen. Als u bijvoorbeeld vijf batches van 102.400 rijen bulksgewijs invoegt, krijgt u vijf gecomprimeerde rijgroepen. Als u REORGANIZE uitvoert, worden deze rijgroepen samengevoegd in één gecomprimeerde rijgroep met 512.000 rijen. Hierbij wordt ervan uitgegaan dat er geen limieten voor woordenlijstgrootte of geheugen zijn.
  • De database-engine probeert rijgroepen te combineren waarin 10% of meer van de rijen zijn gemarkeerd als verwijderd met andere rijgroepen. Rijgroep 1 wordt bijvoorbeeld gecomprimeerd en heeft 500.000 rijen, terwijl rijgroep 21 wordt gecomprimeerd en 1.048.576 rijen bevat. Rowgroep 21 heeft 60% van zijn rijen gemarkeerd als verwijderd, waardoor er 409.830 rijen overblijven. De database-engine biedt de voorkeur aan het combineren van deze twee rijgroepen om een nieuwe rijgroep met 909.830 rijen te comprimeren.

Nadat gegevens zijn geladen, kunt u meerdere kleine rijgroepen in de delta store hebben. U kunt ALTER INDEX REORGANIZE gebruiken om deze rijgroepen af te dwingen in columnstore en vervolgens kleinere gecomprimeerde rijgroepen te combineren in grotere gecomprimeerde rijgroepen. Bij de herindelingsbewerking worden rijen die als verwijderd zijn gemarkeerd, ook uit de columnstore verwijderd.

Notitie

Door een columnstore-index opnieuw te organiseren met Management Studio worden de gecomprimeerde rijgroepen samengevoegd, maar worden niet alle rijgroepen in de columnstore gecomprimeerd. Gesloten rijgroepen worden gecomprimeerd, maar open rijgroepen worden niet gecomprimeerd in columnstore. Als u alle rijgroepen geforceerd wilt comprimeren, gebruikt u het Transact-SQL voorbeeld met COMPRESS_ALL_ROW_GROUPS = ON.

Wat u moet overwegen voordat u indexonderhoud uitvoert

Indexonderhoud, uitgevoerd door een index te herstructureren of opnieuw te bouwen, is resource-intensief. Dit veroorzaakt een aanzienlijke toename van het CPU-gebruik, geheugengebruik en I/O van opslag. De voordelen die het biedt, variëren echter, afhankelijk van de databaseworkload en andere factoren, van cruciaal belang tot verwaarloosbaar.

Om onnodig resourcegebruik te voorkomen, vermijdt u ongediscriminaliseerd indexonderhoud. In plaats daarvan moeten prestatievoordelen van indexonderhoud empirisch worden bepaald voor elke workload met behulp van de aanbevolen strategieen worden afgewogen tegen de resourcekosten en de impact van de workload die nodig is om deze voordelen te bereiken.

De kans om prestatievoordelen te ervaren is groter als de index sterk gefragmenteerd is of als de paginadichtheid laag is. Dit zijn echter niet de enige aandachtspunten. Factoren zoals querypatronen (transactieverwerking versus analyse en rapportage), gedrag van het opslagsubsysteem, beschikbare geheugen- en database-engineverbeteringen spelen allemaal een rol.

Belangrijk

Beslissingen voor indexonderhoud moeten worden genomen nadat u rekening moet houden met meerdere factoren in de specifieke context van elke workload, met inbegrip van de resourcekosten van onderhoud. Ze mogen niet alleen worden gebaseerd op vaste fragmentatie- of paginadichtheidsdrempels.

Een positief neveneffect van het opnieuw opbouwen van indexen

Klanten zien vaak prestatieverbeteringen na het opnieuw opbouwen van indexen. In veel gevallen zijn deze verbeteringen echter niet gerelateerd aan het verminderen van fragmentatie of het verhogen van de paginadichtheid.

Het opnieuw opbouwen van een index heeft een belangrijk voordeel: het werkt statistieken bij op sleutelkolommen van de index door alle rijen in de index te scannen. Dit is het equivalent van het uitvoeren van UPDATE STATISTICS ... WITH FULLSCAN, waardoor statistieken actueel zijn en soms de kwaliteit ervan worden verbeterd in vergelijking met de standaardupdate van de steekproefstatistieken. Wanneer statistieken worden bijgewerkt, worden queryplannen die ernaar verwijzen, opnieuw gecompileerd. Als het vorige plan voor een query niet optimaal was vanwege verouderde statistieken, onvoldoende steekproefverhouding voor statistieken of om andere redenen, presteert het opnieuw gecompileerde plan vaak beter.

Klanten geven deze verbetering vaak ten onrechte toe aan het herbouwen van de index, waardoor deze het gevolg is van verminderde fragmentatie en een verhoogde paginadichtheid. In werkelijkheid kan hetzelfde voordeel vaak worden bereikt tegen veel goedkopere resourcekosten door statistieken bij te werken in plaats van indexen opnieuw te bouwen.

Fooi

Resourcekosten voor het bijwerken van statistieken zijn klein in vergelijking met het opnieuw samenstellen van indexen en de bewerking wordt vaak in minuten voltooid. Het opnieuw opbouwen van indexen kan uren duren.

Strategie voor indexonderhoud

Microsoft raadt klanten aan de volgende strategie voor indexonderhoud te overwegen en te gebruiken:

  • Ga er niet van uit dat indexonderhoud uw werkbelasting altijd aanzienlijk zal verbeteren.
  • Meet de specifieke impact van het opnieuw ordenen of herbouwen van indexen voor queryprestaties in uw workload. Query Store is een goede manier om de prestaties 'vóór onderhoud' en 'na onderhoud' te meten met behulp van de A/B-testtechniek.
  • Als u merkt dat het herbouwen van indexen de prestaties verbetert, kunt u deze vervangen door het bijwerken van statistieken. Dit kan resulteren in een vergelijkbare verbetering. In dat geval hoeft u mogelijk niet zo vaak of helemaal geen indexen opnieuw op te bouwen en in plaats daarvan periodieke statistiekenupdates uit te voeren. Voor sommige statistieken moet u mogelijk de steekproefverhouding verhogen met behulp van de WITH SAMPLE ... PERCENT- of WITH FULLSCAN-componenten (dit is niet gebruikelijk).
  • Bewaak indexfragmentatie en paginadichtheid in de loop van de tijd om te zien of er een correlatie is tussen deze waarden die omhoog of omlaag gaan en de prestaties van query's. Als een hogere fragmentatie of lagere paginadichtheid de prestaties onaanvaardbaar verlaagt, kunt u indexen opnieuw ordenen of herbouwen. Het is vaak voldoende om specifieke indexen die door query's worden gebruikt en een verminderde prestatie hebben, alleen opnieuw te ordenen of opnieuw op te bouwen. Dit voorkomt een hogere resourcekosten voor het onderhouden van elke index in de database.
  • Door een correlatie tussen fragmentatie/paginadichtheid en prestaties tot stand te brengen, kunt u ook de frequentie van indexonderhoud bepalen. Neem niet aan dat onderhoud volgens een vast schema moet worden uitgevoerd. Een betere strategie is het bewaken van fragmentatie en paginadichtheid en het uitvoeren van indexonderhoud indien nodig voordat de prestaties onaanvaardbaar afnemen.
  • Als u hebt vastgesteld dat indexonderhoud nodig is en de resourcekosten acceptabel zijn, voert u, indien mogelijk, onderhoud uit tijdens lage resourcegebruikstijden.
  • Test periodiek, omdat patronen voor resourcegebruik na verloop van tijd kunnen veranderen.

Indexonderhoud in Azure SQL Database en Azure SQL Managed Instance

Naast de bovenstaande overwegingen en strategie is het in Azure SQL Database en Azure SQL Managed Instance vooral belangrijk om rekening te houden met de kosten en voordelen van indexonderhoud. Klanten moeten dit alleen uitvoeren wanneer er een aangetoonde behoefte is en rekening houden met de volgende punten.

  • Azure SQL Database en Azure SQL Managed Instance implementeren resourcebeheer om afhankelijk te stellen van CPU-, geheugen- en I/O-verbruik volgens de ingerichte prijscategorie. Deze grenzen zijn van toepassing op alle gebruikersworkloads, inclusief indexonderhoud. Als het cumulatieve hulpbronnenverbruik door alle workloads de grenzen van de resources nadert, kan de herbouw- of reorganisatieoperatie de prestaties van andere workloads verminderen vanwege resource-contentie. Bulksgewijs laden van gegevens kan bijvoorbeeld langzamer worden omdat de I/O van het transactielogboek 100% is vanwege een gelijktijdige herbouw van de index. In Azure SQL Managed Instance kan deze impact worden verminderd door indexonderhoud uit te voeren in een afzonderlijke Resource Governor-workloadgroep met beperkte resourcetoewijzing, ten koste van het verlengen van de duur van het indexonderhoud.
  • Voor kostenbesparingen richten klanten vaak databases, elastische pools en beheerde exemplaren in met minimale resourcehoofdruimte. De prijscategorie is gekozen om voldoende te zijn voor toepassingsworkloads. Om te voorzien in een aanzienlijke toename van het resourcegebruik vanwege indexonderhoud zonder de prestaties van toepassingen te verlagen, moeten klanten mogelijk meer resources inrichten en kosten verhogen, zonder de prestaties van toepassingen te verbeteren.
  • In elastische pools worden resources gedeeld in alle databases in een pool. Zelfs als een bepaalde database niet actief is, kan het uitvoeren van indexonderhoud op die database invloed hebben op toepassingsworkloads die gelijktijdig worden uitgevoerd in andere databases in dezelfde pool. Zie Resourcebeheer in dichte elastische poolsvoor meer informatie.
  • Voor de meeste typen opslag die wordt gebruikt in Azure SQL Database en Azure SQL Managed Instance, is er geen verschil in prestaties tussen sequentiële I/O en willekeurige I/O. Dit vermindert de impact van indexfragmentatie op queryprestaties.
  • Wanneer u uitschalen lezen of geo-replicatie replica's gebruikt, neemt de latentie van gegevens op replica's vaak toe terwijl indexonderhoud wordt uitgevoerd op de primaire replica. Als een geo-replica is ingericht met onvoldoende resources om een toename van het genereren van transactielogboeken te ondersteunen die wordt veroorzaakt door indexonderhoud, kan deze ver achterblijven bij het primaire, waardoor het systeem het opnieuw kan verzenden. Hierdoor is de replica niet beschikbaar totdat het hersaaien is voltooid. Bovendien kunnen in de premium- en bedrijfskritieke serviceniveaus de replica's die voor hoge beschikbaarheid worden gebruikt, tijdens het indexonderhoud veel achterlopen op de primaire. Als een failover is vereist tijdens of kort na het indexonderhoud, kan het langer duren dan verwacht.
  • Als een index opnieuw opbouwen wordt uitgevoerd op de primaire replica en een langlopende query tegelijkertijd wordt uitgevoerd op een leesbare replica, kan de query automatisch worden beëindigd om te voorkomen dat de redo-thread op de replica wordt geblokkeerd.

Er zijn specifieke, maar ongebruikelijke scenario's waarin eenmalig of periodiek indexonderhoud nodig is in Azure SQL Database en Azure SQL Managed Instance:

  • Als u de paginadichtheid wilt vergroten en de gebruikte ruimte in de database wilt verminderen, blijft u dus binnen de groottelimiet van de prijscategorie. Dit voorkomt dat u omhoog moet schalen naar een hogere prijscategorie met een hogere groottelimiet.
  • Als het nodig is om bestanden te verkleinen, herbouwen of opnieuw ordenen van indexen voordat bestanden worden verkleind, neemt de paginadichtheid toe. Hierdoor wordt de verkleiningsbewerking sneller, omdat deze minder pagina's moet verplaatsen. Ga voor meer informatie naar:

Tip

Als u hebt vastgesteld dat indexonderhoud nodig is voor uw Azure SQL Database- en Azure SQL Managed Instance-workloads, moet u indexen opnieuw ordenen of online opnieuw samenstellen van indexen gebruiken. Hierdoor hebben queryworkloads toegang tot tabellen terwijl indexen opnieuw worden opgebouwd.

Bovendien kunt u voorkomen dat de bewerking opnieuw wordt opgestart vanaf het begin als deze wordt onderbroken door een geplande of niet-geplande databasefailover. Het gebruik van hervatbare indexbewerkingen is met name belangrijk wanneer indexen groot zijn.

Tip

Offlineindexbewerkingen worden doorgaans sneller uitgevoerd dan onlinebewerkingen. Ze moeten worden gebruikt wanneer tabellen tijdens de bewerking niet worden geopend door query's, bijvoorbeeld nadat gegevens in faseringstabellen zijn geladen als onderdeel van een sequentiële ETL-proces.

Beperkingen en beperkingen

Rowstore-indexen met meer dan 128 gebieden worden opnieuw opgebouwd in twee afzonderlijke fasen: logisch en fysiek. In de logische fase worden de bestaande toewijzingseenheden die door de index worden gebruikt gemarkeerd voor deallocatie, worden de gegevensrijen gekopieerd en gesorteerd en vervolgens verplaatst naar nieuwe toewijzingseenheden die zijn gemaakt om de herbouwde index op te slaan. Tijdens de fysieke fase worden de toewijzingseenheden die eerder zijn gemarkeerd voor deallocatie fysiek verwijderd in korte transacties, die op de achtergrond plaatsvinden en waarvoor niet veel vergrendelingen nodig zijn. Zie Pages and Extents Architecture Guidevoor meer informatie over toewijzingseenheden.

De ALTER INDEX REORGANIZE-instructie vereist dat het gegevensbestand met de index ruimte beschikbaar heeft, omdat de bewerking alleen tijdelijke werkpagina's in hetzelfde bestand kan toewijzen, niet in een ander bestand binnen dezelfde bestandsgroep. Hoewel de bestandsgroep vrije ruimte heeft, kan de gebruiker nog steeds fout 1105 tegenkomen: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup tijdens de herorganisatiebewerking als een gegevensbestand niet meer ruimte heeft.

Een index kan niet opnieuw worden geordend wanneer ALLOW_PAGE_LOCKS is ingesteld op UIT.

Tot SQL Server 2017 (14.x) is het herbouwen van een geclusterde columnstore-index een offlinebewerking. De database-engine moet een exclusieve vergrendeling op de tabel of partitie verkrijgen terwijl de herbouw plaatsvindt. De gegevens zijn offline en niet beschikbaar tijdens het opnieuw opbouwen, zelfs wanneer u NOLOCK, read-committed snapshot isolation (RCSI) of snapshot-isolatie gebruikt. Vanaf SQL Server 2019 (15.x) kan een geclusterde columnstore-index opnieuw worden opgebouwd met behulp van de optie ONLINE = ON.

Waarschuwing

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. Microsoft raadt aan om alleen uitgelijnde indexen te gebruiken wanneer het aantal partities groter is dan 1000.

Beperkingen voor statistieken

  • Wanneer een index wordt gemaakt of opnieuw opgebouwd, worden statistieken gemaakt of bijgewerkt door alle rijen in de tabel te scannen, wat gelijk is aan het gebruik van de FULLSCAN clausule in CREATE STATISTICS of UPDATE STATISTICS. Vanaf SQL Server 2012 (11.x), wanneer een gepartitioneerde index wordt gemaakt of opnieuw wordt opgebouwd, worden statistieken echter niet gemaakt of bijgewerkt door alle rijen in de tabel te scannen. In plaats daarvan wordt de standaardsamplingverhouding gebruikt. Als u statistieken wilt maken of bijwerken voor gepartitioneerde indexen door alle rijen in de tabel te scannen, gebruikt u CREATE STATISTICS of UPDATE STATISTICS met de component FULLSCAN.
  • Op dezelfde manier worden statistieken gemaakt of bijgewerkt met de standaardsamplingsverhouding wanneer het aanmaken of opnieuw opbouwen van de index hervatbaar is. Als statistieken zijn gemaakt of voor het laatst zijn bijgewerkt met de PERSIST_SAMPLE_PERCENT-component die is ingesteld op ON, gebruiken hervatbare indexbewerkingen de persistente steekproefverhouding om statistieken te maken of bij te werken.
  • Wanneer een index opnieuw is ingedeeld, worden statistieken niet bijgewerkt.

Voorbeelden

Controleer de fragmentatie en paginadichtheid van een rowstore-index met behulp van Transact-SQL

In het volgende voorbeeld wordt de gemiddelde fragmentatie en paginadichtheid bepaald voor alle rowstore-indexen in de huidige database. De SAMPLED-modus wordt gebruikt om snel bruikbare resultaten te retourneren. Gebruik de DETAILED-modus voor nauwkeurigere resultaten. Hiervoor moeten alle indexpagina's worden gescand en kan het lang duren.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

De vorige instructie retourneert een resultatenset die vergelijkbaar is met de volgende:

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

Zie sys.dm_db_index_physical_statsvoor meer informatie.

Controleer de fragmentatie van een columnstore-index met behulp van Transact-SQL

In het volgende voorbeeld wordt de gemiddelde fragmentatie voor alle columnstore-indexen met gecomprimeerde rijgroepen in de huidige database bepaald.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

De vorige instructie retourneert een resultatenset die vergelijkbaar is met de volgende:

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Indexen onderhouden met SQL Server Management Studio

Een index opnieuw orden of herbouwen

  1. Vouw in Objectverkennerde database uit die de tabel bevat waarop u een index wilt herorganiseren.
  2. Vouw de map Tabellen uit.
  3. Vouw de tabel uit waarop u een index wilt herorganiseren.
  4. Vouw de map Indexen uit.
  5. Klik met de rechtermuisknop op de index die u wilt herorganiseren en selecteer opnieuw organiseren.
  6. Controleer in het dialoogvenster Indexen opnieuw ordenen of de juiste index zich in de Indexen die opnieuw moeten worden geordend lijst bevindt en selecteer OK.
  7. Schakel het selectievakje Grote objectkolomgegevens comprimeren selectievakje in om op te geven dat alle pagina's met grote objectgegevens (LOB) ook worden gecomprimeerd.
  8. Selecteer OK.

Alle indexen in een tabel opnieuw ordenen

  1. Vouw in Objectverkennerde database uit die de tabel bevat waarop u de indexen opnieuw wilt ordenen.
  2. Vouw de map Tabellen uit.
  3. Vouw de tabel uit waarop u de indexen opnieuw wilt ordenen.
  4. Klik met de rechtermuisknop op de map Indexen en selecteer Alleopnieuw ordenen.
  5. Controleer in het dialoogvenster Indexen opnieuw ordenen of de juiste indexen zich in de Indexen bevinden omopnieuw te ordenen. Als u een index wilt verwijderen uit de Indexen die opnieuw geordend moeten worden raster, selecteert u de index en drukt u op de toets Verwijderen.
  6. Schakel het selectievakje Grote objectkolomgegevens comprimeren selectievakje in om op te geven dat alle pagina's met grote objectgegevens (LOB) ook worden gecomprimeerd.
  7. Selecteer OK.

Indexen onderhouden met behulp van Transact-SQL

Notitie

Voor meer voorbeelden over het gebruik van Transact-SQL om indexen opnieuw op te bouwen of te reorganiseren, zie ALTER INDEX Examples - Rowstore Indexes en ALTER INDEX Examples - Columnstore Indexes.

Een index herordenen

In het volgende voorbeeld wordt de IX_Employee_OrganizationalLevel_OrganizationalNode-index in de HumanResources.Employee tabel in de AdventureWorks2022-database opnieuw georganiseert.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

In het volgende voorbeeld wordt de columnstore-index van IndFactResellerSalesXL_CCI in de dbo.FactResellerSalesXL_CCI tabel in de AdventureWorksDW2022-database opnieuw georganiseert. Met deze opdracht worden alle gesloten en geopende rijgroepen gedwongen naar kolommenopslag.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Alle indexen in een tabel opnieuw ordenen

In het volgende voorbeeld worden alle indexen in de HumanResources.Employee tabel in de AdventureWorks2022-database opnieuw georganiseert.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Een index opnieuw samenstellen

In het volgende voorbeeld wordt één index in de Employee tabel in de AdventureWorks2022-database opnieuw opgebouwd.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Alle indexen in een tabel opnieuw samenstellen

In het volgende voorbeeld worden alle indexen die zijn gekoppeld aan de tabel in de AdventureWorks2022-database opnieuw opgebouwd met behulp van het trefwoord ALL. Er worden drie opties opgegeven.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

Zie ALTER INDEXvoor meer informatie.

Verwante inhoud