sys.dm_exec_query_optimizer_info (Transact-SQL)

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Retourneert gedetailleerde statistieken over de werking van de SQL Server-queryoptimalisatie. U kunt deze weergave gebruiken bij het afstemmen van een workload om queryoptimalisatieproblemen of verbeteringen te identificeren. U kunt bijvoorbeeld het totale aantal optimalisaties, de verstreken tijdswaarde en de uiteindelijke kostenwaarde gebruiken om de queryoptimalisaties van de huidige workload te vergelijken en eventuele wijzigingen die tijdens het afstemmingsproces zijn waargenomen. Sommige tellers bieden gegevens die alleen relevant zijn voor intern diagnostisch gebruik van SQL Server. Deze tellers zijn gemarkeerd als 'Alleen intern'.

Notitie

Gebruik de naam sys.dm_pdw_nodes_exec_query_optimizer_infoom dit aan te roepen vanuit Azure Synapse Analytics of Analytics Platform System (PDW). Deze syntaxis wordt niet ondersteund door een serverloze SQL-pool in Azure Synapse Analytics.

Naam	Gegevenstype	Beschrijving
counter	nvarchar(4000)	Naam van de gebeurtenis voor de optimalisatiestatistieken.
occurrence	grote	Aantal exemplaren van optimalisatie-gebeurtenis voor deze teller.
value	float-	Gemiddelde eigenschapswaarde per gebeurtenis.
pdw_node_id		De id voor het knooppunt waarop deze distributie zich bevindt. van toepassing op: Azure Synapse Analytics, Analytics Platform System (PDW)

Machtigingen

SQL Server 2019 (15.x) en eerdere versies, en Azure SQL Managed Instance, vereisen VIEW SERVER STATE machtiging.

VOOR SQL Server 2022 (16.x) en latere versies is VIEW SERVER PERFORMANCE STATE machtiging op de server vereist.

In Azure SQL Database Basic, S0en S1 servicedoelstellingen, en voor databases in elastische pools, is het serverbeheerder--account, het Microsoft Entra-beheerdersaccount of lidmaatschap van de ##MS_ServerStateReader##server rol vereist. Voor alle andere SQL Database-servicedoelstellingen is de VIEW DATABASE STATE machtiging voor de database of lidmaatschap van de ##MS_ServerStateReader## serverfunctie vereist.

Opmerkingen

sys.dm_exec_query_optimizer_info bevat de volgende eigenschappen (tellers). Alle exemplaarwaarden zijn cumulatief en worden ingesteld op 0 bij het opnieuw opstarten van het systeem. Alle waarden voor waardevelden worden ingesteld op NULL bij het opnieuw opstarten van het systeem. Alle waarden in de waardekolom die een gemiddelde opgeven, gebruiken de exemplaarwaarde uit dezelfde rij als de noemer in de berekening van het gemiddelde. Alle queryoptimalisaties worden gemeten wanneer SQL Server wijzigingen in dm_exec_query_optimizer_infobepaalt, inclusief door de gebruiker gegenereerde en door het systeem gegenereerde query's. De uitvoering van een al in de cache opgeslagen plan wijzigt geen waarden in dm_exec_query_optimizer_info, alleen optimalisaties zijn aanzienlijk.

