Kardinaliteitschatting (SQL Server)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric Preview

Sql Server Query Optimizer is een op kosten gebaseerde Query Optimizer. Dit betekent dat er queryplannen worden geselecteerd met de laagste geschatte verwerkingskosten die moeten worden uitgevoerd. De queryoptimalisatie bepaalt de kosten voor het uitvoeren van een queryplan op basis van twee hoofdfactoren:

• Het totale aantal rijen dat wordt verwerkt op elk niveau van een queryplan, aangeduid als de kardinaliteit van het plan.
• Het kostenmodel van het algoritme dat wordt bepaald door de operators die in de query worden gebruikt.

De eerste factor, kardinaliteit, wordt gebruikt als invoerparameter van de tweede factor, het kostenmodel. Verbeterde kardinaliteit leidt daarom tot betere geschatte kosten en op zijn beurt snellere uitvoeringsplannen.

Kardinaliteitschatting (CE) in SQL Server wordt voornamelijk afgeleid van histogrammen die worden gemaakt wanneer indexen of statistieken worden gemaakt, handmatig of automatisch. Soms maakt SQL Server ook gebruik van beperkingsgegevens en logische herschrijven van query's om de kardinaliteit te bepalen.

In de volgende gevallen kan SQL Server kardinaliteit niet nauwkeurig berekenen. Dit veroorzaakt onnauwkeurige kostenberekeningen die suboptimale queryplannen kunnen veroorzaken. Het voorkomen van deze constructies in query's kan de prestaties van query's verbeteren. Soms zijn alternatieve queryformuleringen of andere metingen mogelijk en worden deze opgemerkt:

• Query's met predicaten die gebruikmaken van vergelijkingsoperatoren tussen verschillende kolommen van dezelfde tabel.
• Query's met predicaten die gebruikmaken van operators en een van de volgende zijn waar:
  • Er zijn geen statistieken over de kolommen die aan weerszijden van de operators betrokken zijn.
  • De verdeling van waarden in de statistieken is niet uniform, maar de query zoekt een zeer selectieve waardeset. Deze situatie kan vooral waar zijn als de operator iets anders is dan de gelijkheidsoperator (=).
  • Het predicaat maakt gebruik van de 'niet gelijk aan' (!=) vergelijkingsoperator of de NOT logische operator.
• Query's die gebruikmaken van een van de ingebouwde SQL Server-functies of een scalaire, door de gebruiker gedefinieerde functie waarvan het argument geen constante waarde is.
• Queries waarbij kolommen worden verbonden met rekenkundige of tekenreeksoperatoren.
• Query's waarmee variabelen worden vergeleken waarvan de waarden niet bekend zijn wanneer de query wordt gecompileerd en geoptimaliseerd.

In dit artikel wordt uitgelegd hoe u de beste CE-configuratie voor uw systeem kunt beoordelen en kiezen. De meeste systemen profiteren van de nieuwste CE, omdat deze het meest nauwkeurig is. De CE voorspelt hoeveel rijen uw query waarschijnlijk retourneert. De kardinaliteitsvoorspelling wordt gebruikt door de Query Optimizer om het optimale queryplan te genereren. Met nauwkeurigere schattingen kan de Query Optimizer meestal een betere taak uitvoeren om een optimalere queryplanning te maken.

Uw toepassingssysteem zou mogelijk een belangrijke query kunnen hebben waarvan het plan is gewijzigd naar een langzamere uitvoeringsplan vanwege wijzigingen in de CE door de verschillende versies heen. U hebt technieken en hulpprogramma's voor het identificeren van een query die langzamer wordt uitgevoerd vanwege CE-problemen. En u hebt opties voor het oplossen van de volgende prestatieproblemen.

Versies van de CE

In 1998 maakte een belangrijke update van de CE deel uit van SQL Server 7.0, waarvoor het compatibiliteitsniveau 70 was. Deze versie van het CE-model is ingesteld op vier basisveronderstellingen:

• Onafhankelijkheid: Gegevensdistributies op verschillende kolommen worden verondersteld onafhankelijk van elkaar te zijn, tenzij correlatie-informatie beschikbaar en bruikbaar is.

• Eenvormigheid: Afzonderlijke waarden worden gelijkmatig gespoerd en hebben allemaal dezelfde frequentie. Preciezer gezegd, binnen elke histogramstap worden afzonderlijke waarden gelijkmatig verdeeld en elke waarde heeft dezelfde frequentie.

