Statistics

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL Analytics-eindpunt in Microsoft FabricMagazijn in Microsoft FabricSQL-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

Statistics

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.

Note

SQL Server bouwt histogrammen voor slechts éé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 worden gevonden.
• Scan met bucket-samenvoeging: Elke extra waarde uit de eerste kolom van de statistieksleutel wordt in de tweede stap in gesorteerde volgorde verwerkt. Elke opeenvolgende waarde wordt toegevoegd aan het laatste bereik of er wordt een nieuw bereik aan het einde gemaakt (deze volgorde is mogelijk omdat de invoerwaarden worden gesorteerd). Als er een nieuw bereik wordt gemaakt, wordt met het proces één paar bestaande, aangrenzende bereiken samengevouwen tot één 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.

Note

Als het histogram is gebouwd met behulp van een steekproef in plaats van een volledige scan, zijn de waarden van equal_rows, range_rows, distinct_range_rows en average_range_rows schattingen en hoeven ze dus 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.

Voor elke histogramstap in het vorige voorbeeld:

• De vetgedrukte lijn vertegenwoordigt de bovengrenswaarde (range_high_key) en het aantal keren dat deze voorkomt (equal_rows).

• Het ononderbroken 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.

• De stippellijnen vertegenwoordigen de steekproefwaarden 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.

Note

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 CustomerIdbevat en ItemIdPricede 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 voor meer informatie over het filterpredicaat CREATE STATISTICS. Zie de sectie Wanneer statistieken maken in dit artikel voor meer informatie over wanneer u gefilterde statistieken maakt.

Opties voor statistieken

U kunt opties configureren die van invloed zijn op wanneer en hoe het systeem statistieken maakt en bijwerken. U kunt deze opties alleen instellen op databaseniveau.

AUTO_CREATE_STATISTICS optie

Wanneer u de optie voor automatisch maken van statistieken inschakelt, AUTO_CREATE_STATISTICS, worden met queryoptimalisatie statistieken gemaakt voor 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 de database statistieken voor indexen maakt. 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 u de optie statistieken voor automatische updates inschakelt, AUTO_UPDATE_STATISTICS, bepaalt de queryoptimalisatie wanneer statistieken mogelijk verouderd zijn en worden bijgewerkt wanneer een query deze 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 telt het aantal rijwijzigingen sinds de laatste update van de statistieken en vergelijkt dat getal met een drempelwaarde om te bepalen of statistieken verouderd kunnen zijn. De drempelwaarde is gebaseerd op de tabelkardinaliteit, het aantal rijen in de tabel of geïndexeerde weergave.

Statistieken markeren als verouderd op basis van rijwijzigingen, gebeurt zelfs wanneer de AUTO_UPDATE_STATISTICS optie is OFF. Als de AUTO_UPDATE_STATISTICS optie is OFF, worden de statistieken niet bijgewerkt, zelfs niet wanneer ze zijn gemarkeerd als verouderd. Plannen maken nog steeds gebruik van de verouderde statistiekenobjecten. Het instellen van AUTO_UPDATE_STATISTICS op OFF kan leiden tot suboptimale queryplannen en verminderde queryprestaties. Stel de AUTO_UPDATE STATISTICS optie in op ON.

• 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, afhankelijk van of een tabel tijdelijk of permanent is.

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

  Als uw tabel bijvoorbeeld 20.000 rijen bevat, is de berekening 500 + (0.2 * 20,000) = 4,500 en worden de statistieken elke 4.500 wijzigingen bijgewerkt.

