Kardinalitetsuppskattning (SQL Server)

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Förhandsversion av Microsoft Fabric

SQL Server Query Optimizer är en kostnadsbaserad frågeoptimerare. Det innebär att den väljer frågeplaner som har den lägsta uppskattade bearbetningskostnaden för att köra. Frågeoptimeraren avgör kostnaden för att köra en frågeplan baserat på två huvudfaktorer:

• Det totala antalet rader som bearbetas på varje nivå i en frågeplan, vilket kallas kardinaliteten för planen.
• Kostnadsmodellen för algoritmen som dikteras av de operatorer som används i frågan.

Den första faktorn, kardinalitet, används som indataparameter för den andra faktorn, kostnadsmodellen. Därför leder förbättrad kardinalitet till bättre estimerade kostnader och i sin tur snabbare exekveringsplaner.

Kardinalitetsuppskattning (CE) i SQL Server härleds främst från histogram som skapas när index eller statistik skapas, antingen manuellt eller automatiskt. Ibland använder SQL Server även villkorsinformation och logiska omskrivningar av frågor för att fastställa kardinalitet.

I följande fall kan SQL Server inte beräkna kardinaliteter korrekt. Detta orsakar felaktiga kostnadsberäkningar som kan orsaka suboptimala frågeplaner. Om du undviker dessa konstruktioner i frågor kan det förbättra frågeprestandan. Ibland är alternativa frågeformuleringar eller andra mått möjliga och dessa påpekas:

• Frågor med predikat som använder jämförelseoperatorer mellan olika kolumner i samma tabell.
• Frågor med predikat som använder operatorer och något av följande är sant:
  • Det finns ingen statistik för de kolumner som berörs på någondera sidan av operatorerna.
  • Fördelningen av värden i statistiken är inte enhetlig, men frågan söker efter en mycket selektiv värdeuppsättning. Den här situationen kan vara särskilt sann om operatorn är något annat än likhetsoperatorn (=).
  • Predikatet använder inte lika med (!=) jämförelseoperatorn eller den NOT logiska operatorn.
• Frågor som använder någon av de inbyggda SQL Server-funktionerna eller en skalvärdesbaserad, användardefinierad funktion vars argument inte är ett konstant värde.
• Frågor som omfattar koppling av kolumner via aritmetiska operatorer eller strängsammanfogningsoperatorer.
• Frågor som jämför variabler vars värden inte är kända när frågan kompileras och optimeras.

Den här artikeln visar hur du kan utvärdera och välja den bästa CE-konfigurationen för systemet. De flesta system drar nytta av den senaste CE-koden eftersom den är den mest exakta. CE förutsäger hur många rader din fråga sannolikt kommer att returnera. Kardinalitetsförutsägelse används av frågeoptimeraren för att generera den optimala frågeplanen. Med mer exakta uppskattningar kan frågeoptimeraren vanligtvis göra ett bättre jobb med att skapa en mer optimal frågeplan.

Ditt programsystem kan eventuellt ha en viktig fråga vars plan ändras till en långsammare plan på grund av ändringar i CE i alla versioner. Du har tekniker och verktyg för att identifiera en fråga som går långsammare på grund av CE-problem. Och du har alternativ för hur du kan hantera de efterföljande prestandaproblemen.

Versioner av CE

År 1998 var en stor uppdatering av CE en del av SQL Server 7.0, för vilken kompatibilitetsnivån var 70. Den här versionen av CE-modellen är inställd på fyra grundläggande antaganden:

• Oberoende: Datadistributioner på olika kolumner antas vara oberoende av varandra, såvida inte korrelationsinformation är tillgänglig och användbar.

• Likformighet: Distinkta värden är jämnt fördelade och att de alla har samma frekvens. Mer exakt sprids distinkta värden jämnt inom varje histogramsteg och varje värde har samma frekvens.

• Inneslutning (enkel): Användare frågar efter data som finns. För en jämlikar koppling mellan två tabeller kan du till exempel räkna in predikatens selectivity¹ i varje indatashistogram innan histogrammen ansluts för att uppskatta kopplingsselektiviteten.

