Dela via

Optimera indexunderhåll för att förbättra frågeprestanda och minska resursförbrukningen

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)SQL Database i Microsoft Fabric

Den här artikeln hjälper dig att bestämma när och hur du utför indexunderhåll. Den beskriver begrepp som indexfragmentering och siddensitet och deras inverkan på frågeprestanda och resursförbrukning. Den beskriver indexunderhållsmetoder, omorganisera ett index och återskapa ett indexoch föreslår en strategi för indexunderhåll som balanserar potentiella prestandaförbättringar mot resursförbrukning som krävs för underhåll.

Not

Den här artikeln gäller inte för en dedikerad SQL-pool i Azure Synapse Analytics. Information om indexunderhåll för en dedikerad SQL-pool i Azure Synapse Analytics finns i Indexering av dedikerade SQL-pooltabeller i Azure Synapse Analytics.

Begrepp: indexfragmentering och sidtäthet

Vad är indexfragmentering och hur det påverkar prestanda:

  • I B-trädindex (radlager) finns fragmentering när index har sidor där den logiska ordningen i indexet, baserat på indexets nyckelvärden, inte matchar indexsidornas fysiska ordning.

    Not

    I dokumentationen används termen B-träd vanligtvis som referens till index. I radlagringsindex implementerar databasmotorn ett B+-träd. Detta gäller inte för kolumnlagringsindex eller index i minnesoptimerade tabeller. Mer information finns i arkitekturen och designguiden för SQL Server och Azure SQL-index.

  • Databasmotorn ändrar automatiskt index när infognings-, uppdaterings- eller borttagningsåtgärder görs till underliggande data. Till exempel kan tillägg av rader i en tabell göra att befintliga sidor i radlagringsindex delas, vilket ger utrymme för infogning av nya rader. Med tiden kan dessa ändringar göra att data i indexet blir utspridda i databasen (fragmenterade).

  • För frågor som läser många sidor med hjälp av full- eller intervallindexgenomsökningar kan kraftigt fragmenterade index försämra frågeprestanda när ytterligare I/O krävs för att läsa data. I stället för ett litet antal stora I/O-begäranden skulle frågan kräva ett större antal små I/O-begäranden för att läsa samma mängd data.

  • När lagringsundersystemet ger bättre sekventiella I/O-prestanda än slumpmässiga I/O-prestanda kan indexfragmentering försämra prestanda eftersom mer slumpmässig I/O krävs för att läsa fragmenterade index.

Vad är sidtäthet (även kallat sidfullhet) och hur det påverkar prestanda:

  • Varje sida i databasen kan innehålla ett variabelt antal rader. Om rader tar allt utrymme på en sida är sidtätheten 100%. Om en sida är tom är sidtätheten 0%. Om en sida med 100% densitet delas på två sidor för att rymma en ny rad är densiteten för de två nya sidorna cirka 50%.
  • När sidtätheten är låg krävs fler sidor för att lagra samma mängd data. Det innebär att mer I/O krävs för att läsa och skriva dessa data, och mer minne krävs för att cachelagra dessa data. När minnet är begränsat cachelagras färre sidor som krävs av en fråga, vilket orsakar ännu mer disk-I/O. Låg sidtäthet påverkar därför prestandan negativt.
  • När databasmotorn lägger till rader på en sida när index skapas, återskapas eller omorganiseras fylls inte sidan fullständigt om fyllningsfaktor för indexet är inställt på ett annat värde än 100 (eller 0, vilket är likvärdigt i den här kontexten). Detta orsakar lägre sidhustäthet och ökar också I/O-omkostnaderna, vilket påverkar prestanda negativt.
  • Låg sidtäthet kan öka antalet mellanliggande B-trädnivåer. Detta ökar cpu- och I/O-kostnaden måttligt för att hitta lövnivåsidor i indexgenomsökningar och -sökningar.
  • När Frågeoptimeraren kompilerar en frågeplan tar den hänsyn till kostnaden för I/O som krävs för att läsa de data som krävs av frågan. Med låg sidtäthet finns det fler sidor att läsa, och därför är kostnaden för I/O högre. Detta kan påverka val av frågeplan. När sidtätheten till exempel minskar över tid på grund av siddelningar kan optimeraren skapa en annan plan för samma fråga, vilket innebär en annan prestanda- och resursförbrukningsprofil.

Tips

I många arbetsbelastningar resulterar ökad siddensitet i en större positiv prestandapåverkan än att minska fragmenteringen.