Toonbank	Gebeurtenis	Waarde
optimizations	Totaal aantal optimalisaties.	Niet van toepassing
elapsed time	Totaal aantal optimalisaties.	De gemiddelde verstreken tijd per optimalisatie van een afzonderlijke instructie (query) in seconden.
final cost	Totaal aantal optimalisaties.	Gemiddelde geschatte kosten voor een geoptimaliseerd plan in interne kosteneenheden.
trivial plan	Alleen intern	Alleen intern
tasks	Alleen intern	Alleen intern
no plan	Alleen intern	Alleen intern
search 0	Alleen intern	Alleen intern
search 0 time	Alleen intern	Alleen intern
search 0 tasks	Alleen intern	Alleen intern
search 1	Alleen intern	Alleen intern
search 1 time	Alleen intern	Alleen intern
search 1 tasks	Alleen intern	Alleen intern
search 2	Alleen intern	Alleen intern
search 2 time	Alleen intern	Alleen intern
search 2 tasks	Alleen intern	Alleen intern
gain stage 0 to stage 1	Alleen intern	Alleen intern
gain stage 1 to stage 2	Alleen intern	Alleen intern
timeout	Alleen intern	Alleen intern
memory limit exceeded	Alleen intern	Alleen intern
insert stmt	Aantal optimalisaties voor INSERT instructies.	Niet van toepassing
delete stmt	Aantal optimalisaties voor DELETE instructies.	Niet van toepassing
update stmt	Aantal optimalisaties voor UPDATE instructies.	Niet van toepassing
merge stmt	Aantal optimalisaties voor MERGE instructies.	Niet van toepassing
contains subquery	Aantal optimalisaties voor een query die ten minste één subquery bevat.	Niet van toepassing
unnest failed	Alleen intern	Alleen intern
tables	Totaal aantal optimalisaties.	Het gemiddelde aantal tabellen waarnaar wordt verwezen per geoptimaliseerde query.
hints	Aantal keren dat er een hint is opgegeven. Hints die worden geteld, zijn onder andere: JOIN, GROUP, UNION en FORCE ORDER queryhints, FORCE PLAN optie instellen en hints toevoegen.	Niet van toepassing
order hint	Aantal keren dat de joinorder werd gedwongen. Deze teller is niet beperkt tot de FORCE ORDER hint. Als u een join-algoritme opgeeft in een query, zoals een INNER HASH JOIN, wordt ook de samenvoegvolgorde afgeslagen, waardoor de teller wordt verhoogd.	Niet van toepassing
join hint	Aantal keren dat het join-algoritme is gedwongen door een join-hint. Met de FORCE ORDER queryhint wordt deze teller niet verhoogd.	Niet van toepassing
view reference	Aantal keren dat in een query naar een weergave wordt verwezen.	Niet van toepassing
remote query	Aantal optimalisaties waarnaar de query ten minste één externe gegevensbron verwijst, zoals een tabel met een vierdelige naam of een OPENROWSET resultaat.	Niet van toepassing
maximum DOP	Totaal aantal optimalisaties.	Gemiddelde effectieve MAXDOP waarde voor een geoptimaliseerd plan. Effectieve MAXDOP wordt standaard bepaald door de maximale mate van parallelle uitvoering serverconfiguratieoptie en kan worden overschreven voor een specifieke query door de waarde van de MAXDOP queryhint.
maximum recursion level	Aantal optimalisaties waarin een MAXRECURSION niveau groter is dan 0 is opgegeven met de queryhint.	Gemiddeld MAXRECURSION niveau in optimalisaties waarbij een maximumrecursieniveau is opgegeven met de queryhint.
indexed views loaded	Alleen intern	Alleen intern
indexed views matched	Aantal optimalisaties waarbij een of meer geïndexeerde weergaven overeenkomen.	Het gemiddelde aantal weergaven dat overeenkomt.
indexed views used	Aantal optimalisaties waarbij een of meer geïndexeerde weergaven worden gebruikt in het uitvoerplan nadat deze overeenkomen.	Gemiddeld aantal gebruikte weergaven.
indexed views updated	Aantal optimalisaties van een DML-instructie die een plan produceert dat een of meer geïndexeerde weergaven onderhoudt.	Het gemiddelde aantal weergaven dat wordt onderhouden.
dynamic cursor request	Aantal optimalisaties waarin een dynamische cursoraanvraag is opgegeven.	Niet van toepassing
fast forward cursor request	Aantal optimalisaties waarin een fast-forward cursoraanvraag is opgegeven.	Niet van toepassing

Voorbeelden

Een. Statistieken weergeven over de uitvoering van de optimizer

Wat zijn de huidige uitvoeringsstatistieken van de optimizer voor dit exemplaar van SQL Server?

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. Het totale aantal optimalisaties weergeven

Hoeveel optimalisaties worden uitgevoerd?

SELECT occurrence AS Optimizations
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations';

C. Gemiddelde verstreken tijd per optimalisatie

Wat is de gemiddelde verstreken tijd per optimalisatie?

SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'elapsed time';

D. Deel van optimalisaties waarbij subquery's betrokken zijn

Welk deel van geoptimaliseerde query's bevat een subquery?

SELECT (
    SELECT CAST(occurrence AS FLOAT)
    FROM sys.dm_exec_query_optimizer_info
    WHERE counter = 'contains subquery'
) / (
    SELECT CAST(occurrence AS FLOAT)
    FROM sys.dm_exec_query_optimizer_info
    WHERE counter = 'optimizations'
) AS ContainsSubqueryFraction;

E. Het totale aantal hints weergeven tijdens optimalisatie

Hoeveel hints worden geteld wanneer FORCE ORDER wordt opgenomen als een queryhint?

-- Check hint count before query execution
SELECT ISNULL('', 0) AS [Before],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

SELECT poh.PurchaseOrderID,
    poh.OrderDate,
    pod.ProductID,
    pod.DueDate,
    poh.VendorID
FROM Purchasing.PurchaseOrderHeader AS poh
INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod
    ON poh.PurchaseOrderID = pod.PurchaseOrderID
OPTION (
    FORCE ORDER,
    RECOMPILE
);

-- check hint count after query execution
SELECT ISNULL('', 0) AS [After],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

Verwante inhoud

• dynamische beheerweergaven en -functies (Transact-SQL)
• uitvoeringsgerelateerde dynamische beheerweergaven en -functies (Transact-SQL)