• Vanaf SQL Server 2016 (13.x) en met databasecompatibiliteitsniveau 130 gebruikt de Database Engine een dalende, dynamische drempelwaarde voor het opnieuw compileren van statistieken die wordt aangepast aan de kardinaliteit van de tabel op het moment dat de 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 SQL Server 2014 (12.x) van toepassing.

  Tabeltype	Tabel cardinaliteit (n)	Drempelwaarde voor hercompilatie (#-wijzigingen)
  Temporary	n < 6	6
  Temporary	6 <= n <= 500	500
  Permanent	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.

Important

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 bent aangewezen op een nachtelijke taak om statistieken bij te werken omdat de huidige automatische update niet vaak genoeg wordt geactiveerd, overweeg dan traceflag 2371 in te schakelen om de drempelwaarde aan te passen aan de kardinaliteit 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 AUTO_UPDATE_STATISTICS optie is van toepassing op statistiekenobjecten die zijn gemaakt voor indexen, enkelvoudige kolommen in querypredicaten en statistieken die zijn gemaakt met de CREATE STATISTICS instructie. 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 OFF bedoeld 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 OFFstandaard en de queryoptimalisatie werkt statistieken synchroon bij. De AUTO_UPDATE_STATISTICS_ASYNC optie is van toepassing op statistiekenobjecten die zijn gemaakt voor indexen, afzonderlijke kolommen in querypredicaten en statistieken die zijn gemaakt met de CREATE STATISTICS instructie.

Note

Als u de optie voor het bijwerken van asynchrone statistieken in SQL Server Management Studio wilt instellen, stelt u op de pagina Opties van het venster Database-eigenschappen zowel Statistieken automatisch bijwerken als Statistieken automatisch bijwerken asynchroon in 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 na voltooiing van de bewerking niet handmatig bijwerkt, zorgt het gebruik van synchrone statistieken ervoor dat de statistieken actueel zijn voordat query's deze nodig hebben.

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 toepassing heeft time-outs van clientaanvragen ervaren die worden 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.

Note

Statistieken voor lokale tijdelijke tabellen worden altijd synchroon bijgewerkt, ongeacht de 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 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 update van statistieken wordt geactiveerd of totdat de statistieken handmatig worden bijgewerkt.

Note

De ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY configuratieoptie voor databasebereik is beschikbaar in Azure SQL Database, Azure SQL Managed Instance en in SQL Server vanaf SQL Server 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) kunt u, als u handmatig statistieken maakt of een hulpprogramma van derden gebruikt voor een gebruikersdatabase, deze statistiekenobjecten schemawijzigingen blokkeren of verstoren.

Vanaf SQL Server 2022 (16.x) is de optie voor automatisch neerzetten standaard ingeschakeld voor alle nieuwe en gemigreerde databases. Als u de AUTO_DROP eigenschap inschakelt, maakt de database statistiekenobjecten in een modus, 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 ingeschakeld.

Note

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 aangemaakte statistieken gedragen zich echter altijd als statistieken die automatisch worden verwijderd. 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.

INCREMENTAL

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

Wanneer u de optie INCREMENTAL van CREATE STATISTICS op ON instelt, maakt u statistieken per partitie. Wanneer u deze instelt op OFF, verwijdert de database de statistiekenboom en worden de statistieken opnieuw berekend. De standaardwaarde is OFF. Met deze instelling wordt de eigenschap op databaseniveau INCREMENTAL overschreven.

• Zie voor meer informatie over het maken van incrementele statistieken CREATE STATISTICS.
• Zie Database-eigenschappen (optiespagina) en ALTER DATABASE SET opties voor meer informatie over het automatisch maken van statistieken per partitie.

Wanneer u nieuwe partities aan een grote tabel toevoegt, moet u statistieken bijwerken om de nieuwe partities op te nemen. De tijd die nodig is om de hele tabel (FULLSCAN of SAMPLE opties) te scannen, kan echter 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 die partities waarvoor nieuwe statistieken nodig zijn.

Als statistieken per partitie niet worden ondersteund, negeert de database de optie en genereert een waarschuwing. Incrementele statistieken worden niet ondersteund voor de volgende typen statistieken:

• Statistieken die zijn gemaakt met indexen die niet zijn uitgelijnd met de partitionering van de basistabel.
• Statistieken die zijn gemaakt in leesbare secundaire databases met AlwaysOn.
• Statistieken gemaakt op alleen-lezen-databases.
• Statistieken die zijn gemaakt voor gefilterde indexen.
• Statistieken gemaakt op basis van 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. Wanneer u een index voor tabellen of weergaven maakt, worden met queryoptimalisatie statistieken voor indexen 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 INDEXvoor meer informatie over gefilterde indexen.

   Note

   In SQL Server 2014 (12.x) en latere versies maakt de database geen statistieken door alle rijen in de tabel te scannen wanneer u een gepartitioneerde index maakt of herbouwt. 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. Om statistieken over gepartitioneerde indexen te verkrijgen door alle rijen in de tabel te scannen, gebruikt u CREATE STATISTICS of UPDATE STATISTICS met de component FULLSCAN.

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 behulp van de CREATE STATISTICS instructie. 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 behulp van de CREATE STATISTICS instructie, houdt u de AUTO_CREATE_STATISTICS optie ON behouden, zodat queryoptimalisatie regelmatig statistieken met één kolom voor querypredicaatkolommen blijft maken. Zie Zoekvoorwaarde voor meer informatie over querypredicaten.