För att undvika att sidtätheten sänks på ett onödigt sätt rekommenderar Microsoft inte att du ställer in fyllningsfaktorn på andra värden än 100 eller 0, förutom i vissa fall för index som upplever ett stort antal siddelningar, till exempel ofta ändrade index med inledande kolumner som innehåller icke-sekventiella GUID-värden.

Mäta indexfragmentering och sidtäthet

Både fragmentering och siddensitet är några av de faktorer som bör beaktas när du bestämmer om indexunderhåll ska utföras och vilken underhållsmetod som ska användas.

Fragmentering definieras på olika sätt för radlagringsindex och kolumnarkivsindex. För radlagringsindex sys.dm_db_index_physical_stats() kan du fastställa fragmentering och sidtäthet i ett specifikt index, alla index på en tabell eller i en indexerad vy, alla index i en databas eller alla index i alla databaser. För partitionerade index tillhandahåller sys.dm_db_index_physical_stats() den här informationen för varje partition.

Resultatuppsättningen som returneras av sys.dm_db_index_physical_stats innehåller följande kolumner:

Spalt Beskrivning
avg_fragmentation_in_percent Logisk fragmentering (sidor som inte ligger i ordning i indexet).
avg_page_space_used_in_percent Genomsnittlig sidtäthet.

För komprimerade radgrupper i kolumnlagringsindex definieras fragmentering som förhållandet mellan borttagna rader och totalt antal rader uttryckt i procent. sys.dm_db_column_store_row_group_physical_stats kan du fastställa antalet totala och borttagna rader per radgrupp i ett specifikt index, alla index i en tabell eller alla index i en databas.

Resultatuppsättningen som returneras av sys.dm_db_column_store_row_group_physical_stats innehåller följande kolumner:

Spalt Beskrivning
total_rows Antal rader som lagras fysiskt i radgruppen. För komprimerade radgrupper innehåller detta de rader som har markerats som borttagna.
deleted_rows Antal rader som lagras fysiskt i en komprimerad radgrupp som har markerats för borttagning. 0 för radgrupper som finns i delta store.

Komprimerad radgruppsfragmentering i ett kolumnlagringsindex kan beräknas med hjälp av den här formeln:

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

Tips

För både rowstore- och columnstore-index, granska index- eller Heap-fragmentering samt sidtäthet efter att ett stort antal rader har tagits bort eller uppdaterats. För högar, om det finns frekventa uppdateringar, bör fragmenteringen granskas regelbundet för att undvika spridning av omdirigeringsposter. Mer information om heaps finns i Heaps (tabeller utan klustrade index).

Se Exempel för exempelförfrågningar för att fastställa fragmentering och sidtäthet.

Indexunderhållsmetoder: omorganisera och återskapa

Du kan minska indexfragmenteringen och öka sidtätheten med någon av följande metoder:

  • Omorganisera ett index
  • Återskapa ett index

Notera

För partitionerade index kan du använda någon av följande metoder på alla partitioner eller en enda partition av ett index.

Omorganisera ett index

Omorganiseringen av ett index är mindre resurskrävande än att återskapa ett index. Därför bör det vara din föredragna indexunderhållsmetod, såvida det inte finns en specifik anledning att använda återskapande av index. Omorganisering är alltid en onlineåtgärd. Det innebär att långsiktiga lås på objektnivå inte sparas och att frågor eller uppdateringar av den underliggande tabellen kan fortsätta under den ALTER INDEX ... REORGANIZE åtgärden.

  • För radlagringsindexdefragmenterar databasmotorn endast lövnivån för grupperade och icke-grupperade index i tabeller och vyer genom att fysiskt ordna om bladnivåsidorna så att de matchar lövnodernas logiska ordning (vänster till höger). Omorganisering komprimerar också indexsidorna så att sidtätheten blir lika med fyllningsfaktorn för indexet. Om du vill visa fyllningsfaktorinställningen använder du sys.indexes. Syntaxexempel finns i Exempel – Omorganisera radlagring.
  • När du använder kolumnlagringsindexkan deltaarkivet få flera små radgrupper när du har infogat, uppdaterat och raderat data över tid. Omorganiseringen av ett kolumnlagerindex tvingar deltalager till komprimerade radgrupper i kolumnlagret och kombinerar mindre komprimerade radgrupper till större radgrupper. Den fysiska omorganiseringsåtgärden tar även bort rader som har markerats som borttagna i kolumnbutiken. Om du omorganiserar ett kolumnlagringsindex kan det krävas ytterligare CPU-resurser för att komprimera data. När operationen körs kan prestandan avta. Men när data har komprimerats förbättras frågeprestandan. Syntaxexempel finns i Exempel – Omorganisera kolumnlager.