• Insluiting (eenvoudig): Gebruikers voeren een query uit voor gegevens die bestaan. Voor een gelijkheidsjoin tussen twee tabellen moet u bijvoorbeeld rekening houden met de predicaat selectiviteit¹ in elk invoer histogram, voordat u histogrammen samenvoegt om de join-selectiviteit te schatten.

• Insluiting: Voor filterpredicaten waarbij Column = Constantwordt ervan uitgegaan dat de constante daadwerkelijk bestaat voor de bijbehorende kolom. Als een bijbehorende histogramstap niet leeg is, wordt ervan uitgegaan dat een van de afzonderlijke waarden van de stap overeenkomt met de waarde uit het predicaat.

  ¹ Rijtelling dat aan de voorwaarde voldoet.

Volgende updates zijn gestart met SQL Server 2014 (12.x), wat betekent dat compatibiliteitsniveaus 120 en hoger zijn. De CE-updates voor niveaus 120 en hoger bevatten bijgewerkte veronderstellingen en algoritmen die goed werken voor moderne datawarehousing en oltp-workloads. Vanaf de CE 70-veronderstellingen zijn de volgende modelveronderstellingen gewijzigd vanaf CE 120:

• Onafhankelijkheid wordt Correlatie: de combinatie van de verschillende kolomwaarden is niet noodzakelijkerwijs onafhankelijk. Dit kan lijken op een meer realistische gegevensopvraging.
• Simple Containment wordt Base Containment: gebruikers kunnen query's uitvoeren op gegevens die niet bestaan. Voor een gelijkheidsdeelname tussen twee tabellen gebruiken we bijvoorbeeld de histogrammen van basistabellen om de joinselectiviteit te schatten en vervolgens rekening te houden met de predicatenselectiviteit.

Query Store gebruiken om de CE-versie te evalueren

Vanaf SQL Server 2016 (13.x) is Query Store een handig hulpmiddel voor het onderzoeken van de prestaties van uw query's. Zodra Query Store is ingeschakeld, worden de queryprestaties in de loop van de tijd bijgehouden, zelfs als uitvoeringsplannen veranderen. Bewaak Query Store voor queryprestaties met hoge kosten of terugval. Zie Prestaties bewaken met behulp van de Query Store voor meer informatie.

Als u zich voorbereidt op een upgrade naar SQL Server of als u een databasecompatibiliteitsniveau in een SQL Server-platform wilt promoten, kunt u databases upgraden met behulp van de Assistent Voor het afstemmen van query's. Dit kan helpen bij het vergelijken van queryprestaties in twee verschillende compatibiliteitsniveaus.

Uitgebreide gebeurtenissen gebruiken om de CE-versie te evalueren

Een andere optie voor het bijhouden van het kardinaliteitschattingsproces is het gebruik van de uitgebreide gebeurtenis met de naam query_optimizer_estimate_cardinality. Het volgende Transact-SQL codevoorbeeld wordt uitgevoerd op SQL Server. Er wordt een .xel-bestand naar C:\Temp\ geschreven (hoewel u het pad kunt wijzigen). Wanneer u het .xel-bestand opent in Management Studio, wordt de gedetailleerde informatie op een gebruiksvriendelijke manier weergegeven.

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

Zie Uitgebreide gebeurtenissen in SQL Database voor meer informatie over uitgebreide gebeurtenissen die zijn afgestemd op SQL Database.

Stappen voor het beoordelen van de CE-versie

Hierna volgen de stappen die u kunt gebruiken om te beoordelen of een van uw belangrijkste query's slechter presteert onder de nieuwste CE. Sommige van de stappen worden uitgevoerd door een codevoorbeeld uit te voeren dat wordt weergegeven in een voorgaande sectie.

1. Open SQL Server Management Studio (SSMS). Zorg ervoor dat uw SQL Server-database is ingesteld op het hoogst beschikbare compatibiliteitsniveau.

2. Voer de volgende voorbereidende stappen uit:

   1. Open SQL Server Management Studio (SSMS).

   2. Voer de Transact-SQL uit om ervoor te zorgen dat uw SQL Server-database is ingesteld op het hoogst beschikbare compatibiliteitsniveau.

   3. Zorg ervoor dat de configuratie van LEGACY_CARDINALITY_ESTIMATION uw database is uitgeschakeld OFF.

   4. Wis uw Query Store. Zorg ervoor dat Query Store is ingeschakeld in uw database.

   5. Voer de instructie uit: SET NOCOUNT OFF;

