Dela via

Statistik

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-databas i Microsoft Fabric

Frågeoptimeraren använder statistik för att skapa frågeplaner som förbättrar frågeprestandan. För de flesta frågor genererar frågeoptimeraren redan nödvändig statistik för en frågeplan av hög kvalitet. I vissa fall måste du skapa extra statistik eller ändra frågedesignen för bästa resultat. Den här artikeln beskriver statistikbegrepp och innehåller riktlinjer för att använda statistik för frågeoptimering på ett effektivt sätt.

Komponenter och begrepp

Statistik

Statistik för frågeoptimering är binära stora objekt (BLOB) som innehåller statistisk information om fördelningen av värden i en eller flera kolumner i en tabell eller indexerad vy. Frågeoptimeraren använder den här statistiken för att uppskatta kardinaliteten eller antalet rader i frågeresultatet. Med dessa kardinalitetsuppskattningar kan frågeoptimeraren skapa en frågeplan av hög kvalitet. Beroende på dina predikat kan frågeoptimeraren till exempel använda kardinalitetsuppskattningar för att välja operatorn indexsökning i stället för den mer resursintensiva indexgenomsökningsoperatorn, om det förbättrar frågeprestandan.

Varje statistikobjekt skapas i en lista över en eller flera tabellkolumner och innehåller ett histogram som visar fördelningen av värden i den första kolumnen. Statistikobjekt på flera kolumner lagrar också statistisk information om korrelationen mellan värden mellan kolumnerna. Dessa korrelationsstatistik, eller densiteter, härleds från antalet distinkta rader med kolumnvärden.

Histogram

Ett histogram mäter förekomstens frekvens för varje distinkt värde i en datauppsättning. Frågeoptimeraren beräknar ett histogram på kolumnvärdena i den första nyckelkolumnen i statistikobjektet, väljer kolumnvärdena genom att statistiskt sampling av raderna eller genom att utföra en fullständig genomsökning av alla rader i tabellen eller vyn. Om histogrammet skapas från en samplad uppsättning rader är de lagrade summorna för antalet rader och antalet distinkta värden uppskattningar och behöver inte vara heltal.

Anmärkning

Histogram i SQL Server skapas bara för en enda kolumn – den första kolumnen i uppsättningen med nyckelkolumner i statistikobjektet.

Om du vill skapa histogrammet sorterar Frågeoptimeraren kolumnvärdena, beräknar antalet värden som matchar varje distinkt kolumnvärde och aggregerar sedan kolumnvärdena i högst 200 sammanhängande histogramsteg. Varje histogramsteg innehåller ett intervall med kolumnvärden följt av ett kolumnvärde med övre gräns. Intervallet innehåller alla möjliga kolumnvärden mellan gränsvärdena, exklusive själva gränsvärdena. Det lägsta av de sorterade kolumnvärdena är det övre gränsvärdet för det första histogramsteget.

Mer detaljerat skapar SQL Server histogrammet från den sorterade uppsättningen med kolumnvärden i tre steg:

  • Histograminitiering: I det första steget bearbetas en sekvens med värden som börjar i början av den sorterade uppsättningen och upp till 200 värden för range_high_key, equal_rows, range_rows och distinct_range_rows samlas in (range_rows och distinct_range_rows är alltid noll under det här steget). Det första steget slutar antingen när alla indata har förbrukats eller när 200 värden har hittats.
  • Genomsök med bucketsammanslagning: Varje ytterligare värde från den inledande kolumnen i statistiknyckeln bearbetas i det andra steget, i sorterad ordning; varje efterföljande värde läggs antingen till i det sista intervallet eller så skapas ett nytt intervall i slutet (detta är möjligt eftersom indatavärdena sorteras). Om ett nytt intervall skapas komprimeras ett par befintliga närliggande intervall till ett enda intervall. Det här intervallparet har valts för att minimera informationsförlusten. Den här metoden använder en algoritm för maximal skillnad för att minimera antalet steg i histogrammet samtidigt som skillnaden mellan gränsvärdena maximeras. Antalet steg efter att intervallen har komprimerats ligger kvar på 200 under hela det här steget.
  • Histogramkonsolidering: I det tredje steget kan fler intervall minimeras om en betydande mängd information inte går förlorad. Antalet histogramsteg kan vara färre än antalet distinkta värden, även för kolumner med färre än 200 gränspunkter. Även om kolumnen har fler än 200 unika värden kan histogrammet därför ha färre än 200 steg. För en kolumn som endast består av unika värden har det konsoliderade histogrammet minst tre steg.

Anmärkning

Om histogrammet har skapats med hjälp av ett stickprov i stället för fullscan beräknas värdena för equal_rows, range_rows, distinct_range_rows och average_range_rows, och därför behöver de inte vara hela heltal.

Följande diagram visar ett histogram med sex steg. Området till vänster om det första övre gränsvärdet är det första steget.

Diagram över hur ett histogram beräknas utifrån samplade kolumnvärden.

För varje histogramsteg i föregående exempel:

  • Fet linje representerar det övre gränsvärdet (range_high_key) och antalet gånger det inträffar (equal_rows)

  • Heldragen yta till vänster om range_high_key representerar kolumnvärdenas intervall och det genomsnittliga antalet gånger varje kolumnvärde förekommer (average_range_rows). Den genomsnittliga antalet rader för det första histogramsteget är alltid 0.

  • Streckade linjer representerar de sampelvärden som används för att uppskatta det totala antalet distinkta värden i intervallet (distinct_range_rows) och det totala antalet värden i intervallet (range_rows). Frågeoptimeraren använder range_rows och distinct_range_rows för att beräkna average_range_rows och lagrar inte de samplade värdena.