Overweeg statistieken te maken met behulp van de CREATE STATISTICS instructie wanneer een van de volgende voorwaarden 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.

Note

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 hoeft u het niet 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 CREATE STATISTICS instructie 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 in dit artikel 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.

Query selecteert 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 CREATE STATISTICS instructie met de WHERE-component om de filterpredicaatexpressie te definiëren.

Als u bijvoorbeeld AdventureWorks2025 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:

• Controleer of AUTO_CREATE_STATISTICS en AUTO_UPDATE_STATISTICS zijn ingeschakeld.
• Controleer of de database niet op alleen lezen staat. Als de database het kenmerk Alleen-lezen heeft, kan een nieuw statistiekenobject niet worden opgeslagen.
• Maak de ontbrekende statistieken met behulp van de CREATE STATISTICS instructie.

Tijdelijke statistieken

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 door de database-engine worden gegenereerd. Scripts voor de tijdelijke statistieken kunnen worden gemaakt en uitgevoerd op 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 de database-engine 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 DROP STATISTICS instructie.
• 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, verwijdert een herstart van de database-engine alle tijdelijke statistieken.

Net als voor alle statistieken vereist het maken en bijwerken van tijdelijke statistieken een schemawijziging (Sch-M)-vergrendeling op het object. Deze vergrendeling kan andere query's en processen blokkeren, waaronder het opnieuw uitvoeren van het systeem op secundaire replica's waarmee transacties van de primaire replica worden toegepast. Als deze blokkering van invloed is op queryworkloads of gegevensdoorgifte, kunt u het automatisch maken en bijwerken van tijdelijke statistieken uitschakelen met behulp van respectievelijk de READABLE_SECONDARY_TEMPORARY_STATS_AUTO_CREATEREADABLE_SECONDARY_TEMPORARY_STATS_AUTO_UPDATEconfiguraties binnen het databasebereik .

Wanneer moet u statistieken bijwerken

De queryoptimalisatie bepaalt wanneer statistieken verouderd kunnen zijn en werkt deze vervolgens bij wanneer dat nodig is 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 ON. U kunt statistieken bijwerken met behulp van de UPDATE STATISTICS instructie of de opgeslagen procedure sp_updatestats.

Het bijwerken van statistieken zorgt dat query’s worden gecompileerd met actuele statistieken. Het bijwerken van statistieken via elk proces kan ertoe leiden dat queryplannen automatisch opnieuw worden gecompileren. Werk statistieken niet handmatig bij omdat er een afweging is 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 behulp van UPDATE STATISTICS of sp_updatestats, moet AUTO_UPDATE_STATISTICS ingesteld blijven op ON, zodat de Query Optimizer de statistieken regelmatig bijwerkt.

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

• 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 u deze instelling instelt AUTO_UPDATE_STATISTICSOFF, kan het opnieuw compileren van plannen nog steeds om verschillende andere redenen plaatsvinden, maar dit gebeurt niet automatisch vanwege verouderde statistiekenupdates. Wanneer u AUTO_UPDATE_STATISTICS instelt op OFF, worden statistieken alleen bijgewerkt via andere handmatig geplande processen, zoals onderhoudsplannen. Het instellen van AUTO_UPDATE_STATISTICS op 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 voor voorbeelden van het handmatig UPDATE STATISTICSbijwerken van statistieken.

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 statistiekenupdates 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. Deze voorwaarde kan leiden tot onjuiste kardinaliteitschattingen en trage queryprestaties.

Een query die uit de meest recente verkooporderdatums selecteert, bevat 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. Het proactief bijwerken van statistieken 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 REBUILD-, ALTER INDEX-, DBCC INDEXDEFRAG- of REORGANIZE-bewerkingen hebt uitgevoerdALTER INDEX. 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.