3. Voer de instructie uit: SET STATISTICS XML ON;

4. Voer uw belangrijke query uit.

5. Noteer in het resultatenvenster op het tabblad Berichten het werkelijke aantal rijen dat is beïnvloed.

6. Dubbelklik in het resultatenvenster op het tabblad Resultaten op de cel met de statistieken in XML-indeling. Er wordt een grafisch queryplan weergegeven.

7. Klik met de rechtermuisknop op het eerste vak in het grafische queryplan en selecteer Eigenschappen.

8. Let op de waarden voor de volgende eigenschappen voor een latere vergelijking met een andere configuratie:

   • CardinalityEstimationModelVersion.

   • Geschat aantal rijen.

   • Geschatte I/O-kosten en verschillende vergelijkbare geschatte eigenschappen die betrekking hebben op werkelijke prestaties in plaats van voorspellingen van het aantal rijen.

   • Logische bewerking en fysieke bewerking. Parallellisme is een goede waarde.

   • Werkelijke uitvoeringsmodus. Batch is een goede keuze, beter dan Rij.

9. Vergelijk het geschatte aantal rijen met het werkelijke aantal rijen. Is de CE onnauwkeurig met 1% (hoog of laag) of met 10%?

10. Voer dit uit: SET STATISTICS XML OFF;

11. Voer de Transact-SQL uit om het compatibiliteitsniveau van uw database met één niveau te verlagen (bijvoorbeeld van 130 tot 120).

12. Voer alle niet-voorlopige stappen opnieuw uit.

13. Vergelijk de CE-eigenschapswaarden van de twee uitvoeringen.

    • Is het onnauwkeurigheidspercentage onder de nieuwste CE kleiner dan onder de oudere CE?

14. Vergelijk ten slotte de verschillende prestatie-eigenschapswaarden van de twee uitvoeringen.

    • Heeft uw query een ander plan gebruikt onder de twee verschillende CE-schattingen?

    • Is uw query langzamer uitgevoerd onder de nieuwste CE?

    • Tenzij uw query beter wordt uitgevoerd met een ander plan onder de oudere CE-versie, wilt u bijna zeker de nieuwste versie van CE.

    • Als uw query echter wordt uitgevoerd met een sneller plan onder de oudere CE, kunt u overwegen het systeem te dwingen het snellere plan te gebruiken en de CE te negeren. Op deze manier kunt u de nieuwste CE voor alles hebben, terwijl u het snellere plan in een uitzonderlijk geval behoudt.

Het beste queryplan activeren

Stel dat met CE 120 of hoger een minder efficiënt queryplan wordt gegenereerd voor uw query. Hier volgen enkele opties die u nodig hebt om het betere plan te activeren, geordend van het grootste bereik naar het kleinste:

• U kunt het compatibiliteitsniveau van de database instellen op een waarde die lager is dan de meest recente versie, voor uw hele database.

  • Als u bijvoorbeeld het compatibiliteitsniveau 110 of lager instelt, wordt CE 70 geactiveerd, maar worden alle query's onderworpen aan het vorige CE-model.

  • Bovendien mist het instellen van een lager compatibiliteitsniveau een aantal verbeteringen in de queryoptimalisatie voor de nieuwste versies en is dit van invloed op alle query's op de database.

• U kunt de configuratieoptie voor het databasetoepassingsbereik LEGACY_CARDINALITY_ESTIMATION gebruiken om ervoor te zorgen dat de gehele database de oudere Cardinality Estimator (CE) gebruikt, terwijl andere verbeteringen in de queryoptimalisatie behouden blijven.

• U kunt de query hint LEGACY_CARDINALITY_ESTIMATION gebruiken om een enkele query de oudere CE te laten gebruiken, terwijl andere verbeteringen in de queryoptimizer behouden blijven.

• U kunt de functie voor hints LEGACY_CARDINALITY_ESTIMATION voor Query Store afdwingen om één query te laten gebruiken met de oudere CE zonder de query te wijzigen.

• Dwing een ander plan af met Query Store.

Databasecompatibiliteitsniveau

U kunt ervoor zorgen dat uw database zich op een bepaald niveau bevindt met behulp van de volgende Transact-SQL code voor het compatibiliteitsniveau ALTER DATABASE (Transact-SQL).

SELECT ServerProperty('ProductVersion');
GO

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