Densitetsvektor

Densitet är information om antalet dubbletter i en viss kolumn eller kombination av kolumner och beräknas som 1/(antal distinkta värden). Frågeoptimeraren använder tätheter för att förbättra kardinalitetsuppskattningar för frågor som returnerar flera kolumner från samma tabell eller indexerade vy. När densiteten minskar ökar ett värdes selektivitet. I en tabell som till exempel representerar bilar har många bilar samma tillverkare, men varje bil har ett unikt fordonsidentifieringsnummer (VIN). Ett index på VIN är mer selektivt än ett index på tillverkaren, eftersom VIN har lägre densitet än tillverkaren.

Anmärkning

Frekvens är information om förekomsten av varje distinkt värde i den första nyckelkolumnen i statistikobjektet och beräknas som row count * density. En maximal frekvens på 1 finns i kolumner med unika värden.

Densitetsvektorn innehåller en densitet för varje prefix för kolumner i statistikobjektet. Om ett statistikobjekt till exempel har nyckelkolumnerna CustomerId, ItemId och Priceberäknas densiteten på vart och ett av följande kolumnprefix.

Kolumnprefix Densitet beräknad på
(CustomerId) Rader med matchande värden för CustomerId
(CustomerId, ItemId) Rader med matchande värden för CustomerId och ItemId
(CustomerId, ItemId, Price) Rader med matchande värden för CustomerId, ItemIdoch Price

Filtrerad statistik

Filtrerad statistik kan förbättra frågeprestanda för frågor som väljer från väldefinierade delmängder av data. Filtrerad statistik använder ett filterpredikat för att välja den delmängd av data som ingår i statistiken. Väl utformad filtrerad statistik kan förbättra frågeexekveringsplanen jämfört med fullständig tabellstatistik. Mer information om filterpredikatet finns i SKAPA STATISTIK. Mer information om när du ska skapa filtrerad statistik finns i avsnittet När du ska skapa statistik i den här artikeln.

Statistikalternativ

Det finns alternativ som påverkar när och hur statistik skapas och uppdateras. De här alternativen kan endast konfigureras på databasnivå.

AUTO_CREATE_STATISTICS inställning

När alternativet för automatisk skapande av statistik , AUTO_CREATE_STATISTICS är PÅ, skapar frågeoptimeraren statistik för enskilda kolumner i frågepredikatet, efter behov, för att förbättra kardinalitetsuppskattningarna för frågeplanen. Den här statistik med en kolumn skapas för kolumner som inte redan har ett histogram i ett befintligt statistikobjekt. Alternativet AUTO_CREATE_STATISTICS avgör inte om statistik skapas för index. Det här alternativet genererar inte heller filtrerad statistik. Den gäller strikt för statistik med en kolumn för den fullständiga tabellen.

När Frågeoptimeraren skapar statistik som ett resultat av att använda AUTO_CREATE_STATISTICS alternativet börjar statistiknamnet med _WA. Du kan använda följande fråga för att avgöra om frågeoptimeraren har skapat statistik för en frågepredikatkolumn.

SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s
    INNER JOIN sys.stats_columns AS sc
        ON s.stats_id = sc.stats_id
        AND s.object_id = sc.object_id
WHERE s.name LIKE '_WA%'
ORDER BY s.name;

AUTO_UPDATE_STATISTICS inställning

När alternativet för automatisk uppdateringsstatistik , AUTO_UPDATE_STATISTICS är PÅ, avgör frågeoptimeraren när statistiken kan vara inaktuell och uppdaterar dem sedan när de används av en fråga. Den här åtgärden kallas även för omkompilering av statistik. Statistiken blir inaktuell efter att ändringar från åtgärderna infoga, uppdatera, ta bort eller slå samman ändrar datadistributionen i tabellen eller den indexerade vyn. Frågeoptimeraren avgör när statistiken kan vara inaktuell genom att räkna antalet radändringar sedan den senaste statistikuppdateringen och jämföra antalet radändringar med ett tröskelvärde. Tröskelvärdet baseras på tabellens kardinalitet, som kan definieras som antalet rader i tabellen eller indexerad vy.