Tip

Vanaf SQL Server 2016 (13.x) SP1 CU4 gebruikt u de optie PERSIST_SAMPLE_PERCENT of CREATE STATISTICSUPDATE STATISTICS om een specifiek steekproefpercentage in te stellen en te behouden voor volgende statistieken 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 wilt ordenen op basis van het fragmentatieniveau, onder andere parameters en statistieken wilt bijwerken met een lineaire drempelwaarde.

Bepalen welke statistieken de queryoptimalisatie heeft gebruikt

U vindt de statistiekenobjecten die de Query Optimizer gebruikt wanneer een query wordt gecompileerd door een geschatte of werkelijke uitvoeringsplan te controleren. Wanneer u een uitvoeringsplan inspecteert, bevat OptimizerStatsUage het StatisticsInfo element elementen die informatie bevatten over de statistiekenobjecten die de Query Optimizer tijdens de compilatie heeft geladen. De StatisticsInfo elementen bevatten de naam van het statistiekenobject, de database, het schema en de tabel waartoe het behoort, het aantal wijzigingen op het moment van compileren, het steekproefpercentage en het tijdstip waarop deze voor het laatst is bijgewerkt.

Gebruik een van de volgende technieken om het uitvoeringsplan te inspecteren:

• Selecteer in SQL Server Management Studio Het werkelijke uitvoeringsplan opnemen (Ctrl+M) voordat u de query uitvoert. Op het tabblad Uitvoeringsplan dat wordt weergegeven met de resultaten, kunt u het volgende doen:
  • Klik met de rechtermuisknop in het grafische plan en selecteer XML van uitvoeringsplan weergeven. Zoek naar het OptimizerStatsUsage-element en elk onderliggend StatisticsInfo-element.
  • Selecteer de laatste operator (meest links). (In het geval van een SELECT query is deze operator een SELECT knooppunt.) Vouw in het venster Eigenschappen het knooppunt OptimizerStatsUsage uit en bekijk informatie over de statistiekenobjecten die in de query worden gebruikt.
• Voer SETSET STATISTICS XML ON uit vóór de query. Selecteer de hyperlink die wordt weergegeven met de resultaten om de XML van het uitvoeringsplan weer te geven.
• Raadpleeg sys.dm_exec_query_plan of sys.dm_exec_query_statistics_xml voor recente query’s.
• Lees een eerder vastgelegd plan uit Query Store met behulp van sys.query_store_plan.

Elk StatisticsInfo element ziet eruit als het volgende XML-fragment uit een query in de AdventureWorks2022 voorbeelddatabase:

<StatisticsInfo 
    Database="[AdventureWorks2022]" 
    Schema="[Sales]" 
    Table="[SalesOrderDetail]" 
    Statistics="[IX_SalesOrderDetail_ProductID]" 
    ModificationCount="0" 
    SamplingPercent="100" 
    LastUpdate="2025-09-07T15:32:16.89" />

Attribute	Meaning
Database, Schema, Table	Het object waartoe de statistiek behoort.
Statistics	Naam van het statistiekenobject in de database. Gebruik deze naam met DBCC SHOW_STATISTICS of sys.stats om het histogram en de dichtheidsvector te inspecteren.
ModificationCount	Aantal gegevenswijzigingen sinds de statistiek voor het laatst is bijgewerkt, op het moment dat het plan is gecompileerd. Een grote waarde ten opzichte van de tabelgrootte geeft aan dat de statistiek is verlopen tijdens de compilatie.
SamplingPercent	Percentage rijen dat is gesampeld om de statistiek te bouwen. Lagere waarden kunnen minder nauwkeurige histogrammen produceren voor scheefgetrokken gegevens.
LastUpdate	Tijdstempel van de laatste update van statistieken. Als de optie AUTO_UPDATE_STATISTICS is ingeschakeld voor de database, worden statistieken automatisch bijgewerkt wanneer dat nodig is.

Note