Obs

Från och med SQL Server 2019 (15.x), Azure SQL Database och Azure SQL Managed Instance får tuppelflyttaren hjälp av en bakgrundssammanslagningsaktivitet som automatiskt komprimerar mindre öppna deltaradgrupper som har funnits under en viss tid, vilket bestäms av ett internt tröskelvärde, eller sammanfogar komprimerade radgrupper där ett stort antal rader har tagits bort. Detta förbättrar kolumnlagringsindexets kvalitet över tid. I de flesta fall avvisas behovet av att utfärda ALTER INDEX ... REORGANIZE kommandon.

Tips

Om du avbryter en omorganiseringsåtgärd, eller om den avbryts på annat sätt, sparas de framsteg som den gjorde till den punkten i databasen. Om du vill ordna om stora index kan åtgärden startas och stoppas flera gånger tills den är klar.

Återskapa ett index

Att återskapa ett index innebär att indexet raderas och återskapas. Beroende på typen av index och databasmotorversionen kan en återskapande åtgärd utföras offline eller online. Det tar vanligtvis mindre tid att återskapa ett offlineindex än en online-återskapning, men det håller lås på objektnivå under hela återskapandeåtgärden, vilket blockerar åtkomst av tabellen eller vyn av frågeställningar.

Ombyggnad av onlineindex kräver inte lås på objektnivå förrän åtgärden har slutförts, när ett lås måste hållas under en kort tid för att slutföra återskapandet. Beroende på vilken version av databasmotorn som används kan en online-ombyggnad av index startas som en återupptagbar åtgärd. En återupptagbar indexåteruppbyggnad kan pausas, med vilket de framsteg som gjorts bevaras till den punkten. En återupptagningsbar ombyggnadsåtgärd kan återupptas efter att ha pausats eller avbrutits, eller avbrutits om det blir onödigt att slutföra ombyggnaden.

För Transact-SQL-syntax, se ALTER INDEX REBUILD. Mer information om ombyggnad av index online finns i Utför indexoperationer online.

Not

När ett index återskapas online måste varje ändring av data i indexerade kolumner uppdatera ytterligare en kopia av indexet. Detta kan resultera i en mindre prestandaförsämring av datamodifieringsinstruktioner under online-återskapande.

Om en onlineåtertagningsbar indexåtgärd pausas kvarstår prestandapåverkan tills den återupptabara åtgärden antingen slutförs eller avbryts. Om du inte tänker slutföra en återupptabar indexåtgärd avbryter du den i stället för att pausa den.

Tips

Beroende på tillgängliga resurser och arbetsbelastningsmönster kan om du anger ett högre värde än standardvärdet MAXDOP i ALTER INDEX REBUILD-instruktionen förkorta varaktigheten för återskapande på bekostnad av högre CPU-användning.

  • För radlagringsindextar återskapande bort fragmentering på alla nivåer i indexet och komprimerar sidor baserat på den angivna eller aktuella fyllningsfaktorn. När ALL anges tas alla index i tabellen bort och återskapas i en enda åtgärd. När index med 128 eller fler utsträckningar återskapas, skjuter databasmotorn upp siddeallokeringar och förvärvar de associerade låsen tills återskapandet har slutförts. Syntaxexempel finns i Exempel – Omarbeta Rowstore.

  • För kolumnlagringsindex tar återskapande bort fragmentering, flyttar alla deltalagringsrader till kolumnlagringsindexet och tar fysiskt bort rader som har markerats för borttagning. Syntaxexempel finns i Exempel: Columnstore-återuppbyggnad.

    Tips

    Från och med SQL Server 2016 (13.x) är det vanligtvis inte nödvändigt att återskapa kolumnlagringsindex eftersom REORGANIZE utför de väsentliga delarna av en återskapning som en onlineåtgärd.

Använd indexåterskapning för att återställa från datakorruption

Före SQL Server 2008 (10.0.x) kunde du ibland återskapa ett icke-klustrat index för radlagring för att korrigera inkonsekvenser på grund av datakorruption i indexet.

Du kan fortfarande reparera sådana inkonsekvenser i det icke-grupperade indexet genom att återskapa ett icke-grupperat index offline. Du kan dock inte reparera icke-illustrerade indexinkonsekvenser genom att återskapa indexet online, eftersom mekanismen för återskapande online använder det befintliga icke-illustrerade indexet som grund för återskapande och därmed för över inkonsekvensen. Om du återskapar indexet offline kan det ibland tvinga fram en genomsökning av det klustrade indexet (eller heap) och därför ersätta inkonsekventa data i det icke-grupperade indexet med data från det klustrade indexet eller heapen.

