Delen via

Statistiek

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-database in Microsoft Fabric

Query Optimizer maakt gebruik van statistieken om queryplannen te maken die de queryprestaties verbeteren. Voor de meeste query's genereert de Query Optimizer al de benodigde statistieken voor een queryplan van hoge kwaliteit; in sommige gevallen moet u extra statistieken maken of het queryontwerp wijzigen voor de beste resultaten. In dit artikel worden de concepten van statistieken besproken en worden richtlijnen gegeven voor het effectief gebruiken van queryoptimalisatiestatistieken.

Onderdelen en concepten

Statistiek

Statistieken voor queryoptimalisatie zijn binaire grote objecten (BLOBs) die statistische informatie bevatten over de verdeling van waarden in een of meer kolommen van een tabel of geïndexeerde weergave. De queryoptimalisatie gebruikt deze statistieken om de kardinaliteit of het aantal rijen in het queryresultaat te schatten. Met deze kardinaliteitschattingen kan de Query Optimizer een queryplan van hoge kwaliteit maken. Afhankelijk van uw predicaten kan de Query Optimizer bijvoorbeeld kardinaliteitschattingen gebruiken om de operator voor indexzoeken te kiezen in plaats van de resource-intensieve indexscanoperator, als dit de queryprestaties verbetert.

Elk statistiekenobject wordt gemaakt in een lijst met een of meer tabelkolommen en bevat een histogram met de verdeling van waarden in de eerste kolom. Statistiekenobjecten op meerdere kolommen slaan ook statistische informatie op over de correlatie van waarden tussen de kolommen. Deze correlatiestatistieken, of densiteiten, zijn afgeleid van het aantal afzonderlijke rijen met kolomwaarden.

Histogram

Een histogram meet de frequentie van het optreden voor elke afzonderlijke waarde in een gegevensset. De query-optimalisator berekent een histogram van de kolomwaarden in de eerste sleutelkolom van het statistiekobject. Hierbij worden de kolomwaarden geselecteerd door statistisch de rijen te bemonsteren of door een volledige scan van alle rijen in de tabel of weergave uit te voeren. Als het histogram wordt gemaakt op basis van een steekproefset rijen, zijn de opgeslagen totalen voor het aantal rijen en het aantal afzonderlijke waarden schattingen en hoeven ze geen gehele gehele getallen te zijn.

Opmerking

Histogrammen in SQL Server zijn alleen gebouwd voor één kolom, de eerste kolom in de set sleutelkolommen van het statistiekenobject.

Als u het histogram wilt maken, sorteert de queryoptimalisatie de kolomwaarden, berekent u het aantal waarden dat overeenkomt met elke afzonderlijke kolomwaarde en voegt u vervolgens de kolomwaarden samen tot maximaal 200 aaneengesloten histogramstappen. Elke histogramstap bevat een bereik van kolomwaarden, gevolgd door een bovengrenskolomwaarde. Het bereik bevat alle mogelijke kolomwaarden tussen grenswaarden, met uitzondering van de grenswaarden zelf. Het laagste van de gesorteerde kolomwaarden is de bovenste grenswaarde voor de eerste histogramstap.

In meer detail maakt SQL Server het histogram op basis van de gesorteerde set kolomwaarden in drie stappen:

  • Histogram initialisatie: In de eerste stap wordt een reeks waarden die beginnen bij het begin van de gesorteerde set verwerkt en worden maximaal 200 waarden van range_high_key, equal_rows, range_rows en distinct_range_rows verzameld (range_rows en distinct_range_rows zijn altijd nul tijdens deze stap). De eerste stap eindigt wanneer alle invoer is uitgeput of wanneer er 200 waarden zijn gevonden.
  • Scannen met bucket samenvoegen: Elke extra waarde uit de voorloopkolom van de statistiekensleutel wordt verwerkt in de tweede stap, in gesorteerde volgorde; elke opeenvolgende waarde wordt toegevoegd aan het laatste bereik of er wordt een nieuw bereik aan het einde gemaakt (dit is mogelijk omdat de invoerwaarden worden gesorteerd). Als er een nieuw bereik wordt gemaakt, wordt één paar bestaande, aangrenzende bereiken samengevouwen tot één enkel bereik. Dit paar reeksen is geselecteerd om informatieverlies te minimaliseren. Deze methode maakt gebruik van een maximaal verschil-algoritme om het aantal stappen in het histogram te minimaliseren en tegelijkertijd het verschil tussen de grenswaarden te maximaliseren. Het aantal stappen na het samenvouwen van de intervallen blijft gedurende deze stap 200.
  • Samenvoeging van histogram: in de derde stap kunnen meer bereik worden samengevoegd als er geen aanzienlijke hoeveelheid informatie verloren gaat. Het aantal histogramstappen kan kleiner zijn dan het aantal afzonderlijke waarden, zelfs voor kolommen met minder dan 200 grenspunten. Dus zelfs als de kolom meer dan 200 unieke waarden heeft, kan het histogram minder dan 200 stappen hebben. Voor een kolom die alleen uit unieke waarden bestaat, heeft het geconsolideerde histogram minimaal drie stappen.

Opmerking

Als het histogram is gebouwd met behulp van een steekproef in plaats van een volledige scan, worden de waarden van equal_rows, range_rows en distinct_range_rows en average_range_rows geschat en hoeven ze daarom geen gehele gehele getallen te zijn.

In het volgende diagram ziet u een histogram met zes stappen. Het gebied links van de eerste bovengrenswaarde is de eerste stap.

Diagram van hoe een histogram wordt berekend op basis van waarden in de steekproefkolom.

Voor elke histogramstap in het vorige voorbeeld:

  • De vetgedrukte lijn geeft de bovengrenswaarde (range_high_key) aan en het aantal keren dat deze waarde voorkomt (equal_rows)

  • Effen gebied links van range_high_key vertegenwoordigt het bereik van kolomwaarden en het gemiddelde aantal keren dat elke kolomwaarde voorkomt (average_range_rows). De average_range_rows voor de eerste histogramstap is altijd 0.

  • Stippellijnen vertegenwoordigen de voorbeeldwaarden die worden gebruikt om het totale aantal afzonderlijke waarden in het bereik (distinct_range_rows) en het totale aantal waarden in het bereik (range_rows) te schatten. Query Optimizer gebruikt range_rows en distinct_range_rows om average_range_rows te berekenen en slaat de voorbeeldwaarden niet op.