• Inkludering: För filterpredikat där Column = Constant anses konstanten faktiskt existera för den associerade kolumnen. Om ett motsvarande histogramsteg inte är tomt antas ett av stegets distinkta värden matcha värdet från predikatet.

  ¹ radantal som uppfyller predikatet.

Efterföljande uppdateringar började med SQL Server 2014 (12.x), vilket innebär kompatibilitetsnivåer 120 och senare. CE-uppdateringarna för nivåerna 120 och senare innehåller uppdaterade antaganden och algoritmer som fungerar bra med modern datalagerhantering och OLTP-arbetsbelastningar. Från CE 70-antagandena ändrades följande modellantaganden från och med CE 120:

• Oberoende blir Korrelation: Kombinationen av de olika kolumnvärdena är inte nödvändigtvis oberoende. Detta kan likna mer verkliga datafrågor.
• Enkel inneslutning blir grundläggande inneslutning: Användare kan fråga efter data som inte finns. För en jämlikhetskoppling mellan två tabeller använder vi till exempel bastabellers histogram för att uppskatta urvalsväljbarheten och sedan ta hänsyn till predikatens selektivitet.

Använda Query Store för att utvärdera CE-versionen

Från och med SQL Server 2016 (13.x) är Query Store ett praktiskt verktyg för att undersöka prestanda för dina frågor. När Query Store har aktiverats börjar det spåra frågeprestanda över tid, även om körningsplanerna ändras. Övervaka Query Store för högkostnads- eller regresserad frågeprestanda. Mer information finns i Övervaka prestanda med hjälp av Query Store.

Om du förbereder en uppgradering till SQL Server eller främjar en databaskompatibilitetsnivå på valfri SQL Server-plattform kan du överväga att uppgradera databaser med hjälp av Frågejusteringsassistenten, vilket kan hjälpa dig att jämföra frågeprestanda på två olika kompatibilitetsnivåer.

Använda utökade händelser för att utvärdera CE-versionen

Ett annat alternativ för att spåra kardinalitetsuppskattningsprocessen är att använda den utökade händelsen med namnet query_optimizer_estimate_cardinality. Följande Transact-SQL kodexempel körs på SQL Server. Skriver en .xel-fil till C:\Temp\ (även om du kan ändra filväg). När du öppnar .xel-filen i Management Studio visas dess detaljerade information på ett användarvänligt sätt.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;
go

CREATE EVENT SESSION Test_the_CE_qoec_1
ON SERVER
ADD EVENT sqlserver.query_optimizer_estimate_cardinality
 (
 ACTION (sqlserver.sql_text)
  WHERE (
  sql_text LIKE '%yourTable%'
  and sql_text LIKE '%SUM(%'
  )
 )
ADD TARGET package0.asynchronous_file_target
 (SET
  filename = 'c:\temp\xe_qoec_1.xel',
  metadatafile = 'c:\temp\xe_qoec_1.xem'
 );
GO

ALTER EVENT SESSION Test_the_CE_qoec_1
ON SERVER
STATE = START;  --STOP;
GO

Information om utökade händelser som är skräddarsydda för SQL Database finns i Utökade händelser i SQL Database.

Steg för att utvärdera CE-versionen

Härnäst följer steg som du kan använda för att utvärdera om någon av dina viktigaste frågor presterar sämre under den senaste CE-koden. Några av stegen utförs genom att köra ett kodexempel som visas i ett föregående avsnitt.

1. Öppna SQL Server Management Studio (SSMS). Kontrollera att SQL Server-databasen är inställd på den högsta tillgängliga kompatibilitetsnivån.

2. Utför följande preliminära steg:

   1. Öppna SQL Server Management Studio (SSMS).

   2. Kör Transact-SQL för att säkerställa att SQL Server-databasen är inställd på den högsta tillgängliga kompatibilitetsnivån.

   3. Kontrollera att databasen har sin LEGACY_CARDINALITY_ESTIMATION konfiguration aktiverad OFF.

   4. Rensa frågearkivet. Kontrollera att Query Store är PÅ i databasen.

   5. Kör kommandot: SET NOCOUNT OFF;

3. Kör kommandot: SET STATISTICS XML ON;

4. Kör din viktiga sökfråga.