För att säkerställa att det klustrade indexet eller heapen används som datakälla tar du bort och återskapar det icke-klustrade indexet i stället för att bygga om det. Precis som med tidigare versioner kan du återställa från inkonsekvenser genom att återställa berörda data från en säkerhetskopia. Du kanske dock kan reparera ickeklustrade indexinkonsekvenser genom att återskapa det offline eller skapa det på nytt. Mer information finns i DBCC CHECKDB (Transact-SQL).

Automatisk index- och statistikhantering

Använd lösningar som Adaptive Index Defrag för att automatiskt hantera uppdateringar av indexfragmentering och statistik för en eller flera databaser. Den här proceduren väljer automatiskt om du vill återskapa eller omorganisera ett index enligt dess fragmenteringsnivå, bland andra parametrar, och uppdatera statistik med ett linjärt tröskelvärde.

Överväganden som är specifika för att återskapa och omorganisera radlagringsindex

Följande scenarier gör att alla icke-klustrade radlagrade index i en tabell återuppbyggs automatiskt:

  • Skapa ett klustrat index i en tabell, inklusive återskapa det klustrade indexet med en annan nyckel med hjälp av CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • Ta bort ett grupperat index, vilket gör att tabellen lagras som en heap

Följande scenarier återskapar inte automatiskt alla icke-klustrade rowstore-index på samma tabell:

  • Återskapa ett klustrat index
  • Ändra klustrad indexlagring, till exempel att tillämpa ett partitioneringsschema eller flytta det klustrade indexet till en annan filgrupp

Viktig

Det går inte att ordna om eller återskapa ett index om den filgrupp där det finns är offline eller skrivskyddad. När nyckelordet ALL har angetts och ett eller flera index finns i en offline- eller skrivskyddad filgrupp misslyckas instruktionen.

När ett index återskapas måste det fysiska mediet ha tillräckligt med utrymme för att lagra två kopior av indexet. När återskapande är klar tar databasmotorn bort det ursprungliga indexet.

När ALL anges med ALTER INDEX ... REORGANIZE-instruktionen omorganiseras klustrade, icke-klustrade och XML-index i tabellen.

Om du återskapar eller omorganiserar små radlagringsindex minskar vanligtvis inte fragmenteringen. Fram till och med SQL Server 2014 (12.x) allokerar SQL Server Database Engine utrymme med hjälp av blandade utbredningar. Därför lagras sidor med små index ibland i blandade omfattningar, vilket implicit gör sådana index fragmenterade. Blandade omfattningar delas av upp till åtta objekt, så fragmenteringen i ett litet index kanske inte minskas efter omorganisering eller återskapande.

Överväganden som är specifika för att återskapa ett kolumnlagringsindex

När du återskapar ett columnstore-index läser databasmotorn alla data från det ursprungliga kolumnlagringsindexet, inklusive deltalagret. Den kombinerar data till nya radgrupper och komprimerar alla radgrupper till kolumnlagret. Databasmotorn defragmenterar kolumnarkivet genom att fysiskt ta bort rader som har markerats som borttagna.

Not

Från och med SQL Server 2019 (15.x) får tuppelflyttaren hjälp av en bakgrundssammanslagningsaktivitet som automatiskt komprimerar mindre öppna deltalagringsradgrupper som har funnits under en tid, vilket bestäms av ett internt tröskelvärde, eller sammanfogar komprimerade radgrupper där ett stort antal rader har tagits bort. Detta förbättrar kolumnlagringsindexkvaliteten över tid. Mer information om columnstore-termer och -koncept finns i Columnstore-index: Översikt.

Återskapa en partition i stället för hela tabellen

Det tar lång tid att återskapa hela tabellen om indexet är stort och kräver tillräckligt med diskutrymme för att lagra ytterligare en kopia av hela indexet under återskapande.

För partitionerade tabeller behöver du inte återskapa hela kolumnlagringsindexet om fragmentering endast finns i vissa partitioner, till exempel i partitioner där UPDATE, DELETEeller MERGE-instruktioner har påverkat ett stort antal rader.

Om du återskapar en partition efter inläsning eller ändring av data ser du till att alla data lagras i komprimerade radgrupper i kolumnarkivet. När datainläsningsprocessen infogar data i en partition med batchar som är mindre än 102 400 rader kan partitionen få flera öppna radgrupper i deltaarkivet. Om du återskapar flyttas alla deltalagringsrader till komprimerade radgrupper i kolumnarkivet.