StatisticsInfo geeft statistieken weer die beschikbaar waren en die tijdens de compilatie van het plan werden overwogen. Als er een StatisticsInfo vermelding ontbreekt voor een kolom waarop uw queryfilters zijn toegepast, heeft de queryoptimalisatie geen relevante statistieken geïdentificeerd. Dit is een mogelijke bron van slechte prestaties.

Gebruik sys.dm_db_stats_properties om de huidige nieuwheids- en wijzigingsaantallen voor een statistiekobject te controleren. De volgende query bevat bijvoorbeeld de huidige metrische gegevens voor een statistiekobject met de naam IX_SalesOrderDetail_ProductID in de tabel Sales.SalesOrderDetail:

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id)        AS table_name,
    s.name                          AS statistics_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'Sales.SalesOrderDetail')
  AND s.name = N'IX_SalesOrderDetail_ProductID';

Als u wilt bepalen of de opties voor het automatisch maken en bijwerken van de huidige database zijn ingeschakeld, gebruikt u:

SELECT [name],
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases
WHERE [name] = DB_NAME();

Query's die effectief gebruikmaken van statistieken

Bepaalde queryimplementaties, zoals lokale variabelen en complexe expressies in het querypredicaat, kunnen leiden tot suboptimale queryplannen. Als u deze problemen wilt voorkomen, volgt u de ontwerprichtlijnen voor query's voor het effectief gebruiken van statistieken. 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. Deze situatie leidt tot suboptimale kardinaliteitschattingen en kan de queryprestaties schaden. Zie de sectie histogrammen in dit artikel 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 die constanten bevatten. De Query Optimizer evalueert niet alle functies en expressies die constanten bevatten voordat de kardinaliteitschattingen worden bepaald. 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 queryoptimalisatie kent de waarde van een lokale variabele niet wanneer het queryuitvoeringsplan wordt gemaakt. Wanneer een query een parameter gebruikt, gebruikt de Query Optimizer de kardinaliteitschatting voor de eerste werkelijke parameterwaarde die door de opgeslagen procedure wordt ontvangen.

• Overweeg om een standaardtabel of tijdelijke tabel te gebruiken om de resultaten van functies met meerdere waarden in de tabel te bewaren. Query Optimizer maakt geen statistieken voor tabelfuncties met meerdere waarden. Met deze methode kan Query Optimizer 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 Query Optimizer 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 probleem 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 schattingen van de kardinaliteit voor lokale variabelen wilt verbeteren, gebruikt u de queryhints OPTIMIZE FOR <value> of OPTIMIZE FOR UNKNOWN in combinatie 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. Gebruik planhandleidingen om andere hints op te geven, zoals USE PLAN, om het gedrag van de query te beheren tijdens het onderzoeken van toepassingswijzigingen met de leverancier van de toepassing. Zie Planhandleidingen voor meer informatie over planhandleidingen.

In SQL Server 2022 en latere versies, Azure SQL Database, Azure SQL Managed Instance en SQL Database in Fabric, overweeg Query Store-hints om plannen af te dwingen, in plaats van plangidsen. Zie Query Store-hints voor meer informatie.

Platformondersteuning

In Fabric Data Warehouse en het SQL-analyse-eindpunt kunnen alleen histogramstatistieken met één kolom handmatig worden gemaakt of aangepast. In Fabric Data Warehouse en het SQL-analyse-eindpunt kunnen alleen histogramstatistieken met één kolom handmatig worden bijgewerkt met UPDATE STATISTICS. De sp_updatestats opgeslagen procedure wordt niet ondersteund in Fabric Data Warehouse of het SQL-analyse-endpoint. Zie Statistieken in Fabric Data Warehouse voor meer informatie.

Verwante inhoud

• Statistieken voor Memory-Optimized tabellen
• CREATE STATISTICS (Transact-SQL)
• UPDATE UPDATE STATISTICS (Transact-SQL)
• sys.sp_updatestats (Transact-SQL)
• DBCC-SHOW_STATISTICS (Transact-SQL)
• ALTER DATABASE SET opties (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• Gefilterde indexen maken
• STATS_DATE (Transact-SQL)
• sys.dm_db_stats_properties (Transact-SQL)
• sys.dm_db_stats_histogram (Transact-SQL)
• sys.stats (Transact-SQL)
• sys.stats_columns (Transact-SQL)
• Adaptieve Indexdefragmentatie