5. Observera det faktiska antalet rader som påverkas på fliken Meddelanden i resultatfönstret.

6. Dubbelklicka på cellen som innehåller statistiken i XML-format i resultatfönstret på fliken Resultat . En grafisk frågeplan visas.

7. Högerklicka på den första rutan i den grafiska frågeplanen och välj sedan Egenskaper.

8. Observera värdena för följande egenskaper för senare jämförelse med en annan konfiguration:

   • CardinalityEstimationModelVersion.

   • Uppskattat antal rader.

   • Uppskattad I/O-kostnad och flera liknande uppskattade egenskaper som omfattar faktiska prestanda snarare än förutsägelser för radantal.

   • Logisk åtgärd och fysisk åtgärd. Parallellitet är ett bra värde.

   • Verkligt exekveringsläge Batch är ett bra värde, bättre än Rad.

9. Jämför det uppskattade antalet rader med det faktiska antalet rader. Är CE felaktig med 1% (hög eller låg), eller med 10%?

10. Kör: SET STATISTICS XML OFF;

11. Kör Transact-SQL för att minska databasens kompatibilitetsnivå med en nivå (till exempel från 130 ned till 120).

12. Kör alla icke-preliminära steg igen.

13. Jämför CE-egenskapsvärdena från de två körningarna.

    • Är felaktighetsprocenten under den nyaste CE mindre än under äldre CE?

14. Jämför slutligen de olika prestandaegenskapsvärdena från de två körningarna.

    • Använde din fråga en annan plan under de två olika CE-uppskattningarna?

    • Gick din fråga långsammare under den senaste kompatibilitetsversionen?

    • Om din fråga inte körs bättre med en annorlunda plan under den äldre CE:n, vill du nästan säkert ha den senaste CE:n.

    • Men om frågan körs med en snabbare plan under äldre CE kan du överväga att tvinga systemet att använda den snabbare planen och ignorera CE. På så sätt kan du ha den senaste CE-versionen för allt, samtidigt som du har den snabbare planen i de speciella fallen.

Så här aktiverar du den bästa frågeplanen

Anta att med CE 120 eller senare genereras en mindre effektiv frågeplan för din fråga. Här följer några alternativ för att aktivera den bättre planen, ordnade från det största omfånget till det minsta:

• Du kan ange databasens kompatibilitetsnivå till ett värde som är lägre än det senaste tillgängliga för hela databasen.

  • Om du till exempel anger kompatibilitetsnivå 110 eller lägre aktiveras CE 70, men alla frågor omfattas av den tidigare CE-modellen.

  • Dessutom missar inställningen av en lägre kompatibilitetsnivå ett antal förbättringar i frågeoptimeraren för de senaste versionerna och påverkar alla frågor mot databasen.

• Du kan använda LEGACY_CARDINALITY_ESTIMATION konfigurationsalternativet med databasomfattning för att låta hela databasen använda den äldre CE-koden, samtidigt som du behåller andra förbättringar i frågeoptimeraren.

• Du kan använda LEGACY_CARDINALITY_ESTIMATION frågetips för att få en enskild fråga att använda den äldre CE-koden, samtidigt som du behåller andra förbättringar i frågeoptimeraren.

• Du kan framtvinga LEGACY_CARDINALITY_ESTIMATION tipsfunktionen via Query Store om du vill att en enskild fråga ska använda den äldre CE:en utan att ändra frågan.

• Tvinga fram en annan plan med Query Store.

Databaskompatibilitetsnivå

Du kan se till att databasen är på en viss nivå med hjälp av följande Transact-SQL kod för ALTER DATABASE (Transact-SQL) kompatibilitetsnivå.

SELECT ServerProperty('ProductVersion');
GO

SELECT d.name, d.compatibility_level
FROM sys.databases AS d
WHERE d.name = 'yourDatabase';
GO

För befintliga databaser som körs på lägre kompatibilitetsnivåer, så länge programmet inte behöver använda förbättringar som endast är tillgängliga på en högre databaskompatibilitetsnivå, är det en giltig metod för att upprätthålla den tidigare databaskompatibilitetsnivån. För nytt utvecklingsarbete, eller när ett befintligt program kräver användning av nya funktioner, till exempel Intelligent frågebearbetning i SQL-databaser, samt några nya Transact-SQL, planerar du att uppgradera databasens kompatibilitetsnivå till den senaste tillgängliga. Mer information finns i Kompatibilitetsnivåer och Uppgraderingar av databasmotorn.