Överväganden som är specifika för att omorganisera ett kolumnlagringsindex

När du omorganiserar ett kolumnlagringsindex komprimerar databasmotorn varje stängd radgrupp i deltaarkivet till kolumnarkivet som en komprimerad radgrupp. Från och med SQL Server 2016 (13.x) och i Azure SQL Database utför kommandot REORGANIZE följande ytterligare defragmenteringsoptimeringar online:

  • Tar fysiskt bort rader från en radgrupp när 10% eller fler av raderna har tagits bort logiskt. Om till exempel en komprimerad radgrupp på 1 miljon rader har 100 000 rader borttagna, tar databasmotorn bort de borttagna raderna och komprimerar radgruppen igen med 900 000 rader, vilket minskar lagringsavtrycket.
  • Kombinerar en eller flera komprimerade radgrupper för att öka rader per radgrupp, upp till högst 1 048 576 rader. Om du till exempel massinfogar fem batchar med 102 400 rader vardera får du fem komprimerade radgrupper. Om du kör REORGANIZE sammanfogas dessa radgrupper i en komprimerad radgrupp med 512 000 rader. Detta förutsätter att det inte fanns någon ordlistestorlek eller minnesbegränsningar.
  • Databasmotorn försöker kombinera radgrupper där 10% eller fler av raderna har markerats som borttagna med andra radgrupper. Radgrupp 1 komprimeras till exempel och har 500 000 rader, medan radgrupp 21 komprimeras och har 1 048 576 rader. Radgrupp 21 har 60% av raderna markerade som borttagna, vilket lämnar 409 830 rader. Databasmotorn föredrar att kombinera dessa två radgrupper för att komprimera en ny radgrupp som har 909 830 rader.

När du har utfört datainläsningar kan du ha flera små radgrupper i delta-lagringen. Du kan använda ALTER INDEX REORGANIZE för att tvinga dessa radgrupper till columnstore och sedan kombinera mindre komprimerade radgrupper i större komprimerade radgrupper. Omorganiseringsåtgärden tar också bort rader som har markerats som borttagna från kolumnstore.

Not

Att omorganisera ett kolumnlagringsindex med hjälp av Management Studio kombinerar komprimerade radgrupper, men tvingar inte alla radgrupper att komprimeras till kolumnlagringen. Stängda radgrupper komprimeras, men öppna radgrupper komprimeras inte till columnstore. För att med tvångskomprimera alla radgrupper, använd Transact-SQL exempel som inkluderar COMPRESS_ALL_ROW_GROUPS = ON.

Vad du bör tänka på innan du utför indexunderhåll

Indexunderhåll, som utförs genom att antingen omorganisera eller återskapa ett index, är resursintensivt. Det orsakar en betydande ökning av CPU-användning, minne som används och lagrings-I/O. Beroende på databasens arbetsbelastning och andra faktorer kan fördelarna dock vara allt från mycket viktiga till minimala.

Undvik onödig resursanvändning genom att undvika att utföra indexunderhåll urskillningslöst. I stället bör prestandafördelarna med indexunderhåll fastställas empiriskt för varje arbetsbelastning med hjälp av den rekommenderade strategioch vägas mot resurskostnader och arbetsbelastningspåverkan som krävs för att uppnå dessa fördelar.

Sannolikheten att se prestandafördelarna med att omorganisera eller återskapa ett index är högre när indexet är kraftigt fragmenterat eller när dess sidtäthet är låg. Dessa är dock inte de enda saker att tänka på. Faktorer som frågemönster (transaktionsbearbetning jämfört med analys och rapportering), beteende för lagringsundersystem, tillgängligt minne och förbättringar av databasmotorn över tid spelar en roll.

Viktig

Beslut om indexunderhåll bör fattas efter att du har övervägt flera faktorer i den specifika kontexten för varje arbetsbelastning, inklusive resurskostnaden för underhåll. De bör inte baseras på fastställd fragmentering eller enbart tröskelvärden för sidtäthet.

En positiv bieffekt av återskapande av index

Kunder observerar ofta prestandaförbättringar efter att ha återskapat index. I många fall är dessa förbättringar dock inte relaterade till att minska fragmenteringen eller öka sidtätheten.

Ett återskapande av index har en viktig fördel: det uppdaterar statistik på nyckelkolumner i indexet genom att skanna alla rader i indexet. Detta motsvarar att köra UPDATE STATISTICS ... WITH FULLSCAN, vilket gör statistiken aktuell och ibland förbättrar deras kvalitet jämfört med standarduppdateringen av urvalsstatistik. När statistiken uppdateras omkompileras frågeplaner som refererar till dem. Om den tidigare planen för en fråga inte var optimal på grund av inaktuell statistik, otillräckligt statistiksamplingsförhållande eller av andra skäl presterar den omkompilerade planen ofta bättre.

