Utforska databasunderhållskontroller

  • 7 minuter

Frågeoptimeraren använder statistisk information från indexen för att försöka skapa den mest optimala körningsplanen.

I Azure SQL-underhållsaktiviteter som säkerhetskopior och integritetskontroller hanteras åt dig, och även om du kanske kommer undan med automatiska uppdateringar som håller din statistik uppdaterad, räcker det ibland inte.

Om du har felfria index och statistik ser du till att en viss plan fungerar optimalt. Indexunderhåll bör utföras regelbundet när data i dina databaser ändras över tid. Du kan ändra strategin för indexunderhåll baserat på frekvensen för ändringar av dina data.

Återskapa och omorganisera

Indexfragmentering sker när logisk ordning på indexsidor inte matchar den fysiska ordningen. Sidor kan vara ur ordning under rutinmässiga datamodifieringsinstruktioner som UPDATE, DELETEoch INSERT. Fragmentering kan medföra prestandaproblem på grund av den extra I/O som krävs för att hitta de data som refereras av pekarna på indexsidorna.

När data infogas, uppdateras och tas bort från index matchar den logiska ordningen i indexet inte längre den fysiska ordningen på sidorna och mellan sidorna, vilket utgör indexen. Med tiden kan dataändringarna dessutom leda till att data sprids eller fragmenteras i databasen. Fragmentering kan försämra frågeprestanda när databasmotorn behöver läsa extra sidor för att hitta nödvändiga data.

En omorganisering av ett index är en onlineåtgärd som defragmenterar indexets lövnivå (både klustrad och icke-grupperad). Den här defragmenteringsprocessen ordnar fysiskt om sidorna på lövnivå så att de matchar nodernas logiska ordning från vänster till höger. Under den här processen komprimeras även indexsidorna baserat på det konfigurerade fillfactor-värdet.

En ombyggnad kan antingen vara online eller offline beroende på vilket kommando som körs eller vilken version av SQL Server som används. En offlineåterbyggnadsprocess släpper och återskapar själva indexet. Om du kan göra det online skapas ett nytt index parallellt med det befintliga indexet. När det nya indexet har skapats kommer det befintliga att tas bort och sedan kommer det nya att byta namn så att det matchar det gamla indexnamnet. Tänk på att onlineversionen kräver mer utrymme eftersom det nya indexet byggs parallellt med det befintliga indexet.

Den vanliga vägledningen för indexunderhåll är:

  • > 5 % men < 30 % – Omorganisera indexet

  • > 30 % – Återskapa indexet

Använd dessa siffror som allmänna rekommendationer. Beroende på din arbetsbelastning och dina data kan du behöva vara mer bestämd, eller i vissa fall kanske du kan skjuta upp indexunderhåll för databaser som främst utför frågor som söker efter specifika sidor.

SQL Server- och Azure SQL-plattformarna erbjuder DMV:er som gör att du kan identifiera fragmentering i dina objekt. De vanligaste DMV:erna för detta ändamål är sys.dm_db_index_physical_stats för b-trädindex och sys.dm_db_column_store_row_group_physical_stats för kolumnlagringsindex.

En annan sak att notera är att indexet återskapas gör att statistiken för indexet uppdateras, vilket ytterligare kan hjälpa prestanda. Indexomorganisering uppdaterar inte statistik.

Microsoft introducerade återupptagningsbara återskapningsindexåtgärder med SQL Server 2017. Alternativet Återskapa indexåtgärder ger större flexibilitet när det gäller att kontrollera hur lång tid en ombyggnadsåtgärd kan medföra för en viss instans. Med SQL Server 2019 introducerades möjligheten att styra en associerad maximal grad av parallellitet ytterligare vilket ger mer detaljerad kontroll till databasadministratörer.

Statistik

När du gör prestandajusteringar i Azure SQL är det viktigt att förstå vikten av statistik.

Statistik lagras i användardatabasen som binära stora objekt (blobar). Dessa blobar innehåller statistisk information om fördelningen av datavärden i en eller flera kolumner i en tabell eller indexerad vy.

Statistik innehåller information om fördelningen av datavärden i en kolumn. Frågeoptimeraren använder kolumn- och indexstatistik för att fastställa kardinalitet, vilket är antalet rader som en fråga förväntas returnera.

Kardinalitetsuppskattningar används sedan av frågeoptimeraren för att generera körningsplanen. Kardinalitetsuppskattningar hjälper också optimeraren att avgöra vilken typ av åtgärd (till exempel indexsökning eller genomsökning) som ska användas för att hämta begärda data.

Om du vill se listan över användardefinierad statistik med det senast uppdaterade datumet kör du frågan nedan:

SELECT sp.stats_id, 
       name, 
       last_updated, 
       rows, 
       rows_sampled
FROM sys.stats
     CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1

Skapa statistik

När du har AUTO_CREATE_STATISTICS möjlighet till ONskapar frågeoptimeraren statistik för den indexerade kolumnen som standard. Frågeoptimeraren skapar också statistik för enskilda kolumner i frågepredikat.

Dessa metoder ger högkvalitativa frågeplaner för de flesta frågor. Ibland kan du behöva skapa mer statistik med hjälp av CREATE STATISTICS instruktionen för att förbättra specifika frågeplaner.

Vi rekommenderar att du behåller AUTO_CREATE_STATISTICS alternativet aktiverat eftersom det gör att frågeoptimeraren kan skapa statistik för frågepredikatkolumner automatiskt.

När du stöter på följande situationer bör du överväga att skapa statistik:

  • I Finjusteringsverktyg för databasmotorer föreslår vi att du skapar statistik
  • Frågepredikatet innehåller flera kolumner som inte redan finns i samma index
  • Frågan väljer från en delmängd data
  • Frågan saknar statistik

Automatisering av underhållsaktiviteter

Azure SQL tillhandahåller inbyggda verktyg för att utföra databasunderhållsuppgifter i automatiseringssyfte. Olika verktyg är tillgängliga beroende på vilken plattform databasen körs på.

SQL Server på en virtuell Azure-dator

Du har åtkomst till schemaläggningstjänster, till exempel SQL-agenten eller Schemaläggaren för Windows. Dessa automatiseringsverktyg kan hjälpa till att hålla mängden fragmentering i index till ett minimum. Med större databaser måste en balans mellan en återskapande och en omorganisering av index hittas för att säkerställa optimala prestanda. Med flexibiliteten från SQL Agent eller Schemaläggaren kan du köra anpassade jobb.

Azure SQL Database

På grund av Azure SQL Database har du inte åtkomst till SQL Server Agent eller Windows Task Scheduler. Utan dessa tjänster måste indexunderhåll skapas med andra metoder. Det finns tre sätt att hantera underhållsåtgärder för SQL Database:

  • Azure Automation Runbook-rutiner

  • SQL Agent-jobb från SQL Server på en virtuell Azure-dator (fjärranrop)

  • Elastiska Azure SQL-jobb

Azure SQL Managed Instance

Precis som med SQL Server på en virtuell Azure-dator kan du schemalägga jobb på en SQL Managed Instance via SQL Server Agent. Att använda SQL Server Agent ger flexibilitet att köra kod som är utformad för att minska fragmenteringen i indexen i databasen.