Att markera statistik som inaktuell baserat på radändringar sker även när alternativet AUTO_UPDATE_STATISTICS är AV. När alternativet AUTO_UPDATE_STATISTICS är AV uppdateras inte statistiken, även om de har markerats som inaktuella. Planer fortsätter att använda inaktuella statistikobjekt. Om du ställer in AUTO_UPDATE_STATISTICS på OFF kan det orsaka suboptimala frågeplaner och försämrad frågeprestanda. Vi rekommenderar att du anger alternativet AUTO_UPDATE STATISTICS till PÅ.

  • Fram till SQL Server 2014 (12.x) använder databasmotorn ett omkompileringströskelvärde baserat på antalet rader i tabellen eller den indexerade vyn när statistiken utvärderades. Tröskelvärdet skiljer sig från om en tabell är tillfällig eller permanent.

    Tabelltyp Tabellkardinalitet (n) Tröskelvärde för omkompilering (# modifikationer)
    Temporär n< 6 6
    Temporär 6 <= n<= 500 500
    Permanent n<= 500 500
    Tillfällig eller permanent n> 500 500 + (0,20 * n)

    Om tabellen till exempel innehåller 20 000 rader är 500 + (0.2 * 20,000) = 4,500 beräkningen och statistiken uppdateras var 4 500:e ändring.

  • Från och med SQL Server 2016 (13.x) och med databasens kompatibilitetsnivå 130 använder databasmotorn också ett minskande, dynamiskt statistikomkompileringströskelvärde som justeras enligt tabellens kardinalitet när statistiken utvärderades. Med den här ändringen uppdateras statistik för stora tabeller oftare. Men om en databas har en kompatibilitetsnivå under 130 gäller tröskelvärdena för SQL Server 2014 (12.x).

    Tabelltyp Tabell kardinalitet (n) Tröskelvärde för rekompilering (antal ändringar)
    Temporär n < 6 6
    Temporär 6 <= n <= 500 500
    Permanent n <= 500 500
    Tillfällig eller permanent n > 500 MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

    Om tabellen till exempel innehåller 2 miljoner rader är beräkningen minst 500 + (0.20 * 2,000,000) = 400,500 och SQRT(1,000 * 2,000,000) = 44,721. Det innebär att statistiken uppdateras var 44 721:e ändring.

Viktigt!

I SQL Server 2008 R2 (10.50.x) via SQL Server 2014 (12.x) eller i SQL Server 2016 (13.x) och senare versioner med databaskompatibilitetsnivå 120 och lägre versioner aktiverar du spårningsflagga 2371 så att SQL Server använder ett minskande tröskelvärde för dynamisk statistikuppdatering.

Även om det rekommenderas för alla scenarier är det valfritt att aktivera spårningsflagga 2371. Du kan dock använda följande vägledning för att aktivera spårningsflaggan 2371 i din pre-SQL Server 2016-miljö (13.x):

  • Om du använder ett SAP-system aktiverar du den här spårningen. Mer information finns i den här bloggen om spårningsflagga 2371.
  • Om du måste lita på nattjobb för att uppdatera statistik eftersom den nuvarande automatiska uppdateringen inte utlöses tillräckligt ofta, överväg att aktivera spårningsflaggan 2371 för att justera tröskelvärdet till tabellens kardinalitet.

Frågeoptimeraren söker efter inaktuell statistik innan en fråga kompileras och innan en cachelagrad frågeplan körs. Innan frågan kompileras använder Frågeoptimeraren kolumner, tabeller och indexerade vyer i frågepredikatet för att avgöra vilken statistik som kan vara inaktuell. Innan den kör en cachelagrad frågeplan verifierar databasmotorn att frågeplanen refererar till up-to-date-statistik.

Alternativet AUTO_UPDATE_STATISTICS gäller för statistikobjekt som skapats för index, enkla kolumner i frågepredikat och statistik som skapats med instruktionen CREATE STATISTICS . Det här alternativet gäller även för filtrerad statistik.

Du kan använda sys.dm_db_stats_properties för att korrekt spåra antalet rader som ändrats i en tabell och bestämma om du vill uppdatera statistiken manuellt.

AUTO_UPDATE_STATISTICS är alltid AV för minnesoptimerade tabeller.

AUTO_UPDATE_STATISTICS_ASYNC

Uppdateringsalternativet för asynkron statistik , AUTO_UPDATE_STATISTICS_ASYNC, avgör om frågeoptimeraren använder synkrona eller asynkrona statistikuppdateringar. Som standard är alternativet för asynkron statistikuppdatering AV och frågeoptimeraren uppdaterar statistiken synkront. Alternativet AUTO_UPDATE_STATISTICS_ASYNC gäller för statistikobjekt som skapats för index, enskilda kolumner i frågepredikat och statistik som skapats med instruktionen CREATE STATISTICS .

Anmärkning

Om du vill ange alternativet för asynkron statistikuppdatering i SQL Server Management Studio på sidan Alternativ i fönstret Databasegenskaper måste alternativen för automatisk uppdateringsstatistik och automatisk uppdateringsstatistik asynkront anges till Sant.

Statistikuppdateringar kan vara synkrona (standard) eller asynkrona.

  • Med synkrona statistikuppdateringar kompileras och körs frågor alltid med statistik för datum up-to. När statistiken är inaktuell väntar frågeoptimeraren på uppdaterad statistik innan den kompilerar och kör frågan.

  • Med asynkrona statistikuppdateringar kompileras frågor med befintlig statistik även om den befintliga statistiken är inaktuell. Frågeoptimeraren kan välja en suboptimal frågeplan om statistiken är inaktuell när frågan kompileras. Statistiken uppdateras vanligtvis strax därefter. När frågor kompileras efter att statistikuppdateringar har slutförts, drar de nytta av den uppdaterade statistiken.

Överväg att använda synkron statistik när du utför åtgärder som ändrar fördelningen av data, till exempel trunkera en tabell eller utföra en massuppdatering av en stor procentandel av raderna. Om du inte uppdaterar statistiken manuellt när du har slutfört åtgärden ser du till att statistiken up-to-date innan frågor körs på ändrade data med synkron statistik.

Överväg att använda asynkron statistik för att uppnå mer förutsägbara svarstider för frågor i följande scenarier:

  • Ditt program kör ofta samma fråga, liknande frågor eller liknande cachelagrade frågeplaner. Dina svarstider för frågor kan vara mer förutsägbara med asynkrona statistikuppdateringar än med synkrona statistikuppdateringar eftersom Frågeoptimeraren kan köra inkommande frågor utan att vänta på up-to-date-statistik. Detta förhindrar fördröjning av vissa frågor och inte andra.

  • Ditt program har överskridit tidsgränsen för klientbegäran på grund av att en eller flera frågor väntar på uppdaterad statistik. I vissa fall kan väntan på synkron statistik leda till att program med aggressiva tidsgränser misslyckas.

Anmärkning

Statistik för lokala temporära tabeller uppdateras alltid synkront oavsett AUTO_UPDATE_STATISTICS_ASYNC alternativ. Statistik för globala temporära tabeller uppdateras synkront eller asynkront enligt AUTO_UPDATE_STATISTICS_ASYNC alternativuppsättning för användardatabasen.

Asynkron statistikuppdatering utförs av en bakgrundsbegäran. När begäran är redo att skriva uppdaterad statistik till databasen försöker den hämta ett schemaändringslås för statistikmetadataobjektet. Om en annan session redan håller ett lås på samma objekt blockeras asynkron statistikuppdatering tills schemaändringslåset kan hämtas. På samma sätt kan sessioner som behöver hämta ett schemastabilitet (Sch-S) lås på statistikmetadataobjektet för att kompilera en fråga blockeras av bakgrundssessionen för asynkron statistikuppdatering, som redan håller kvar eller väntar på att hämta schemats ändringslås. För arbetsbelastningar med mycket frekventa frågekompileringar och frekventa statistikuppdateringar kan användning av asynkron statistik därför öka sannolikheten för samtidighetsproblem på grund av låsblockering.

I Azure SQL Database, Azure SQL Managed Instance och från och med SQL Server 2022 (16.x) kan du undvika potentiella samtidighetsproblem med hjälp av asynkron statistikuppdatering om du aktiverar ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY databasomfattande konfiguration. När den här konfigurationen är aktiverad väntar bakgrundsbegäran på att hämta schemaändringen (Sch-M) och bevarar den uppdaterade statistiken i en separat kö med låg prioritet, så att andra begäranden kan fortsätta kompilera frågor med befintlig statistik. När ingen annan session har ett lås på statistikmetadataobjektet hämtar bakgrundsbegäran sitt schemaändringslås och uppdaterar statistik. I den osannolika händelsen att bakgrundsbegäran inte kan hämta låset inom en tidsgräns på flera minuter avbryts uppdateringen av asynkron statistik och statistiken uppdateras inte förrän en annan automatisk statistikuppdatering utlöses eller tills statistiken uppdateras manuellt.

Anmärkning

Konfigurationsalternativet med databasomfattning ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY är tillgängligt i Azure SQL Database, Azure SQL Managed Instance och i SQL Server, från och med SQL Server 2022 (16.x).

AUTO_DROP inställning

Gäller för: Azure SQL Database, Azure SQL Managed Instance och börjar med SQL Server 2022 (16.x)

I SQL Server före SQL Server 2022 (16.x), om statistik skapas manuellt av en användare eller ett verktyg från tredje part i en användardatabas, kan dessa statistikobjekt blockera eller störa schemaändringar som du kanske vill.

Från och med SQL Server 2022 (16.x) aktiveras alternativet för automatisk borttagning som standard på alla nya och migrerade databaser. Med AUTO_DROP egenskapen kan du skapa statistikobjekt i ett läge så att en efterföljande schemaändring inte blockeras av statistikobjektet, utan i stället tas statistiken bort efter behov. På så sätt fungerar manuellt skapad statistik med automatisk radering aktiverad som statistik som skapats automatiskt.

I Azure SQL Database, Azure SQL Managed Instance och SQL Server 2022 (16.x) och senare versioner fungerar automatiskt statistik som om AUTO_DROP har angetts.

Anmärkning

Om du försöker ange eller ta bort egenskapen auto drop i statistik som skapats automatiskt kan det generera fel. Statistik som skapas automatiskt använder alltid automatisk borttagning. Vissa säkerhetskopior, när de återställs, kan ha den här egenskapen felaktigt inställd till nästa gång statistikobjektet uppdateras (manuellt eller automatiskt). Men statistik som skapas automatiskt fungerar alltid som statistik för automatisk borttagning. När du återställer en databas till SQL Server 2022 (16.x) från en tidigare version rekommenderar vi att du kör sp_updatestats på databasen och anger rätt metadata för funktionen för automatisk borttagning av statistik.

Om du till exempel vill skapa ett statistikobjekt manuellt i dbo.DatabaseLog tabellen:

CREATE STATISTICS [mystats]
    ON [dbo].[DatabaseLog]([DatabaseLogID], [PostTime], [DatabaseUser])
    WITH AUTO_DROP = ON;

Om du till exempel vill uppdatera en inställning för automatisk borttagning av statistikobjekt i dbo.DatabaseLog tabellen:

UPDATE STATISTICS [dbo].[DatabaseLog] ([mystats])
    WITH AUTO_DROP = ON;

Om du vill utvärdera inställningen för automatisk borttagning av befintlig statistik använder du auto_drop kolumnen i sys.stats:

SELECT object_id,
       [name],
       auto_drop
FROM sys.stats;

Mer information finns i AUTO_DROP.

INKREMENTELL

gäller för: SQL Server 2014 (12.x) och senare versioner.

När INKREMENTELLT alternativ för CREATE STATISTICS är aktiverat, skapas statistiken som partitionsspecifika. När du är AV tas statistikträdet bort och SQL Server beräknar om statistiken. Standardvärdet är AV. Den här inställningen åsidosätter egenskapen INCREMENTAL på databasnivå. Mer information om hur du skapar inkrementell statistik finns i SKAPA STATISTIK. Mer information om hur du skapar statistik per partition automatiskt finns i Databasegenskaper (alternativsida) och ALTER DATABASE SET-alternativ.

När nya partitioner läggs till i en stor tabell bör statistiken uppdateras för att inkludera de nya partitionerna. Tiden som krävs för att skanna hela tabellen (FULLSCAN eller SAMPLE alternativen) kan dock vara ganska lång. Dessutom är det inte nödvändigt att skanna hela tabellen eftersom endast statistiken för de nya partitionerna kan behövas. Det inkrementella alternativet skapar och lagrar statistik per partition, och när det uppdateras uppdateras bara statistik för de partitioner som behöver ny statistik

Om statistik per partition inte stöds ignoreras alternativet och en varning genereras. Inkrementell statistik stöds inte för följande statistiktyper:

  • Statistik som skapats med index som inte är partitionsjusterade med bastabellen.
  • Statistik som skapats på skrivbara sekundära databaser med AlwaysOn.
  • Statistik som skapats på skrivskyddade databaser.
  • Statistik som skapats för filtrerade index.
  • Statistik som skapats för vyer.
  • Statistik som skapats i interna tabeller.
  • Statistik som skapats med rumsliga index eller XML-index.

När du ska skapa statistik

Frågeoptimeraren skapar redan statistik på följande sätt:

  1. Frågeoptimeraren skapar statistik för index för tabeller eller vyer när indexet skapas. Den här statistiken skapas på nyckelkolumnerna i indexet. Om indexet är ett filtrerat index skapar Frågeoptimeraren filtrerad statistik på samma delmängd av rader som angetts för det filtrerade indexet. Mer information om filtrerade index finns i Skapa filtrerade index och SKAPA INDEX.

    Anmärkning

    I SQL Server 2014 (12.x) och senare versioner skapas inte statistik genom att genomsöka alla rader i tabellen när ett partitionerat index skapas eller återskapas. I stället använder Frågeoptimeraren standardsamplingsalgoritmen för att generera statistik. När du har uppgraderat en databas med partitionerade index kan du märka en skillnad i histogramdata för dessa index. Den här beteendeändringen kanske inte påverkar frågeprestanda. Om du vill hämta statistik om partitionerade index genom att skanna alla rader i tabellen använder du CREATE STATISTICS eller UPDATE STATISTICS med FULLSCAN-satsen.

  2. Frågeoptimeraren skapar statistik för enskilda kolumner i frågepredikat när AUTO_CREATE_STATISTICS är på.

För de flesta frågor säkerställer dessa två metoder för att skapa statistik en frågeplan av hög kvalitet. I några fall kan du förbättra frågeplaner genom att skapa ytterligare statistik med instruktionen CREATE STATISTICS . Den här ytterligare statistiken kan samla in statistiska korrelationer som frågeoptimeraren inte tar hänsyn till när den skapar statistik för index eller enskilda kolumner. Ditt program kan ha ytterligare statistiska korrelationer i tabelldata som, om de beräknas till ett statistikobjekt, kan göra det möjligt för Frågeoptimeraren att förbättra frågeplaner. Filtrerad statistik på en delmängd av datarader eller multikollumnstatistik för frågepredikatkolumner kan till exempel förbättra frågeplanen.

När du skapar statistik med instruktionen CREATE STATISTICS rekommenderar vi att du behåller alternativet AUTO_CREATE_STATISTICS PÅ så att frågeoptimeraren fortsätter att rutinmässigt skapa statistik med en kolumn för frågepredikatkolumner. Mer information om frågepredikat finns i Sökvillkor.

Överväg att skapa statistik med CREATE STATISTICS-instruktionen när något av följande gäller:

  • Justeringsguiden för databasmotorn föreslår att du skapar statistik.
  • Frågepredikatet innehåller flera korrelerade kolumner som inte redan är nycklar i samma index.
  • Frågan väljer från en delmängd data.
  • Frågan saknar statistik.

Anmärkning

Information som är specifik för In-Memory OLTP-relaterade tabeller och statistik finns i Statistik för Memory-Optimized tabeller.

Frågepredikat innehåller flera korrelerade kolumner

När ett frågepredikat innehåller flera kolumner som har relationer och beroenden mellan kolumner kan statistik på flera kolumner förbättra frågeplanen. Statistik för flera kolumner innehåller korrelationsstatistik för korskolumner, som kallas tätheter, som inte är tillgängliga i statistik med en kolumn. Densiteter kan förbättra kardinalitetsuppskattningar när frågeresultat beror på datarelationer mellan flera kolumner.

Om kolumnerna redan finns i samma index finns det redan ett statistikobjekt för flera kolumner och det är inte nödvändigt att skapa det manuellt. Om kolumnerna inte redan finns i samma index kan du skapa statistik för flera kolumner genom att skapa ett index för kolumnerna eller med hjälp av INSTRUKTIONEN SKAPA STATISTIK . Det krävs fler systemresurser för att underhålla ett index än ett statistikobjekt. Om programmet inte kräver multikollumnindexet kan du spara på systemresurser genom att skapa statistikobjektet utan att skapa indexet.

När du skapar statistik för flera kolumner påverkar ordningen på kolumnerna i definitionen av statistikobjekt effektiviteten av densiteter för att göra kardinalitetsuppskattningar. Statistikobjektet lagrar tätheter för varje prefix för nyckelkolumner i statistikobjektdefinitionen. Mer information om tätheter finns i avsnittet Densitet på den här sidan.

För att skapa densiteter som är användbara för kardinalitetsuppskattningar måste kolumnerna i frågepredikatet matcha ett av prefixen för kolumner i definitionen av statistikobjektet. I följande exempel skapas till exempel ett statistikobjekt för flera kolumner i kolumnerna LastName, MiddleNameoch FirstName.

USE AdventureWorks2022;
GO

IF EXISTS (SELECT name
           FROM sys.stats
           WHERE name = 'LastFirst'
                 AND object_ID = OBJECT_ID('Person.Person'))
    DROP STATISTICS Person.Person.LastFirst;
GO

CREATE STATISTICS LastFirst
    ON Person.Person(LastName, MiddleName, FirstName);
GO

I det här exemplet har statistikobjektet LastFirst tätheter för följande kolumnprefix: (LastName), (LastName, MiddleName)och (LastName, MiddleName, FirstName). Densiteten är inte tillgänglig för (LastName, FirstName). Om frågan använder LastName och FirstName utan att använda MiddleNameär densiteten inte tillgänglig för kardinalitetsuppskattningar.

Frågeval från en delmängd av data

När Frågeoptimeraren skapar statistik för enskilda kolumner och index skapar den statistik för värdena i alla rader. När frågor väljer från en delmängd rader och den delmängden av rader har en unik datadistribution kan filtrerad statistik förbättra frågeplaner. Du kan skapa filtrerad statistik med hjälp av instruktionen CREATE STATISTICS med WHERE-satsen för att definiera filterpredikatuttrycket.

Med AdventureWorks2022 tillhör till exempel varje produkt i Production.Product tabellen en av fyra kategorier i Production.ProductCategory tabellen: Bikes, Components, Clothingoch Accessories. Var och en av kategorierna har olika datafördelning för vikt: cykelvikter varierar från 13,77 till 30,0, komponentvikterna varierar från 2,12 till 1050,00 med vissa NULL värden, klädvikter är alla NULL, och tillbehörsvikter är också NULL.

Filtrerad Bikes statistik för alla cykelvikter ger till exempel mer exakt statistik till frågeoptimeraren och kan förbättra frågeplanskvaliteten jämfört med fulltabellstatistik eller obefintlig statistik i kolumnen Vikt. Cykelviktskolumnen är en bra kandidat för filtrerad statistik, men inte nödvändigtvis en bra kandidat för ett filtrerat index om antalet viktuppslag är relativt litet. Prestandavinsten för sökningar som ett filtrerat index ger kanske inte uppväger den extra underhålls- och lagringskostnaden för att lägga till ett filtrerat index i databasen.

Följande instruktion skapar den BikeWeights filtrerade statistiken för alla underkategorier för Bikes. Det filtrerade predikatuttrycket definierar cyklar genom att räkna upp alla cykelunderkategorier med jämförelsen Production.ProductSubcategoryID IN (1,2,3). Predikatet Bikes kan inte använda kategorinamnet eftersom det lagras i Production.ProductCategory tabellen och alla kolumner i filteruttrycket måste finnas i samma tabell.

USE AdventureWorks2022;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

Frågeoptimeraren kan använda den BikeWeights filtrerade statistiken för att förbättra frågeplanen för följande fråga som väljer alla cyklar som väger mer än 25.

SELECT P.Weight AS Weight,
       S.Name AS BikeName
FROM Production.Product AS P
     INNER JOIN Production.ProductSubcategory AS S
         ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1, 2, 3)
      AND P.Weight > 25