Kunder tillskriver ofta felaktigt den här förbättringen själva ombyggnaden av indexet och betraktar det som ett resultat av minskad fragmentering och ökad sidtäthet. I själva verket kan samma fördel ofta uppnås till mycket billigare resurskostnader genom att uppdatera statistik i stället för att återskapa index.

Tips

Resurskostnaden för att uppdatera statistik är mindre jämfört med återskapande av index och åtgärden slutförs ofta på några minuter. Det kan ta timmar att återskapa index.

Strategi för indexunderhåll

Microsoft rekommenderar att kunderna överväger och antar följande strategi för indexunderhåll:

  • Anta inte att indexunderhållet alltid kommer att förbättra din arbetsbelastning märkbart.
  • Mät den specifika effekten av att omorganisera eller återskapa index på frågeprestanda i din arbetsbelastning. Query Store är ett bra sätt att mäta prestandan "före underhåll" och "efter underhåll" med hjälp av A/B-testning teknik.
  • Om du ser att återskapande av index förbättrar prestandan kan du prova att ersätta det med uppdatering av statistik. Detta kan resultera i en liknande förbättring. I så fall kanske du inte behöver återskapa index så ofta, eller alls, och i stället kan utföra regelbundna statistikuppdateringar. För viss statistik kan du behöva öka samplingsförhållandet med hjälp av satserna WITH SAMPLE ... PERCENT eller WITH FULLSCAN (detta är inte vanligt).
  • Övervaka indexfragmentering och sidtäthet över tid för att se om det finns en korrelation mellan dessa värden som trendar uppåt eller nedåt och frågeprestanda. Om högre fragmentering eller lägre syddensitet försämrar prestandan oacceptabelt, bör du omorganisera eller ombygga indexen. Det räcker ofta att endast omorganisera eller återskapa specifika index som används av frågor med försämrad prestanda. På så sätt undviker du en högre resurskostnad för att underhålla varje index i databasen.
  • Genom att upprätta en korrelation mellan fragmentering/siddensitet och prestanda kan du också fastställa frekvensen för indexunderhåll. Anta inte att underhåll måste utföras enligt ett fast schema. En bättre strategi är att övervaka fragmentering och sidtäthet och köra indexunderhåll vid behov innan prestandan försämras till en oacceptabel nivå.
  • Om du har fastställt att indexunderhåll behövs och dess resurskostnad är acceptabel kan du utföra underhåll under korta resursanvändningstider, om möjligt.
  • Testa regelbundet eftersom resursanvändningsmönster kan ändras med tiden.

Indexunderhåll i Azure SQL Database och Azure SQL Managed Instance

Utöver ovanstående överväganden och strategi är det särskilt viktigt att överväga kostnaderna och fördelarna med indexunderhåll i Azure SQL Database och Azure SQL Managed Instance. Kunder bör endast utföra det när det finns ett påvisat behov och ta hänsyn till följande punkter.

  • Azure SQL Database och Azure SQL Managed Instance implementerar resursstyrning för att ange gränser för PROCESSOR-, minnes- och I/O-förbrukning enligt den etablerade prisnivån. Dessa gränser gäller för alla användararbetsbelastningar, inklusive indexunderhåll. Om den sammanlagda resursförbrukningen för alla arbetsbelastningar närmar sig resursgränsen, kan åtgärden att återskapa eller omorganisera försämra prestanda för andra arbetsbelastningar på grund av resurskonflikter. Massdatainläsningar kan till exempel bli långsammare eftersom transaktionsloggens I/O ligger på 100% på grund av en samtidig återskapning av index. I Azure SQL Managed Instance kan den här effekten minskas genom att indexunderhåll körs i en separat arbetsbelastningsgrupp för Resource Governor med begränsad resursallokering, på bekostnad av att förlänga varaktigheten för indexunderhåll.
  • För kostnadsbesparingar etablerar kunder ofta databaser, elastiska pooler och hanterade instanser med minimalt resursutrymme. Prisnivån väljs som tillräcklig för programarbetsbelastningar. För att hantera en betydande ökning av resursanvändningen på grund av indexunderhåll utan att försämra programprestandan kan kunderna behöva etablera fler resurser och öka kostnaderna, utan att nödvändigtvis förbättra programmets prestanda.
  • I elastiska pooler delas resurser mellan alla databaser i en pool. Även om en viss databas är inaktiv kan indexunderhåll på databasen påverka programarbetsbelastningar som körs samtidigt i andra databaser i samma pool. Mer information finns i Resurshantering i kompakta elastiska pooler.
  • För de flesta typer av lagring som används i Azure SQL Database och Azure SQL Managed Instance finns det ingen skillnad i prestanda mellan sekventiell I/O och slumpmässig I/O. Detta minskar effekten av indexfragmentering på frågeprestanda.
  • När du använder antingen Read Scale-out eller Geo-replikering repliker, ökar ofta datafördröjningen på repliker medan indexunderhåll utförs på den primära repliken. Om en geo-replika etableras med otillräckliga resurser för att upprätthålla en ökning av genereringen av transaktionsloggar som orsakas av indexunderhåll, kan den ligga långt efter den primära, vilket gör att systemet behöver återställa den. Det gör repliken otillgänglig tills den har återställts. I tjänstnivåerna Premium och Business Critical kan repliker som används för hög tillgänglighet dessutom komma långt efter det primära under indexunderhållet. Om en redundansväxling krävs under eller strax efter indexunderhållet kan det ta längre tid än förväntat.
  • Om ett index återskapas körs på den primära repliken och en tidskrävande fråga körs på en läsbar replik samtidigt, kan frågan avslutas automatiskt för att förhindra blockering av redo-tråden på repliken.