Voor bestaande databases die worden uitgevoerd op lagere compatibiliteitsniveaus, zolang de toepassing geen verbeteringen hoeft te gebruiken die alleen beschikbaar zijn in een hoger databasecompatibiliteitsniveau, is het een geldige benadering om het vorige compatibiliteitsniveau van de database te behouden. Voor nieuwe ontwikkelwerkzaamheden of wanneer voor een bestaande toepassing nieuwe functies nodig zijn, zoals intelligente queryverwerking in SQL-databases, evenals een aantal nieuwe Transact-SQL-toepassingen, moet u het databasecompatibiliteitsniveau upgraden naar het meest recente beschikbare. Zie Compatibiliteitsniveaus en Database Engine-upgrades voor meer informatie.

ALTER DATABASE <yourDatabase>
SET COMPATIBILITY_LEVEL = 150;
GO

Voor een SQL Server-database die is ingesteld op compatibiliteitsniveau 120 of hoger, zorgt de activering van de traceringsvlag 9481 ervoor dat het systeem de CE-versie 70 gebruikt.

Verouderde kardinaliteitsschatter

Voor een SQL Server-database die is ingesteld op compatibiliteitsniveau 120 en hoger, kan de verouderde kardinaliteitsschatter (CE-versie 70) op databaseniveau worden geactiveerd met behulp van de 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

Query aanpassen om hint te gebruiken

Begin vanaf SQL Server 2016 (13.x) SP1 de query te wijzigen zodat deze de Query HintUSE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') gebruikt.

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

Een Query Store-hint instellen

Query's kunnen worden gedwongen om de verouderde kardinaliteitsschatter te gebruiken zonder de query te wijzigen, met behulp van Query Store-hints.

1. Identificeer de query in de catalogusweergaven van de sys.query_store_query_text en sys.query_store_query Query Store. Zoek bijvoorbeeld naar een uitgevoerde query op tekstfragment:

   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. In het volgende voorbeeld wordt een Query Store-hint toegepast om de verouderde kardinaliteitsschatter af te dwingen op query_id 39, zonder de query te wijzigen:

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

Hoe een bepaald queryplan afdwingen

Voor de beste controle kunt u afdwingen dat het systeem het plan gebruikt dat tijdens uw test met CE 70 is gegenereerd. Nadat u uw voorkeursplan hebt vastgemaakt , kunt u uw hele database zo instellen dat het meest recente compatibiliteitsniveau en ce worden gebruikt. De optie wordt hierna uitgewerkt.

De Query Store biedt u verschillende manieren waarop u het systeem kunt dwingen een bepaald queryplan te gebruiken:

• Voer sys.sp_query_store_force_planuit.

• Vouw in SQL Server Management Studio (SSMS) uw Query Store-knooppunt uit, klik met de rechtermuisknop op Knooppunten die de belangrijkste resources verbruiken en selecteer vervolgens Topresourcegebruiksknooppunten weergeven. De weergave toont knoppen met het label Force Plan en Unforce Plan.

Zie Prestaties bewaken met behulp van de Query Store voor meer informatie over de Query Store.

Constante vouw- en expressie-evaluatie tijdens cardinaliteitsschatting

De database-engine evalueert enkele constante expressies vroeg om de queryprestaties te verbeteren. Dit wordt ook wel constant vouwen genoemd. Een constante is een letterlijke Transact-SQL, zoals 3, 'ABC', '2005-12-31', 1.0e3of 0x12345678. Zie Constant Folding voor meer informatie.

Daarnaast worden sommige expressies die niet constant zijn gevouwen, maar waarvan de argumenten tijdens het compileren bekend zijn, ongeacht of de argumenten parameters of constanten zijn, geëvalueerd door de cardinaliteit (grootte) van de resultatenset, die deel uitmaakt van de Query Optimizer tijdens de optimalisatie. Zie Expressie-evaluatie voor meer informatie.

Beste praktijken: Het gebruik van constante vouwen en evaluatie van expressies tijdens compilatie voor het genereren van optimale queryplannen

Om ervoor te zorgen dat u optimale queryplannen genereert, kunt u het beste query's, opgeslagen procedures en batches ontwerpen, zodat de Query Optimizer de selectiviteit van de voorwaarden in uw query nauwkeurig kan schatten, op basis van statistieken over uw gegevensdistributie. Anders moet de Query Optimizer een standaardschatting gebruiken bij het schatten van selectiviteit.