ORDER BY P.Weight;
GO

Frågan identifierar statistik som saknas

Om ett fel eller en annan händelse hindrar frågeoptimeraren från att skapa statistik skapar Frågeoptimeraren frågeplanen utan att använda statistik. Frågeoptimeraren markerar statistiken som saknad och försöker återskapa statistiken nästa gång frågan körs.

Statistik som saknas anges som varningar (tabellnamn i röd text) när körningsplanen för en fråga visas grafiskt med SQL Server Management Studio. Dessutom visar övervakning av händelseklassen Kolumnstatistik som saknas med hjälp av SQL Server Profiler när statistik saknas. Mer information finns i Händelsekategori för fel och varningar (databasmotor).

Utför följande steg om statistik saknas:

När statistik för en skrivskyddad databas eller skrivskyddad ögonblicksbild saknas eller är inaktuell skapar och underhåller databasmotorn tillfällig statistik i tempdb. När databasmotorn skapar tillfällig statistik läggs statistiknamnet till med suffixet _readonly_database_statistic för att skilja den tillfälliga statistiken från den permanenta statistiken. Suffixet _readonly_database_statistic är reserverat för statistik som genereras av SQL Server. Skript för den tillfälliga statistiken kan skapas och återskapas i en databas med läs- och skrivmöjligheter. Vid skript ändrar Management Studio suffixet för statistiknamnet från _readonly_database_statistic till _readonly_database_statistic_scripted.