Det finns specifika men ovanliga scenarier när engångs- eller periodiskt indexunderhåll kan behövas i Azure SQL Database och Azure SQL Managed Instance:

Tips

Om du har fastställt att indexunderhåll är nödvändigt för dina Azure SQL Database- och Azure SQL Managed Instance-arbetsbelastningar bör du antingen omorganisera index eller använda återskapande av onlineindex. Detta gör att frågearbetsbelastningar kan komma åt tabeller medan index återskapas.

Genom att göra åtgärden återupptad kan du dessutom undvika att starta om den från början om den avbryts av en planerad eller oplanerad databasredundans. Det är särskilt viktigt att använda återupptabara indexåtgärder när index är stora.

Tips

Offlineindexåtgärder slutförs vanligtvis snabbare än onlineåtgärder. De bör användas när tabeller inte kommer att nås av frågor under åtgärden, till exempel efter inläsning av data i mellanlagringstabeller som en del av en sekventiell ETL-process.

Begränsningar och restriktioner

Radlagringsindex med mer än 128 omfattningar återskapas i två separata faser: logiska och fysiska. I den logiska fasen markeras de allokeringsenheter som indexet använder för avallokering, dataraderna kopieras och sorteras, och flyttas sedan till nya allokeringsenheter som skapats för att lagra det återskapade indexet. I den fysiska fasen tas de allokeringsenheter som tidigare markerats för frigöring fysiskt bort i korta transaktioner som inträffar i bakgrunden och kräver inte många lås. Mer information om allokeringsenheter finns i arkitekturguiden sidor och omfattningar.

ALTER INDEX REORGANIZE-instruktionen kräver att datafilen som innehåller indexet har tillgängligt utrymme, eftersom åtgärden endast kan allokera tillfälliga arbetssidor i samma fil, inte i en annan fil i samma filgrupp. Även om filgruppen har ledigt utrymme kan användaren fortfarande stöta på fel 1105: 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 under omorganiseringsåtgärden om en datafil är slut.

Det går inte att ordna om ett index när ALLOW_PAGE_LOCKS är inställt på OFF.

Upp till SQL Server 2017 (14.x) är återskapande av ett grupperat kolumnlagringsindex en offlineåtgärd. Databasmotorn måste skaffa ett exklusivt lås på tabellen eller partitionen medan ombyggnaden sker. Datat är offline och otillgängligt under återskapandet, även när du använder NOLOCK, ögonblicksbildisolering med bekräftad läsning (RCSI) eller ögonblicksbildisolering. Från och med SQL Server 2019 (15.x) kan ett grupperat kolumnlagringsindex återskapas med hjälp av alternativet ONLINE = ON.

Varning

Det är möjligt att skapa och återskapa nonaligerade index i en tabell med fler än 1 000 partitioner, men stöds inte. Detta kan orsaka försämrad prestanda eller överdriven minnesförbrukning under dessa åtgärder. Microsoft rekommenderar att du endast använder justerade index när antalet partitioner överskrider 1 000.