Dichtheidsvector

Dichtheid is informatie over het aantal duplicaten in een bepaalde kolom of combinatie van kolommen en wordt berekend als 1/(aantal afzonderlijke waarden). Query Optimizer maakt gebruik van densiteiten om kardinaliteitschattingen te verbeteren voor query's die meerdere kolommen retourneren uit dezelfde tabel of geïndexeerde weergave. Naarmate de dichtheid afneemt, neemt de selectiviteit van een waarde toe. In een tabel die auto's vertegenwoordigt, hebben veel auto's bijvoorbeeld dezelfde fabrikant, maar elke auto heeft een uniek voertuigidentificatienummer (VIN). Een index van de VIN is selectiever dan een index van de fabrikant, omdat VIN een lagere dichtheid heeft dan de fabrikant.

Opmerking

Frequentie is informatie over het voorkomen van elke afzonderlijke waarde in de eerste sleutelkolom van het statistiekenobject en wordt berekend als row count * density. Een maximale frequentie van 1 vindt u in kolommen met unieke waarden.

De dichtheidsvector bevat één dichtheid voor elk voorvoegsel van kolommen in het statistiekenobject. Als een statistiekenobject bijvoorbeeld de sleutelkolommen CustomerIdItemId bevat en Pricede dichtheid wordt berekend op elk van de volgende kolomvoorvoegsels.

Kolomvoorvoegsel Dichtheid berekend op
(CustomerId) Rijen met overeenkomende waarden voor CustomerId
(CustomerId, ItemId) Rijen met overeenkomende waarden voor CustomerId en ItemId
(CustomerId, ItemId, Price) Rijen met overeenkomende waarden voor CustomerId, ItemIden Price

Gefilterde statistieken

Gefilterde statistieken kunnen de queryprestaties verbeteren voor query's die kiezen uit goed gedefinieerde subsets van gegevens. Gefilterde statistieken gebruiken een filterpredicaat om de subset te selecteren van gegevens die zijn opgenomen in de statistieken. Goed ontworpen gefilterde statistieken kunnen het uitvoeringsplan voor query's verbeteren vergeleken met volledige tabelstatistieken. Zie CREATE STATISTICS voor meer informatie over het filterpredicaat. Zie de sectie Wanneer statistieken maken in dit artikel voor meer informatie over wanneer u gefilterde statistieken maakt.

Opties voor statistieken

Er zijn opties die van invloed zijn op wanneer en hoe statistieken worden gemaakt en bijgewerkt. Deze opties kunnen alleen op databaseniveau worden geconfigureerd.

AUTO_CREATE_STATISTICS optie

Wanneer de optie voor het automatisch maken van statistieken , AUTO_CREATE_STATISTICS is ingeschakeld, maakt de queryoptimalisatie statistieken over afzonderlijke kolommen in het querypredicaat, indien nodig, om de kardinaliteitschattingen voor het queryplan te verbeteren. Deze statistieken met één kolom worden gemaakt op kolommen die nog geen histogram hebben in een bestaand statistiekenobject. De AUTO_CREATE_STATISTICS optie bepaalt niet of statistieken worden gemaakt voor indexen. Met deze optie worden ook geen gefilterde statistieken gegenereerd. Het is strikt van toepassing op statistieken met één kolom voor de volledige tabel.

Wanneer de queryoptimalisatie statistieken maakt als gevolg van het gebruik van de AUTO_CREATE_STATISTICS optie, begint de naam van de statistieken met _WA. U kunt de volgende query gebruiken om te bepalen of de queryoptimalisatie statistieken heeft gemaakt voor een querypredicaatkolom.

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 optie

Wanneer de optie AUTO_UPDATE_STATISTICS is ingeschakeld, bepaalt de Query Optimizer of statistieken mogelijk verouderd zijn en worden ze vervolgens bijgewerkt wanneer ze door een query worden gebruikt. Deze actie wordt ook wel statistiekenhercompilatie genoemd. Statistieken worden verouderd nadat wijzigingen van invoeg-, update-, verwijder- of samenvoegbewerkingen de gegevensdistributie in de tabel of geïndexeerde weergave wijzigen. De queryoptimalisatie bepaalt wanneer statistieken mogelijk verouderd zijn door het aantal rijwijzigingen te tellen sinds de laatste update van de statistieken en het aantal rijwijzigingen te vergelijken met een drempelwaarde. De drempelwaarde is gebaseerd op de tabelkardinaliteit, die kan worden gedefinieerd als het aantal rijen in de tabel- of geïndexeerde weergave.