Endast SQL Server kan skapa och uppdatera tillfällig statistik. Du kan dock ta bort tillfällig statistik och övervaka statistikegenskaper med samma verktyg som du använder för permanent statistik:

Eftersom tillfällig statistik lagras i tempdbförsvinner en omstart av SQL Server-tjänsten med all tillfällig statistik.

När du ska uppdatera statistik

Frågeoptimeraren avgör när statistiken kan vara inaktuell och uppdaterar dem sedan när de behövs för en frågeplan. I vissa fall kan du förbättra frågeplanen och därmed förbättra frågeprestandan genom att uppdatera statistik oftare än när AUTO_UPDATE_STATISTICS är på. Du kan uppdatera statistik med -instruktionen UPDATE STATISTICS eller den lagrade proceduren sp_updatestats.

Genom att uppdatera statistik ser du till att frågor kompileras med up-to-date-statistik. Uppdatering av statistik via valfri process kan leda till att frågeplaner kompilerar om automatiskt. Vi rekommenderar att du inte uppdaterar statistiken manuellt för ofta eftersom det finns en prestandaavvägning mellan att förbättra frågeplaner och den tid det tar att kompilera om frågor. De specifika kompromisserna beror på ditt program.

När du uppdaterar statistik med UPDATE STATISTICS eller sp_updatestatsrekommenderar vi att du håller AUTO_UPDATE_STATISTICS inställt på PÅ så att Frågeoptimeraren regelbundet uppdaterar statistik.

  • Mer information om hur du uppdaterar statistik för en kolumn, ett index, en tabell eller en indexerad vy finns i UPPDATERA STATISTIK.

  • Information om hur du uppdaterar statistik för alla användardefinierade och interna tabeller i databasen finns i den lagrade proceduren sp_updatestats.

  • Mer information om tröskelvärdena för automatiska statistikuppdateringar finns i AUTO_UPDATE_STATISTICS Alternativ.