ALTER DATABASE <yourDatabase>
SET COMPATIBILITY_LEVEL = 150;
GO

För en SQL Server-databas som angetts på kompatibilitetsnivå 120 eller senare tvingar aktiveringen av spårningsflaggan 9481 systemet att använda CE-version 70.

Äldre kardinalitetsestimator

För en SQL Server-databas som angetts på kompatibilitetsnivå 120 och senare kan den äldre kardinalitetsberäknaren (CE version 70) aktiveras på databasnivå med hjälp av ALTER DATABASE SCOPED CONFIGURATION.

ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO

SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
GO

Ändra fråga så att den använder tips

Från och med SQL Server 2016 (13.x) SP1 ändrar du frågan så att den använder frågetipsetUSE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

SELECT CustomerId, OrderAddedDate
FROM OrderTable
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Ange ett Query Store-tips

Frågor kan tvingas använda den äldre kardinalitetsestimatorn utan att ändra frågan med hjälp av Query Store-tips.

1. Identifiera frågan i katalogvyerna sys.query_store_query_text och sys.query_store_query Query Store. Du kan till exempel söka efter en körd fråga efter textfragment:

   SELECT q.query_id, qt.query_sql_text
   FROM sys.query_store_query_text qt
   INNER JOIN sys.query_store_query q ON
   qt.query_text_id = q.query_text_id
   WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'
   AND query_sql_text not like N'%query_store%';
   

2. I följande exempel används ett Query Store-tips för att tvinga den äldre kardinalitetsberäknaren på query_id 39, utan att ändra frågan:

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

Tvinga fram en viss frågeplan

För den bästa kontrollen kan du tvinga systemet att använda den plan som genererades med CE 70 under testningen. När du har fäst din föredragna plan kan du ange att hela databasen ska använda den senaste kompatibilitetsnivån och CE. Alternativet beskrivs härnäst.

Query Store ger dig olika sätt som du kan tvinga systemet att använda en viss frågeplan:

• Kör sys.sp_query_store_force_plan.

• I SQL Server Management Studio (SSMS) expanderar du noden Query Store , högerklickar på De främsta resurskrävande noderna och väljer sedan Visa de mest resurskrävande noderna. I displayen visas knappar med etiketten Force Plan och Unforce Plan.

Mer information om Query Store finns i Övervaka prestanda med hjälp av Query Store.

Utvärdering av konstant vikning och uttryck under kardinalitetsuppskattning

Databasmotorn utvärderar vissa konstanta uttryck tidigt för att förbättra frågeprestandan. Detta kallas konstant vikning. En konstant är en Transact-SQL literal, till exempel 3, 'ABC', '2005-12-31', 1.0e3eller 0x12345678. Mer information finns i Konstant vikning.

Dessutom utvärderas vissa uttryck som inte är konstant vikta men vars argument är kända vid kompileringstidpunkten, oavsett om argumenten är parametrar eller konstanter, av den resultatuppsättningsstorlek (kardinalitet) som ingår i frågeoptimeraren under optimeringen. Mer information finns i Utvärdering av uttryck.

Metodtips: Använda konstantfällning och utvärdering av uttryck vid kompilerings-tid för att generera optimala frågeplaner

För att säkerställa att du genererar optimala frågeplaner är det bäst att utforma frågor, lagrade procedurer och batchar så att frågeoptimeraren kan uppskatta villkorens selektivitet i frågan, baserat på statistik om din datadistribution. Annars måste frågeoptimeraren använda en standarduppskattning när selektivitet beräknas.

För att säkerställa att kardinalitetsestimatorn för frågeoptimeraren ger bra uppskattningar bör du först se till att databasalternativen AUTO_CREATE_STATISTICSAUTO_UPDATE_STATISTICS och SET är ON (standardinställningen) eller att du har skapat statistik manuellt för alla kolumner som refereras i ett frågevillkor. När du sedan utformar villkoren i dina frågor gör du följande när det är möjligt:

• Undvik att använda lokala variabler i frågor. Använd i stället parametrar, literaler eller uttryck i frågan.

• Begränsa användningen av operatorer och funktioner som är inbäddade i en fråga som innehåller en parameter till de som listas under Uttrycksutvärdering vid Kompileringstid för kardinalitetsuppskattning.

• Kontrollera att konstanta uttryck i frågans villkor antingen är konstant vikbara eller kan utvärderas vid kompileringstillfället.

• Om du måste använda en lokal variabel för att utvärdera ett uttryck som ska användas i en fråga bör du överväga att utvärdera det i ett annat omfång än frågan. Det kan till exempel vara bra att utföra något av följande alternativ:

  • Skicka värdet för variabeln till en lagrad procedur som innehåller den fråga som du vill utvärdera och låt frågan använda procedurparametern i stället för en lokal variabel.

  • Skapa en sträng som innehåller en fråga som delvis baseras på värdet för den lokala variabeln och kör sedan strängen med hjälp av dynamisk SQL (EXEC eller helst sp_executesql).

  • Parametrera sökfrågan och kör den med hjälp av sp_executesql samt skicka variabelns värde som en parameter till sökfrågan.

Exempel på CE-förbättringar

I det här avsnittet beskrivs exempelfrågor som drar nytta av de förbättringar som implementerats i CE i de senaste versionerna. Det här är bakgrundsinformation som inte kräver specifika åtgärder från din sida.

Exempel A. CE förstår att det maximala värdet kan vara högre än när statistik senast samlades in

Anta att statistik senast samlades in för OrderTable den 2016-04-30, när maxvärdet OrderAddedDate var 2016-04-30. CE 120 (och senare version) förstår att kolumner i OrderTable, som har stigande data, kan ha värden som är större än det högsta som registrerats av statistiken. Den här förståelsen förbättrar frågeplanen för Transact-SQL-instruktioner SELECT , till exempel följande.

SELECT CustomerId, OrderAddedDate
FROM OrderTable
WHERE OrderAddedDate >= '2016-05-01';

Exempel B. CE förstår att filtrerade predikat i samma tabell ofta är korrelerade

I följande SELECT ser vi filtrerade predikat på Model och ModelVariant. Vi förstår intuitivt att när Model är "Xbox" finns det en chans att ModelVariant är "One", med tanke på att Xbox har en variant som heter One.

Från och med CE 120 förstår SQL Server att det kan finnas en korrelation mellan de två kolumnerna i samma tabell Model och ModelVariant. CE gör en mer exakt uppskattning av hur många rader som returneras av frågan, och frågeoptimeraren genererar en mer optimal plan.

SELECT Model, Purchase_Price
FROM dbo.Hardware
WHERE Model = 'Xbox' AND
ModelVariant = 'Series X';

Exempel C. CE förutsätter inte längre någon korrelation mellan filtrerade predikater från olika tabeller

Omfattande ny forskning om moderna arbetsbelastningar och faktiska affärsdata visar att predikatfilter från olika tabeller vanligtvis inte korrelerar med varandra. I följande fråga förutsätter CE att det inte finns någon korrelation mellan s.type och r.date. Därför gör CE en lägre uppskattning av antalet rader som returneras.

SELECT s.ticket, s.customer, r.store
FROM dbo.Sales AS s
CROSS JOIN dbo.Returns AS r
WHERE s.ticket = r.ticket AND
s.type = 'toy' AND
r.date = '2016-05-11';

Relaterat innehåll

• feedback om kardinalitetsuppskattning (CE)
• Övervaka och finjustera prestanda
• optimera dina frågeplaner med SQL Server 2014 Cardinality Estimator
• Frågetips (Transact-SQL)
• ANVÄNDA TIPS– frågetips
• Uppgradera databaser med hjälp av Frågejusteringsassistenten
• Övervaka prestanda med hjälp av Query Store
• Arkitekturguide för frågebearbetning
• Query Store-anvisningar
• Intelligent frågebearbetning i SQL-databaser
• Ange spårningsflaggor med DBCC TRACEON (Transact-SQL)
• sys.query_store_query_hints