Het markeren van statistieken als verouderd op basis van rijwijzigingen vindt plaats, zelfs wanneer de AUTO_UPDATE_STATISTICS optie UIT is. Wanneer de AUTO_UPDATE_STATISTICS optie UIT is, worden statistieken niet bijgewerkt, zelfs niet wanneer ze zijn gemarkeerd als verouderd. Plannen maken nog steeds gebruik van de verouderde statistiekenobjecten. Als AUTO_UPDATE_STATISTICS op UIT staat gezet, kan dit suboptimale queryplannen en verminderde queryprestaties veroorzaken. Het instellen van de optie AUTO_UPDATE STATISTICS op AAN wordt aanbevolen.

  • Tot SQL Server 2014 (12.x) gebruikt de database-engine een drempelwaarde voor opnieuw compileren op basis van het aantal rijen in de tabel of geïndexeerde weergave op het moment dat de statistieken zijn geëvalueerd. De drempelwaarde verschilt of een tabel tijdelijk of permanent is.

    Tabeltype Tabel cardinaliteit (n) Drempelwaarde voor hercompilatie (#-wijzigingen)
    Tijdelijk n< 6 6
    Tijdelijk 6 <= n< = 500 500
    Permanente n<= 500 500
    Tijdelijk of permanent n> 500 500 + (0,20 * n)

    Als uw tabel bijvoorbeeld 20 duizend rijen bevat, wordt de berekening uitgevoerd 500 + (0.2 * 20,000) = 4,500 en worden de statistieken elke 4500 wijzigingen bijgewerkt.

  • Vanaf SQL Server 2016 (13.x) en met databasecompatibiliteitsniveau 130 gebruikt de Database Engine ook een afnemende, dynamische drempelwaarde voor het opnieuw compileren van statistieken die wordt aangepast aan de tabelkardinaliteit op het moment dat statistieken zijn geëvalueerd. Met deze wijziging worden statistieken voor grote tabellen vaker bijgewerkt. Als een database echter een compatibiliteitsniveau heeft dat lager is dan 130, zijn de drempelwaarden voor SQL Server 2014 (12.x) van toepassing.

    Tabeltype Tabel cardinaliteit (n) Drempelwaarde voor hercompilatie (#-wijzigingen)
    Tijdelijk n < 6 6
    Tijdelijk 6 <= n <= 500 500
    Permanente n <= 500 500
    Tijdelijk of permanent n > 500 MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

    Als uw tabel bijvoorbeeld 2 miljoen rijen bevat, is de berekening het minimum van 500 + (0.20 * 2,000,000) = 400,500 en SQRT(1,000 * 2,000,000) = 44,721. Dit betekent dat de statistieken elke 44.721 wijzigingen worden bijgewerkt.

Belangrijk

Schakel in SQL Server 2008 R2 (10.50.x) tot en met SQL Server 2014 (12.x) of in SQL Server 2016 (13.x) en latere versies met databasecompatibiliteitsniveau 120 en lagere versies traceringsvlag 2371 in, zodat SQL Server gebruikmaakt van een afnemende drempelwaarde voor dynamische statistieken.

Hoewel aanbevolen voor alle scenario's, is het inschakelen van traceringsvlag 2371 optioneel. U kunt echter de volgende richtlijnen gebruiken voor het inschakelen van de traceringsvlag 2371 in uw pre-SQL Server 2016-omgeving (13.x):

  • Als u een SAP-systeem gebruikt, schakelt u deze tracering in. Zie deze blog over traceringsvlag 2371 voor meer informatie.
  • Als u moet vertrouwen op een nachtelijke taak om statistieken bij te werken omdat de huidige automatische update niet vaak genoeg wordt geactiveerd, overweeg dan traceringsvlag 2371 in te schakelen om de drempelwaarde aan te passen aan de kaartinaliteit van de tabel.

De Query Optimizer controleert op verouderde statistieken voordat u een query compileert en voordat u een queryplan in de cache uitvoert. Voordat een query wordt gecompileerd, gebruikt de Query Optimizer de kolommen, tabellen en geïndexeerde weergaven in het querypredicaat om te bepalen welke statistieken verouderd kunnen zijn. Voordat een queryplan in de cache wordt uitgevoerd, controleert de database-engine of het queryplan verwijst naar up-to-datumstatistieken.

De optie AUTO_UPDATE_STATISTICS is van toepassing op statistiekenobjecten die zijn gemaakt voor indexen, enkelvoudige kolommen in querypredicaten en statistieken die zijn gemaakt met de instructie CREATE STATISTICS . Deze optie is ook van toepassing op gefilterde statistieken.

U kunt de sys.dm_db_stats_properties gebruiken om het aantal gewijzigde rijen in een tabel nauwkeurig bij te houden en te bepalen of u statistieken handmatig wilt bijwerken.

AUTO_UPDATE_STATISTICS is altijd UITGESCHAKELD voor tabellen die zijn geoptimaliseerd voor geheugen.

AUTO_UPDATE_STATISTICS_ASYNC

De optie voor het bijwerken van asynchrone statistieken, AUTO_UPDATE_STATISTICS_ASYNC, bepaalt of de queryoptimalisatie synchrone of asynchrone statistiekenupdates gebruikt. De optie voor het bijwerken van asynchrone statistieken is standaard UITGESCHAKELD en de queryoptimalisatie werkt statistieken synchroon bij. De optie AUTO_UPDATE_STATISTICS_ASYNC is van toepassing op statistiekenobjecten die zijn gemaakt voor indexen, enkelvoudige kolommen in querypredicaten en statistieken die zijn gemaakt met de instructie CREATE STATISTICS .

Opmerking

Als u de optie voor het bijwerken van asynchrone statistieken wilt instellen in SQL Server Management Studio, moet u op de pagina Opties van het venster Database-eigenschappen zowel Statistieken automatisch bijwerken als Automatische bijwerkstatistieken Asynchroon ingesteld op Waar.

Statistiekenupdates kunnen synchroon (de standaardinstelling) of asynchroon zijn.

  • Met updates van synchrone statistieken worden query's altijd gecompileerd en uitgevoerd met up-to-datumstatistieken. Wanneer statistieken verouderd zijn, wacht de Query Optimizer op bijgewerkte statistieken voordat de query wordt gemaakt en uitgevoerd.

  • Met asynchrone statistieken worden query's gecompileerd met bestaande statistieken, zelfs als de bestaande statistieken verouderd zijn. Query Optimizer kan een suboptimaal queryplan kiezen als statistieken verouderd zijn wanneer de query wordt gecompileerd. Statistieken worden doorgaans binnenkort bijgewerkt. Query's die worden gecompileerd nadat de statistieken zijn bijgewerkt, profiteren van het gebruik van de bijgewerkte statistieken.

Overweeg synchrone statistieken te gebruiken wanneer u bewerkingen uitvoert die de distributie van gegevens wijzigen, zoals het afkappen van een tabel of het uitvoeren van een bulkupdate van een groot percentage van de rijen. Als u de statistieken niet handmatig bijwerkt nadat u de bewerking hebt voltooid, zorgt u ervoor dat de statistieken up-to-datum zijn voordat query's worden uitgevoerd op de gewijzigde gegevens.

Overweeg het gebruik van asynchrone statistieken om voorspelbarere reactietijden voor query's te bereiken voor de volgende scenario's:

  • Uw applicatie voert vaak dezelfde query uit, vergelijkbare vragen of vergelijkbare cache-geheugens plannen. De reactietijden van uw query kunnen voorspelbaarder zijn met asynchrone statistiekenupdates dan met synchrone statistiekenupdates, omdat queryoptimalisatie binnenkomende query's kan uitvoeren zonder te wachten op up-to-datumstatistieken. Dit voorkomt dat sommige query's worden vertraagd, terwijl andere dat niet worden.

  • Uw applicatie heeft time-outs van clientverzoeken ervaren, veroorzaakt door een of meer query's die wachten op bijgewerkte statistieken. In sommige gevallen kan het wachten op synchrone statistieken ertoe leiden dat toepassingen met agressieve time-outs mislukken.

Opmerking

Statistieken over lokale tijdelijke tabellen worden altijd synchroon bijgewerkt, ongeacht AUTO_UPDATE_STATISTICS_ASYNC optie. Statistieken over globale tijdelijke tabellen worden synchroon of asynchroon bijgewerkt volgens de AUTO_UPDATE_STATISTICS_ASYNC optie die is ingesteld voor de gebruikersdatabase.

Update van asynchrone statistieken wordt uitgevoerd door een achtergrondaanvraag. Wanneer de aanvraag gereed is om bijgewerkte statistieken naar de database te schrijven, wordt geprobeerd een schemawijzigingsvergrendeling voor het metagegevensobject voor statistieken te verkrijgen. Als een andere sessie al een vergrendeling op hetzelfde object vasthoudt, wordt de update van asynchrone statistieken geblokkeerd totdat de schemawijzigingsvergrendeling kan worden verkregen. Op dezelfde manier kunnen sessies die een schemastabiliteit (Sch-S) vergrendeling moeten verkrijgen voor het metagegevensobject van statistieken om een query te compileren, worden geblokkeerd door de asynchrone-statistieken-update-achtergrondsessie, die al de schemawijzigingsvergrendeling heeft of wacht om deze te verkrijgen. Daarom kunnen workloads met zeer frequente querycompilaties en frequente updates van statistieken met behulp van asynchrone statistieken de kans op gelijktijdigheidsproblemen verhogen vanwege vergrendelingsblokkering.

In Azure SQL Database, Azure SQL Managed Instance en vanaf SQL Server 2022 (16.x) kunt u potentiële gelijktijdigheidsproblemen voorkomen met behulp van asynchrone statistieken bijwerken als u de ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY configuratie met databasebereik inschakelt. Als deze configuratie is ingeschakeld, wacht de achtergrondaanvraag om de schemawijziging (Sch-M) te verkrijgen en de bijgewerkte statistieken in een afzonderlijke wachtrij met lage prioriteit te behouden, zodat andere aanvragen query's kunnen blijven compileren met bestaande statistieken. Zodra er geen andere sessie een vergrendeling op het metagegevensobject voor statistieken vasthoudt, verkrijgt de achtergrondaanvraag een schema-aanpassingsvergrendeling en werkt deze de statistieken bij. In het onwaarschijnlijke geval dat de achtergrondaanvraag de vergrendeling niet binnen een time-outperiode van enkele minuten kan verkrijgen, wordt de update van asynchrone statistieken afgebroken en worden de statistieken pas bijgewerkt wanneer een andere automatische statistieken-update wordt geactiveerd of totdat statistieken handmatig worden bijgewerkt.

Opmerking

De configuratieoptie ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY binnen het database-bereik is beschikbaar in Azure SQL Database, Azure SQL Managed Instance en in SQL Server, vanaf versie 2022 (16.x).

AUTO_DROP optie

Van toepassing op: Azure SQL Database, Azure SQL Managed Instance en te beginnen met SQL Server 2022 (16.x)

In SQL Server vóór SQL Server 2022 (16.x), als statistieken handmatig worden gemaakt door een gebruiker of een hulpprogramma van derden in een gebruikersdatabase, kunnen deze statistiekenobjecten schemawijzigingen blokkeren of verstoren die u mogelijk wenst.

Vanaf SQL Server 2022 (16.x) is de optie voor automatisch neerzetten standaard ingeschakeld voor alle nieuwe en gemigreerde databases. Met AUTO_DROP de eigenschap kunnen statistiekenobjecten in een modus worden gemaakt, zodat een volgende schemawijziging niet wordt geblokkeerd door het statistiekobject, maar in plaats daarvan worden de statistieken zo nodig verwijderd. Op deze manier gedragen handmatig gemaakte statistieken waarvoor automatisch verwijderen is ingeschakeld, zich als automatisch gemaakte statistieken.

In Azure SQL Database, Azure SQL Managed Instance en SQL Server 2022 (16.x) en latere versies werken automatisch gemaakte statistieken alsof de AUTO_DROP is ingesteld.

Opmerking

Als u de eigenschap voor automatisch neerzetten wilt instellen of de set ongedaan wilt maken voor automatisch gemaakte statistieken, kunnen er fouten optreden. Automatisch gemaakte statistieken maken altijd gebruik van automatisch verwijderen. Sommige back-ups, wanneer deze worden hersteld, kunnen deze eigenschap onjuist worden ingesteld totdat het object voor statistieken de volgende keer wordt bijgewerkt (handmatig of automatisch). Automatisch gemaakte statistieken gedragen zich echter altijd als statistieken voor automatisch neerzetten. Wanneer u een database herstelt naar SQL Server 2022 (16.x) uit een eerdere versie, is het raadzaam om deze uit te voeren sp_updatestats op de database en de juiste metagegevens voor de functie voor automatisch verwijderen van statistieken in te stellen.

Als u bijvoorbeeld handmatig een statistiekenobject in de dbo.DatabaseLog tabel wilt maken:

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

Als u bijvoorbeeld de instelling voor het automatisch verwijderen van statistiekenobjecten op de dbo.DatabaseLog-tabel wilt bijwerken:

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

Als u de automatische drop-instelling van bestaande statistieken wilt beoordelen, gebruik dan de auto_drop kolom in sys.stats.

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

Zie AUTO_DROP voor meer informatie.

INCREMENTEEL

Van toepassing op: SQL Server 2014 (12.x) en nieuwere versies.

Wanneer de optie INCREMENTAL van CREATE STATISTICS is ingeschakeld, worden de statistieken per partitie aangemaakt. Wanneer uitgeschakeld, wordt de statistiekenboom verwijderd en worden de statistieken opnieuw berekend in SQL Server. De standaardwaarde is UITGESCHAKELD. Met deze instelling wordt de incrementele eigenschap op databaseniveau overschreven. Zie CREATE STATISTICS voor meer informatie over het maken van incrementele statistieken. Zie database-eigenschappen (pagina Opties) en ALTER DATABASE SET voor meer informatie over het automatisch maken van statistieken per partitie.

Wanneer er nieuwe partities worden toegevoegd aan een grote tabel, moeten statistieken worden bijgewerkt om de nieuwe partities op te nemen. De tijd die nodig is om de hele tabel (FULLSCAN of SAMPLE opties) te scannen, kan echter behoorlijk lang zijn. Het scannen van de hele tabel is ook niet nodig omdat alleen de statistieken op de nieuwe partities nodig zijn. Met de incrementele optie worden statistieken per partitie gemaakt en opgeslagen. Wanneer deze optie wordt bijgewerkt, worden alleen statistieken vernieuwd op deze partities waarvoor nieuwe statistieken nodig zijn

Als statistieken per partitie niet worden ondersteund, wordt de optie genegeerd en wordt er een waarschuwing gegenereerd. Incrementele statistieken worden niet ondersteund voor de volgende typen statistieken:

  • Statistieken die zijn gemaakt met indexen die niet zijn gepartitioneerd met de basistabel.
  • Statistieken die zijn gemaakt in leesbare secundaire databases met AlwaysOn.
  • Statistieken die zijn gemaakt voor alleen-lezendatabases.
  • Statistieken die zijn gemaakt voor gefilterde indexen.
  • Statistieken die zijn gemaakt in weergaven.
  • Statistieken die zijn gemaakt op interne tabellen.
  • Statistieken die zijn gemaakt met ruimtelijke indexen of XML-indexen.

Wanneer moet u statistieken maken

Query Optimizer maakt al statistieken op de volgende manieren:

  1. Query Optimizer maakt statistieken voor indexen in tabellen of weergaven wanneer de index wordt gemaakt. Deze statistieken worden gemaakt op de belangrijkste kolommen van de index. Als de index een gefilterde index is, worden met queryoptimalisatie gefilterde statistieken gemaakt op dezelfde subset rijen die zijn opgegeven voor de gefilterde index. Zie Gefilterde indexen maken en CREATE INDEX voor meer informatie over gefilterde indexen.

    Opmerking

    In SQL Server 2014 (12.x) en latere versies worden geen statistieken gemaakt door alle rijen in de tabel te scannen wanneer een gepartitioneerde index wordt gemaakt of opnieuw wordt opgebouwd. In plaats daarvan gebruikt de Query Optimizer het standaardsampling-algoritme om statistieken te genereren. Nadat u een database met gepartitioneerde indexen hebt bijgewerkt, ziet u mogelijk een verschil in de histogramgegevens voor deze indexen. Deze wijziging in gedrag heeft mogelijk geen invloed op de prestaties van query's. Als u statistieken over gepartitioneerde indexen wilt verkrijgen door alle rijen in de tabel te scannen, gebruikt u CREATE STATISTICS of UPDATE STATISTICS met de FULLSCAN-component.

  2. Query Optimizer maakt statistieken voor afzonderlijke kolommen in querypredicaten wanneer AUTO_CREATE_STATISTICS is ingeschakeld.

Voor de meeste query's zorgen deze twee methoden voor het maken van statistieken voor een queryplan van hoge kwaliteit; In enkele gevallen kunt u queryplannen verbeteren door extra statistieken te maken met de instructie CREATE STATISTICS . Deze aanvullende statistieken kunnen statistische correlaties vastleggen waarvoor de Query Optimizer geen rekening houdt wanneer er statistieken worden gemaakt voor indexen of afzonderlijke kolommen. Uw toepassing heeft mogelijk extra statistische correlaties in de tabelgegevens die, indien berekend in een statistiekenobject, de Query Optimizer kunnen inschakelen om queryplannen te verbeteren. Gefilterde statistieken op een subset van gegevensrijen of multicolumnstatistieken voor querypredicaatkolommen kunnen bijvoorbeeld het queryplan verbeteren.

Wanneer u statistieken maakt met de instructie CREATE STATISTICS, raden we u aan de optie AUTO_CREATE_STATISTICS AAN te houden, zodat queryoptimalisatieroutines regelmatig statistieken met één kolom voor querypredicaatkolommen blijven maken. Zie Zoekvoorwaarde voor meer informatie over querypredicaten.

Overweeg statistieken te maken met de instructie CREATE STATISTICS wanneer een van de volgende van toepassing is:

  • De Database Engine Tuning Advisor stelt voor om statistieken te maken.
  • Het querypredicaat bevat meerdere gecorreleerde kolommen die nog geen sleutels in dezelfde index zijn.
  • De query selecteert uit een subset met gegevens.
  • De query bevat ontbrekende statistieken.

Opmerking

Zie Statistieken voor Memory-Optimized Tabellen voor meer informatie specifiek voor In-Memory OLTP-gerelateerde tabellen en statistieken.

Querypredicaat bevat meerdere gecorreleerde kolommen

Wanneer een querypredicaat meerdere kolommen bevat met relaties tussen kolommen en afhankelijkheden, kunnen statistieken over de meerdere kolommen het queryplan verbeteren. Statistieken voor meerdere kolommen bevatten correlatiestatistieken tussen kolommen, ook wel densiteiten genoemd, die niet beschikbaar zijn in statistieken met één kolom. Densiteiten kunnen kardinaliteitschattingen verbeteren wanneer queryresultaten afhankelijk zijn van gegevensrelaties tussen meerdere kolommen.

Als de kolommen al in dezelfde index staan, bestaat het multicolumn statistics-object al en is het niet nodig om het handmatig te maken. Als de kolommen zich nog niet in dezelfde index bevinden, kunt u multicolumnstatistieken maken door een index in de kolommen te maken of door de instructie CREATE STATISTICS te gebruiken. Er zijn meer systeemresources nodig om een index te onderhouden dan een statistiekenobject. Als de toepassing de multicolumn-index niet nodig heeft, kunt u de systeembronnen beperken door het statistiekenobject te maken zonder de index te maken.

Wanneer u multicolumnstatistieken maakt, is de volgorde van de kolommen in de definitie van het statistiekenobject van invloed op de effectiviteit van de densiteit voor het maken van kardinaliteitschattingen. Het statistiekenobject slaat densiteit op voor elk voorvoegsel van sleutelkolommen in de definitie van het statistiekenobject. Zie de sectie Dichtheid op deze pagina voor meer informatie over densiteit.

Als u densiteiten wilt maken die nuttig zijn voor kardinaliteitschattingen, moeten de kolommen in het querypredicaat overeenkomen met een van de voorvoegsels van kolommen in de definitie van het statistiekenobject. In het volgende voorbeeld wordt bijvoorbeeld een multicolumn statistics-object gemaakt op de kolommen LastName, MiddleNameen 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

In dit voorbeeld heeft het statistische object LastFirst densiteit voor de volgende kolomvoorvoegsels: (LastName), (LastName, MiddleName)en (LastName, MiddleName, FirstName). De dichtheid is niet beschikbaar voor (LastName, FirstName). Als de query gebruikmaakt van LastName en FirstName zonder gebruik van MiddleName, is de dichtheid niet beschikbaar voor kardinaliteitschattingen.

Queryselecties uit een subset met gegevens

Wanneer queryoptimalisatie statistieken maakt voor afzonderlijke kolommen en indexen, worden de statistieken voor de waarden in alle rijen gemaakt. Wanneer query's kiezen uit een subset van rijen en die subset van rijen een unieke gegevensdistributie heeft, kunnen gefilterde statistieken queryplannen verbeteren. U kunt gefilterde statistieken maken met behulp van de instructie CREATE STATISTICS met de WHERE-component om de filterpredicaatexpressie te definiëren.

Als u bijvoorbeeld AdventureWorks2022 gebruikt, behoort elk product in de Production.Product tabel tot een van de vier categorieën in de Production.ProductCategory tabel: Bikes, Components, Clothingen Accessories. Elk van de categorieën heeft een andere gegevensverdeling voor gewicht: fietsgewichten variëren van 13.77 tot 30.0, onderdeelgewichten variëren van 2,12 tot 1050,00 met een aantal NULL waarden, kledinggewichten zijn allemaal NULL, en accessoiregewichten zijn ook NULL.

Met behulp van Bikes een voorbeeld bieden gefilterde statistieken voor alle fietsgewichten nauwkeurigere statistieken voor de queryoptimalisatie en kunnen de kwaliteit van het queryplan worden verbeterd in vergelijking met volledige tabelstatistieken of niet-bestaande statistieken in de kolom Gewicht. De kolom fietsgewicht is een goede kandidaat voor gefilterde statistieken, maar niet noodzakelijkerwijs een goede kandidaat voor een gefilterde index als het aantal gewichtzoekacties relatief klein is. De prestatiewinst voor zoekacties die een gefilterde index biedt, kan mogelijk niet opwegen tegen de extra onderhouds- en opslagkosten voor het toevoegen van een gefilterde index aan de database.

Met de volgende instructie worden de BikeWeights gefilterde statistieken voor alle subcategorieën voor Bikesgemaakt. De gefilterde predicaatexpressie definieert fietsen door alle fietssubcategorieën te inventariseren met de vergelijking Production.ProductSubcategoryID IN (1,2,3). Het predicaat kan de Bikes categorienaam niet gebruiken omdat deze is opgeslagen in de Production.ProductCategory tabel en alle kolommen in de filterexpressie moeten zich in dezelfde tabel bevinden.

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

De Query Optimizer kan de BikeWeights gefilterde statistieken gebruiken om het queryplan voor de volgende query te verbeteren waarmee alle fietsen worden geselecteerd die meer wegen dan 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

Query identificeert ontbrekende statistieken

Als een fout of andere gebeurtenis voorkomt dat queryoptimalisatie statistieken maakt, maakt de Query Optimizer het queryplan zonder statistieken te gebruiken. De queryoptimalisatie markeert de statistieken als ontbrekend en probeert de statistieken opnieuw te genereren wanneer de query de volgende keer wordt uitgevoerd.

Ontbrekende statistieken worden aangegeven als waarschuwingen (tabelnaam in rode tekst) wanneer het uitvoeringsplan van een query grafisch wordt weergegeven met behulp van SQL Server Management Studio. Daarnaast geeft het bewaken van de gebeurtenisklasse Ontbrekende kolomstatistieken met behulp van SQL Server Profiler aan wanneer er statistieken ontbreken. Zie voor meer informatie fouten en waarschuwingen gebeurteniscategorie (Database Engine).

Als er statistieken ontbreken, voert u de volgende stappen uit:

Wanneer statistieken voor een alleen-lezen database of alleen-lezen momentopname ontbreken of verouderd zijn, maakt en onderhoudt de database-engine tijdelijke statistieken in tempdb. Wanneer de database-engine tijdelijke statistieken maakt, wordt de naam van de statistieken toegevoegd aan het achtervoegsel _readonly_database_statistic om de tijdelijke statistieken te onderscheiden van de permanente statistieken. Het achtervoegsel _readonly_database_statistic is gereserveerd voor statistieken die worden gegenereerd door SQL Server. Scripts voor de tijdelijke statistieken kunnen worden gemaakt en gereproduceerd in een database voor lezen/schrijven. Wanneer het script is uitgevoerd, wijzigt Management Studio het achtervoegsel van de naam van de statistieken van _readonly_database_statistic in _readonly_database_statistic_scripted.

Alleen SQL Server kan tijdelijke statistieken maken en bijwerken. U kunt echter tijdelijke statistieken verwijderen en eigenschappen van statistieken controleren met behulp van dezelfde hulpprogramma's die u gebruikt voor permanente statistieken:

  • Verwijder tijdelijke statistieken met behulp van de instructie DROP STATISTICS .
  • Bewaak statistieken met behulp van de sys.stats en sys.stats_columns catalogusweergaven. De sys.stats systeemcatalogusweergave bevat de is_temporary kolom om aan te geven welke statistieken permanent zijn en welke tijdelijk zijn.

Omdat tijdelijke statistieken worden opgeslagen in tempdb, zorgt een herstart van de SQL Server-service ervoor dat alle tijdelijke statistieken verdwijnen.

Wanneer moet u statistieken bijwerken

De queryoptimalisatie bepaalt wanneer statistieken verouderd zijn en worden bijgewerkt wanneer ze nodig zijn voor een queryplan. In sommige gevallen kunt u het queryplan verbeteren en de queryprestaties verbeteren door statistieken vaker bij te werken dan wanneer AUTO_UPDATE_STATISTICS is ingeschakeld. U kunt statistieken bijwerken met de UPDATE STATISTICS instructie of de opgeslagen procedure sp_updatestats.

Het bijwerken van statistieken zorgt ervoor dat query's worden gecompileerd met up-to-datumstatistieken. Het bijwerken van statistieken via elk proces kan ertoe leiden dat queryplannen automatisch opnieuw worden gecompileren. Het is raadzaam om statistieken niet te vaak handmatig bij te werken, omdat er sprake is van een compromis tussen het verbeteren van queryplannen en de tijd die nodig is om query's opnieuw te compileren. De specifieke afwegingen zijn afhankelijk van uw toepassing.

Wanneer u statistieken bijwerkt met UPDATE STATISTICS of sp_updatestats, raden we u aan om AUTO_UPDATE_STATISTICS zo in te stellen dat de queryoptimalisatie statistieken regelmatig bijwerkt.

  • Zie UPDATE STATISTICS voor meer informatie over het bijwerken van statistieken voor een kolom, een index, een tabel of een geïndexeerde weergave.

  • Zie de opgeslagen procedure sp_updatestatsvoor informatie over het bijwerken van statistieken voor alle door de gebruiker gedefinieerde en interne tabellen in de database.

  • Zie AUTO_UPDATE_STATISTICS Optie voor meer informatie over de drempelwaarden voor automatische updates voor statistieken.

Wanneer AUTO_UPDATE_STATISTICS is ingesteld op UIT, kan hercompilatie van plannen nog steeds om verschillende andere redenen plaatsvinden, maar niet automatisch wegens veroudering van statistieken. Wanneer AUTO_UPDATE_STATISTICS deze is ingesteld op UIT, worden er alleen statistieken bijgewerkt via andere handmatig geplande processen, zoals onderhoudsplannen. Het instellen op AUTO_UPDATE_STATISTICS OFF kan daarom suboptimale queryplannen en verminderde queryprestaties veroorzaken.

Verouderde statistieken detecteren

Als u wilt bepalen wanneer statistieken voor het laatst zijn bijgewerkt, gebruikt u de functies sys.dm_db_stats_properties of STATS_DATE .

Overweeg statistieken bij te werken voor de volgende voorwaarden:

  • Uitvoeringstijden van query's zijn traag.
  • Invoegbewerkingen vinden plaats op oplopende of aflopende sleutelkolommen.
  • Na onderhoudsbewerkingen.

Zie UPDATE STATISTICS voor voorbeelden die statistieken handmatig bijwerken.

Uitvoeringstijden van query's zijn traag

Als reactietijden van query's traag of onvoorspelbaar zijn, moet u ervoor zorgen dat query's up-to-datumstatistieken hebben voordat u aanvullende stappen voor probleemoplossing uitvoert.

Invoegbewerkingen worden uitgevoerd op oplopende of aflopende sleutelkolommen

Statistieken voor oplopende of aflopende sleutelkolommen, zoals IDENTITY- of realtime tijdstempelkolommen, vereisen mogelijk vaker updates voor statistieken dan de Query Optimizer uitvoert. Invoegbewerkingen wijzen nieuwe waarden toe aan oplopende of aflopende kolommen. Het aantal toegevoegde rijen kan te klein zijn om een statistiekenupdate te activeren. Als statistieken niet up-to-datum en query's uit de laatst toegevoegde rijen selecteren, bevatten de huidige statistieken geen kardinaliteitschattingen voor deze nieuwe waarden. Dit kan leiden tot onjuiste kardinaliteitschattingen en trage queryprestaties.

Een query die uit de meest recente verkooporderdatums selecteert, heeft bijvoorbeeld onjuiste kardinaliteitschattingen als de statistieken niet worden bijgewerkt om kardinaliteitschattingen op te nemen voor de meest recente verkooporderdatums.

Na onderhoudsbewerkingen

Overweeg statistieken bij te werken na het uitvoeren van onderhoudsprocedures die de distributie van gegevens wijzigen, zoals het afkappen van een tabel of het uitvoeren van een bulkinvoeging van een groot percentage van de rijen. Dit kan toekomstige vertragingen in de verwerking van query's voorkomen terwijl query's wachten op automatische updates van statistieken.

Bewerkingen zoals herbouwen, defragmenteren of opnieuw orden van een index, wijzigen de distributie van gegevens niet. Daarom hoeft u geen statistieken bij te werken nadat u ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG- of ALTER INDEX REORGANIZE-bewerkingen hebt uitgevoerd. De queryoptimalisatie werkt statistieken bij wanneer u een index herbouwt in een tabel of weergave met ALTER INDEX REBUILD of DBCC DBREINDEX, maar deze statistiekenupdate is een byproduct van het opnieuw maken van de index. De queryoptimalisatie werkt geen statistieken bij na DBCC INDEXDEFRAG of ALTER INDEX REORGANIZE bewerkingen.

Aanbeveling

Vanaf SQL Server 2016 (13.x) SP1 CU4 gebruikt u de PERSIST_SAMPLE_PERCENT optie CREATE STATISTICS of UPDATE STATISTICS om een specifiek steekproefpercentage in te stellen en te behouden voor volgende statistische updates die geen steekproefpercentage expliciet opgeven.

Automatisch index- en statistiekenbeheer

Gebruik slimme oplossingen zoals Adaptive Index Defragmentatie om automatisch indexdefragmentatie en statistiekenupdates voor een of meer databases te beheren. Met deze procedure wordt automatisch gekozen of u een index wilt herbouwen of opnieuw ordenen op basis van het fragmentatieniveau, onder andere parameters, en statistieken wilt bijwerken met een lineaire drempelwaarde.

Query's die effectief gebruikmaken van statistieken

Bepaalde queryimplementaties, zoals lokale variabelen en complexe expressies in het querypredicaat, kunnen leiden tot suboptimale queryplannen. Het volgen van richtlijnen voor het ontwerpen van query's voor het effectief gebruiken van statistieken kan helpen dit te voorkomen. Zie Zoekvoorwaarde voor meer informatie over querypredicaten.

U kunt queryplannen verbeteren door ontwerprichtlijnen voor query's toe te passen die effectief gebruikmaken van statistieken om kardinaliteitschattingen te verbeteren voor expressies, variabelen en functies die worden gebruikt in querypredicaten. Wanneer de queryoptimalisatie de waarde van een expressie, variabele of functie niet weet, weet deze niet welke waarde in het histogram moet worden opgezoekd en kan daarom niet de beste kardinaliteitschatting uit het histogram worden opgehaald. In plaats daarvan baseert de Query Optimizer de kardinaliteitschatting op het gemiddelde aantal rijen per afzonderlijke waarde voor alle gemonsterde rijen in het histogram. Dit leidt tot suboptimale kardinaliteitschattingen en kan de prestaties van query's schaden. Zie de sectie histogrammen op deze pagina of sys.dm_db_stats_histogram voor meer informatie over histogrammen.

In de volgende richtlijnen wordt beschreven hoe u query's schrijft om queryplannen te verbeteren door kardinaliteitschattingen te verbeteren.

Kardinaliteitschattingen voor expressies verbeteren

Volg deze richtlijnen om de kardinaliteitschattingen voor expressies te verbeteren:

  • Vereenvoudig waar mogelijk expressies met constanten erin. De Query Optimizer evalueert niet alle functies en expressies die constanten bevatten voordat u kardinaliteitschattingen bepaalt. Vereenvoudig bijvoorbeeld de expressie ABS(-100) naar 100.
  • Als de expressie meerdere variabelen gebruikt, kunt u een berekende kolom voor de expressie maken en vervolgens statistieken of een index maken in de berekende kolom. Het querypredicaat WHERE PRICE + Tax > 100 kan bijvoorbeeld een betere kardinaliteitschatting hebben als u een berekende kolom voor de expressie Price + Taxmaakt.

Kardinaliteitsramingen voor variabelen en functies verbeteren

Volg deze richtlijnen om de kardinaliteitsramingen voor variabelen en functies te verbeteren:

  • Als het querypredicaat gebruikmaakt van een lokale variabele, kunt u overwegen de query opnieuw te schrijven om een parameter te gebruiken in plaats van een lokale variabele. De waarde van een lokale variabele is niet bekend wanneer de Query Optimizer het queryuitvoeringsplan maakt. Wanneer een query een parameter gebruikt, gebruikt de Query Optimizer de kardinaliteitschatting voor de eerste werkelijke parameterwaarde die wordt doorgegeven aan de opgeslagen procedure.

  • Overweeg om een standaardtabel of tijdelijke tabel te gebruiken om de resultaten van functies met meerdere instructies (mstvf) op te nemen. Query Optimizer maakt geen statistieken voor tabelwaardefuncties met meerdere instructies. Met deze methode kan queryoptimalisatie statistieken maken voor de tabelkolommen en deze gebruiken om een beter queryplan te maken.

  • Overweeg om een standaardtabel of tijdelijke tabel te gebruiken als vervanging voor tabelvariabelen. Query Optimizer maakt geen statistieken voor tabelvariabelen. Met deze methode kan queryoptimalisatie statistieken maken voor de tabelkolommen en deze gebruiken om een beter queryplan te maken. Er zijn compromissen bij het bepalen of een tijdelijke tabel of tabelvariabele moet worden gebruikt; Tabelvariabelen die worden gebruikt in opgeslagen procedures veroorzaken minder hercompilaties van de opgeslagen procedure dan tijdelijke tabellen. Afhankelijk van de toepassing kan het gebruik van een tijdelijke tabel in plaats van een tabelvariabele de prestaties mogelijk niet verbeteren.

  • Als een opgeslagen procedure een query bevat die gebruikmaakt van een doorgegeven parameter, moet u voorkomen dat u de parameterwaarde binnen de opgeslagen procedure wijzigt voordat u deze in de query gebruikt. De kardinaliteitschattingen voor de query zijn gebaseerd op de doorgegeven parameterwaarde en niet op de bijgewerkte waarde. Om te voorkomen dat u de parameterwaarde wijzigt, kunt u de query herschrijven om twee opgeslagen procedures te gebruiken.

    Bijvoorbeeld, de volgende opgeslagen procedure Sales.GetRecentSales wijzigt de waarde van de parameter @date wanneer @dateNULL is.

    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

    Als de eerste aanroep van de opgeslagen procedure Sales.GetRecentSales een NULL voor de @date parameter doorgeeft, compileert de Query Optimizer de opgeslagen procedure met de schatting voor de kardinaliteit van @date = NULL, ook al wordt het querypredicaat niet aangeroepen met @date = NULL. Deze kardinaliteitschatting kan aanzienlijk afwijken van het aantal rijen in het werkelijke queryresultaat. Als gevolg hiervan kan de Query Optimizer een suboptimaal queryplan kiezen. Om dit te voorkomen, kunt u de opgeslagen procedure als volgt herschrijven in twee procedures:

    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

Verbeter de kardinaliteitschattingen met query-aanwijzingen

Als u kardinaliteitschattingen voor lokale variabelen wilt verbeteren, kunt u de OPTIMIZE FOR <value> of OPTIMIZE FOR UNKNOWN query hints gebruiken met RECOMPILE. Zie queryhintsvoor meer informatie.

Voor sommige toepassingen kan het opnieuw compileren van de query telkens wanneer deze wordt uitgevoerd, te veel tijd in beslag nemen. De OPTIMIZE FOR queryhint kan zelfs helpen als u de RECOMPILE optie niet gebruikt. U kunt bijvoorbeeld een OPTIMIZE FOR optie toevoegen aan de opgeslagen procedure Sales.GetRecentSales om een specifieke datum op te geven. In het volgende voorbeeld wordt de OPTIMIZE FOR optie aan de Sales.GetRecentSales procedure toegevoegd.

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

Kardinaliteitschattingen verbeteren met planhandleidingen

Voor sommige toepassingen zijn ontwerprichtlijnen voor query's mogelijk niet van toepassing omdat u de query niet kunt wijzigen of omdat de RECOMPILE queryhint te veel hercompileert. U kunt planhandleidingen gebruiken om andere hints op te geven, zoals USE PLAN, om het gedrag van de query te beheren tijdens het onderzoeken van toepassingswijzigingen bij de leverancier van de toepassing. Zie Planhandleidingen voor meer informatie over planhandleidingen.

In Azure SQL Database kunt u Query Store-hints overwegen om plannen af te dwingen, in plaats van planhandleidingen. Zie Query Store-hints voor meer informatie.

Verwante inhoud