När AUTO_UPDATE_STATISTICS är inställt på OFF kan plankompilering fortfarande ske av olika andra orsaker, men inträffar inte automatiskt på grund av inaktuella statistikuppdateringar. När AUTO_UPDATE_STATISTICS är inställt på OFF sker statistikuppdateringar endast via andra manuellt schemalagda processer, till exempel underhållsplaner. Att ställa in AUTO_UPDATE_STATISTICS på OFF kan därför orsaka suboptimala frågeplaner och försämrad frågeprestanda.

Identifiera inaktuell statistik

Om du vill avgöra när statistiken senast uppdaterades använder du funktionerna sys.dm_db_stats_properties eller STATS_DATE .

Överväg att uppdatera statistik för följande villkor:

  • Utförandetiderna för frågor är långa.
  • Infogningsåtgärder utförs i stigande eller fallande nyckelkolumner.
  • Efter underhållsåtgärder.

Exempel på hur du uppdaterar statistik manuellt finns i UPPDATERA STATISTIK.

Frågeexekveringstiderna går långsamt

Om frågesvarstiderna är långsamma eller oförutsägbara kontrollerar du att frågorna har up-to-date-statistik innan du utför ytterligare felsökningssteg.

Infogningsåtgärder utförs i stigande eller fallande nyckelkolumner