Statistikbegränsningar

  • När ett index skapas eller återskapas, skapas eller uppdateras statistiken genom att genomsöka alla rader i tabellen, vilket motsvarar användningen av FULLSCAN-satsen i CREATE STATISTICS eller UPDATE STATISTICS. Men från och med SQL Server 2012 (11.x), när ett partitionerat index skapas eller återskapas, skapas eller uppdateras inte statistik genom att genomsöka alla rader i tabellen. I stället används standardsamplingsförhållandet. Om du vill skapa eller uppdatera statistik för partitionerade index genom att skanna alla rader i tabellen använder du CREATE STATISTICS eller UPDATE STATISTICS med FULLSCAN-satsen.
  • På samma sätt, när operationen för att skapa eller återskapa index kan återupptas, skapas eller uppdateras statistiken med standardmässigt urvalsförhållande. Om statistik har skapats eller senast uppdaterats med PERSIST_SAMPLE_PERCENT-satsen inställd på ONanvänder återupptabara indexåtgärder det bevarade samplingsförhållandet för att skapa eller uppdatera statistik.
  • När ett index omorganiserasuppdateras inte statistiken.

Exempel

Kontrollera fragmentering och sidtäthet för ett radlagringsindex med hjälp av Transact-SQL

I följande exempel fastställs den genomsnittliga fragmenteringen och sidtätheten för alla radlagringsindex i den aktuella databasen. Den använder SAMPLED läge för att snabbt returnera åtgärdsbara resultat. Använd DETAILED läge för att få mer exakta resultat. Detta kräver genomsökning av alla indexsidor och kan ta lång tid.

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;

Föregående uttryck returnerar en resultatuppsättning som liknar följande:

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

Mer information finns i sys.dm_db_index_physical_stats.

Kontrollera fragmenteringen av ett kolumnlagringsindex med hjälp av Transact-SQL

I följande exempel fastställs den genomsnittliga fragmenteringen för alla kolumnlagringsindex med komprimerade radgrupper i den aktuella databasen.

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;

Föregående uttryck returnerar en resultatuppsättning som liknar följande:

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

Underhålla index med SQL Server Management Studio

Omorganisera eller återskapa ett index

  1. I Object Explorerexpanderar du databasen som innehåller tabellen där du vill omorganisera ett index.
  2. Expandera mappen Tables.
  3. Expandera tabellen där du vill omorganisera ett index.
  4. Expandera mappen Index.
  5. Högerklicka på det index som du vill ordna om och välj Ordna om.
  6. I dialogrutan Ordna om index kontrollerar du att rätt index finns i index som ska organiseras om rutnät och väljer OK.
  7. Markera kryssrutan Komprimera stora objektkolumndata för att ange att alla sidor som innehåller stora objektdata (LOB) också komprimeras.
  8. Välj OK.

Ordna om alla index i en tabell

  1. I Object Explorerexpanderar du databasen som innehåller tabellen där du vill omorganisera indexen.
  2. Utvidga mappen Tables.
  3. Expandera tabellen där du vill ordna om indexen.
  4. Högerklicka på mappen Index och välj Ordna om alla.
  5. I dialogrutan Omorganisera index kontrollerar du att rätt index finns i index som ska ordnas om. Om du vill ta bort ett index från Index som ska omorganiseras rutnätet väljer du indexet och trycker sedan på ta bort.
  6. Markera kryssrutan Komprimera stora objektkolumndata för att ange att alla sidor som innehåller stora objektdata (LOB) också komprimeras.
  7. Välj OK.

Underhålla index med hjälp av Transact-SQL

Note

Fler exempel på hur du använder Transact-SQL för att återskapa eller omorganisera index finns i ALTER INDEX Examples – Rowstore Indexes and ALTER INDEX Examples – Columnstore Indexes.

Omorganisera ett index

I följande exempel omorganiseras IX_Employee_OrganizationalLevel_OrganizationalNode index i tabellen HumanResources.Employee i AdventureWorks2022-databasen.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

I följande exempel omorganiseras IndFactResellerSalesXL_CCI kolumnlagringsindex i tabellen dbo.FactResellerSalesXL_CCI i AdventureWorksDW2022-databasen. Det här kommandot tvingar alla stängda och öppna radgrupper till columnstore.

-- 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);

Ordna om alla index i en tabell

I följande exempel omorganiseras alla index i tabellen HumanResources.Employee i databasen AdventureWorks2022.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Återskapa ett index

I följande exempel återskapas ett enda index i tabellen Employee i AdventureWorks2022-databasen.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Återskapa alla index i en tabell

I följande exempel återskapas alla index som är associerade med tabellen i AdventureWorks2022-databasen med hjälp av nyckelordet ALL. Tre alternativ har angetts.

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

Mer information finns i ALTER INDEX.

Relaterat innehåll