Om ervoor te zorgen dat de kardinaliteitsschatter van de queryoptimalisatie goede schattingen biedt, moet u eerst controleren of de opties voor de AUTO_CREATE_STATISTICS database AUTO_UPDATE_STATISTICSSET (de standaardinstelling) zijn ON of dat u handmatig statistieken hebt gemaakt voor alle kolommen waarnaar wordt verwezen in een queryvoorwaarde. Wanneer u vervolgens de voorwaarden in uw query's ontwerpt, gaat u als volgt te werk wanneer dit mogelijk is:

• Vermijd het gebruik van lokale variabelen in query's. Gebruik in plaats daarvan parameters, letterlijke waarden of expressies in de query.

• Beperk het gebruik van operators en functies die zijn ingesloten in een query die een parameter heeft tot die vermeld onder "Compile-Time Expression Evaluation for Cardinality Estimation."

• Zorg ervoor dat expressies met alleen constanten in de voorwaarde van uw query constant kunnen worden gevouwen of dat ze tijdens de compilatie kunnen worden geëvalueerd.

• Als u een lokale variabele moet gebruiken om een expressie te evalueren die in een query moet worden gebruikt, kunt u overwegen deze te evalueren in een ander bereik dan de query. Het kan bijvoorbeeld handig zijn om een van de volgende opties uit te voeren:

  • Geef de waarde van de variabele door aan een opgeslagen procedure die de query bevat die u wilt evalueren en laat de query de procedureparameter gebruiken in plaats van een lokale variabele.

  • Maak een tekenreeks die een query bevat die gedeeltelijk is gebaseerd op de waarde van de lokale variabele en voer vervolgens de tekenreeks uit met behulp van dynamische SQL (EXEC of bij voorkeur sp_executesql).

  • Parameteriseer de query en voer deze uit met behulp van sp_executesql, en geef de waarde van de variabele als parameter door aan de query.

Voorbeelden van CE-verbeteringen

In deze sectie worden voorbeeldquery's beschreven die profiteren van de verbeteringen die in de CE in recente releases zijn geïmplementeerd. Dit is achtergrondinformatie die geen specifieke actie op uw kant aanroept.

Voorbeeld A. CE begrijpt dat de maximumwaarde hoger kan zijn dan wanneer statistieken voor het laatst zijn verzameld

Stel dat de statistieken voor het laatst zijn verzameld OrderTable2016-04-30, wanneer het maximum OrderAddedDate was 2016-04-30. De CE 120 (en de bovenstaande versie) begrijpt dat kolommen met OrderTableoplopende gegevens waarden kunnen hebben die groter zijn dan het maximum dat door de statistieken is vastgelegd. Dit inzicht verbetert het queryplan voor Transact-SQL SELECT instructies, zoals de volgende.

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

Voorbeeld B. CE begrijpt dat gefilterde predicaten in dezelfde tabel vaak worden gecorreleerd

In het volgende SELECT zien we gefilterde predicaten op Model en ModelVariant. We begrijpen intuïtief dat wanneer Model 'Xbox' er een kans is dat de ModelVariant 'One' is, gezien het feit dat Xbox een variant heeft genaamd One.

Vanaf CE 120 begrijpt SQL Server dat er mogelijk een correlatie is tussen de twee kolommen in dezelfde tabel en ModelModelVariant. De CE maakt een nauwkeurigere schatting van het aantal rijen dat door de query wordt geretourneerd en de queryoptimalisatie genereert een beter plan.

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

Voorbeeld C. CE gaat niet langer uit van een correlatie tussen gefilterde predicaten uit verschillende tabellen

Uitgebreid nieuw onderzoek naar moderne workloads en werkelijke zakelijke gegevens laat zien dat predicaatfilters uit verschillende tabellen meestal niet met elkaar correleren. In de volgende query gaat de CE ervan uit dat er geen correlatie is tussen s.type en r.date. Daarom maakt de CE een lagere schatting van het aantal geretourneerde rijen.

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

Verwante inhoud

• feedback over kardinaliteitschatting (CE)
• Prestaties bewaken en afstemmen voor betere resultaten
• Uw queryplannen optimaliseren met de SQL Server 2014-kardinaliteitsschatter
• queryhints (Transact-SQL)
• HINT-queryhints GEBRUIKEN
• Databases upgraden met behulp van de queryafstemmingsassistent
• Prestaties controleren via de Query Store
• architectuurhandleiding voor verwerking van query's
• Query Store-hints
• Intelligent queryverwerking in SQL-databases
• Traceringsvlagmen instellen met DBCC TRACEON (Transact-SQL)
• sys.query_store_query_hints