Statistik om stigande eller fallande nyckelkolumner, till exempel IDENTITY- eller realtidstidsstämpelkolumner, kan kräva mer frekventa statistikuppdateringar än vad frågeoptimeraren utför. Infoga åtgärder lägger till nya värden i stigande eller fallande kolumner. Antalet rader som läggs till kan vara för litet för att utlösa en statistikuppdatering. Om statistiken inte är up-to- datum och frågor väljer från de senast tillagda raderna, har den aktuella statistiken inte kardinalitetsuppskattningar för dessa nya värden. Detta kan resultera i felaktiga kardinalitetsuppskattningar och långsamma frågeprestanda.

En fråga som väljer från de senaste försäljningsorderdatumen har till exempel felaktiga kardinalitetsuppskattningar om statistiken inte uppdateras för att inkludera kardinalitetsuppskattningar för de senaste försäljningsorderdatumen.

Efter underhållsåtgärder

Överväg att uppdatera statistik efter att ha utfört underhållsprocedurer som ändrar fördelningen av data, till exempel trunkera en tabell eller utföra en massinfogning av en stor procentandel av raderna. Detta kan undvika framtida fördröjningar i frågebearbetningen medan frågor väntar på automatiska statistikuppdateringar.

Åtgärder som att återskapa, defragmentera eller omorganisera ett index ändrar inte fördelningen av data. Därför behöver du inte uppdatera statistiken när du har utfört ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG eller ALTER INDEX REORGANIZE-åtgärder . Frågeoptimeraren uppdaterar statistik när du återskapar ett index i en tabell eller vy med ALTER INDEX REBUILD eller DBCC DBREINDEX, men den här statistikuppdateringen är en biprodukt för att återskapa indexet. Frågeoptimeraren uppdaterar inte statistik efter DBCC INDEXDEFRAG eller ALTER INDEX REORGANIZE åtgärder.

Tips/Råd

Från och med SQL Server 2016 (13.x) SP1 CU4 använder du PERSIST_SAMPLE_PERCENT alternativet SKAPA STATISTIK eller UPPDATERA STATISTIK för att ange och behålla en specifik samplingsprocent för efterföljande statistikuppdateringar som inte uttryckligen anger en samplingsprocent.

Automatisk index- och statistikhantering

Använd smarta lösningar som Adaptive Index Defrag för att automatiskt hantera indexdefragmentering och statistikuppdateringar 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.

Frågor som använder statistik effektivt

Vissa frågeimplementeringar, till exempel lokala variabler och komplexa uttryck i frågepredikatet, kan leda till suboptimala frågeplaner. Att följa riktlinjerna för frågedesign för att använda statistik effektivt kan bidra till att undvika detta. Mer information om frågepredikat finns i Sökvillkor.

Du kan förbättra frågeplaner genom att tillämpa riktlinjer för frågedesign som använder statistik effektivt för att förbättra kardinalitetsuppskattningar för uttryck, variabler och funktioner som används i frågepredikat. När Frågeoptimeraren inte känner till värdet för ett uttryck, en variabel eller en funktion vet den inte vilket värde som ska slås upp i histogrammet och kan därför inte hämta den bästa kardinalitetsuppskattningen från histogrammet. I stället baserar Frågeoptimeraren kardinalitetsuppskattningen på det genomsnittliga antalet rader per distinkt värde för alla samplade rader i histogrammet. Detta leder till suboptimal uppskattning av kardinalitet och kan skada databasfrågeprestanda. Mer information om histogram finns i avsnittet histogram på den här sidan eller sys.dm_db_stats_histogram.

Följande riktlinjer beskriver hur du skriver frågor för att förbättra frågeplaner genom att förbättra kardinalitetsuppskattningar.

Förbättra kardinalitetsuppskattningar för uttryck

Följ dessa riktlinjer för att förbättra kardinalitetsuppskattningar för uttryck:

  • När det är möjligt förenklar du uttryck med konstanter i dem. Frågeoptimeraren utvärderar inte alla funktioner och uttryck som innehåller konstanter innan kardinalitetsuppskattningar fastställs. Du kan till exempel förenkla uttrycket ABS(-100) till 100.
  • Om uttrycket använder flera variabler kan du skapa en beräknad kolumn för uttrycket och sedan skapa statistik eller ett index i den beräknade kolumnen. Frågepredikatet WHERE PRICE + Tax > 100 kan till exempel ha en bättre kardinalitetsuppskattning om du skapar en beräknad kolumn för uttrycket Price + Tax.

Förbättra kardinalitetsuppskattningar för variabler och funktioner

Följ dessa riktlinjer för att förbättra kardinalitetsuppskattningarna för variabler och funktioner:

  • Om frågepredikatet använder en lokal variabel kan du skriva om frågan så att den använder en parameter i stället för en lokal variabel. Värdet för en lokal variabel är inte känt när frågeoptimeraren skapar frågekörningsplanen. När en fråga använder en parameter använder Frågeoptimeraren kardinalitetsuppskattningen för det första faktiska parametervärdet som skickas till den lagrade proceduren.

  • Överväg att använda en standardtabell eller en tillfällig tabell för att hålla resultaten av tabellvärdesfunktioner med flera uttalanden (mstvf). Frågeoptimeraren skapar inte statistik för tabellvärdesfunktioner med fler uttalanden. Med den här metoden kan frågeoptimeraren skapa statistik för tabellkolumnerna och använda dem för att skapa en bättre frågeplan.

  • Överväg att använda en standardtabell eller en tillfällig tabell som ersättning för tabellvariabler. Frågeoptimeraren skapar inte statistik för tabellvariabler. Med den här metoden kan frågeoptimeraren skapa statistik för tabellkolumnerna och använda dem för att skapa en bättre frågeplan. Det finns kompromisser när det gäller att avgöra om en tillfällig tabell eller en tabellvariabel ska användas. Tabellvariabler som används i lagrade procedurer orsakar färre omkompileringar av den lagrade proceduren än temporära tabeller. Beroende på programmet kan det hända att prestanda inte förbättras om du använder en tillfällig tabell i stället för en tabellvariabel.

  • Om en lagrad procedur innehåller en fråga som använder en inledd parameter bör du undvika att ändra parametervärdet i den lagrade proceduren innan du använder den i frågan. Kardinalitetsuppskattningarna för frågan baseras på det angivna parametervärdet och inte det uppdaterade värdet. Om du vill undvika att ändra parametervärdet kan du skriva om frågan för att använda två lagrade procedurer.

    Följande lagrade procedur Sales.GetRecentSales ändrar till exempel värdet för parametern @date när @date är NULL.

    USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO

CREATE PROCEDURE Sales.GetRecentSales
@date DATETIME
AS
BEGIN
    IF @date IS NULL
        SET @date = DATEADD(MONTH, -3,
            (SELECT MAX(ORDERDATE)
            FROM Sales.SalesOrderHeader));
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID
        AND h.OrderDate > @date;
END
GO

    Om det första anropet till den lagrade proceduren Sales.GetRecentSales skickar en NULL för parametern @date kompilerar Frågeoptimeraren den lagrade proceduren med kardinalitetsuppskattningen för @date = NULL även om frågepredikatet inte anropas med @date = NULL. Den här kardinalitetsuppskattningen kan skilja sig avsevärt från antalet rader i det faktiska frågeresultatet. Därför kan frågeoptimeraren välja en suboptimal frågeplan. För att undvika detta kan du skriva om den lagrade proceduren i två procedurer på följande sätt:

    USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.GetNullRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetNullRecentSales;
GO

CREATE PROCEDURE Sales.GetNullRecentSales
@date DATETIME
AS
BEGIN
    IF @date IS NULL
        SET @date = DATEADD(MONTH, -3,
            (SELECT MAX(ORDERDATE)
            FROM Sales.SalesOrderHeader));
    EXECUTE Sales.GetNonNullRecentSales @date;
END
GO

IF OBJECT_ID('Sales.GetNonNullRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetNonNullRecentSales;
GO

CREATE PROCEDURE Sales.GetNonNullRecentSales
@date DATETIME
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID
        AND h.OrderDate > @date;
END
GO

Förbättra kardinalitetsuppskattningar med frågetips

Om du vill förbättra uppskattningar för kardinalitet av lokala variabler kan du använda OPTIMIZE FOR <value> eller OPTIMIZE FOR UNKNOWN frågehintar med RECOMPILE. Mer information finns i Frågetips.

För vissa program kan det ta för lång tid att kompilera om frågan varje gång den körs. Frågetipset OPTIMIZE FOR kan hjälpa även om du inte använder alternativet RECOMPILE . Du kan till exempel lägga till ett OPTIMIZE FOR alternativ i den lagrade proceduren Sales.GetRecentSales för att ange ett visst datum. I följande exempel läggs OPTIMIZE FOR alternativet till i proceduren Sales.GetRecentSales .

USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO

CREATE PROCEDURE Sales.GetRecentSales
@date DATETIME
AS
BEGIN
    IF @date IS NULL
        SET @date = DATEADD(MONTH, -3,
            (SELECT MAX(ORDERDATE)
            FROM Sales.SalesOrderHeader));
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date
    OPTION (OPTIMIZE FOR (@date = '2004-05-01 00:00:00.000'));
END
GO

Förbättra kardinalitetsuppskattningar med planguider

För vissa program kanske riktlinjerna för frågedesign inte gäller eftersom du inte kan ändra frågan eller så kan frågetipset RECOMPILE orsaka för många omkompileringar. Du kan använda planguider för att ange andra tips, till exempel USE PLAN, för att styra frågans beteende när du undersöker programändringar med programleverantören. Mer information om planguider finns i Planguider.

I Azure SQL Database bör du överväga query store-tips för att tvinga fram planer i stället för planguider. Mer information finns i Query Store-tips.

Relaterat innehåll