sys.dm_exec_query_optimizer_info (Transact-SQL)
gäller för:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)
Returnerar detaljerad statistik om hur SQL Server-frågeoptimeraren fungerar. Du kan använda den här vyn när du justerar en arbetsbelastning för att identifiera problem med frågeoptimering eller förbättringar. Du kan till exempel använda det totala antalet optimeringar, det förflutna tidsvärdet och det slutliga kostnadsvärdet för att jämföra frågeoptimeringarna för den aktuella arbetsbelastningen och eventuella ändringar som observerats under justeringsprocessen. Vissa räknare tillhandahåller data som endast är relevanta för intern diagnostikanvändning i SQL Server. Dessa räknare är markerade som "Endast internt".
Not
Om du vill anropa detta från Azure Synapse Analytics eller Analytics Platform System (PDW) använder du namnet sys.dm_pdw_nodes_exec_query_optimizer_info. Den här syntaxen stöds inte av en serverlös SQL-pool i Azure Synapse Analytics.
| Namn | Datatyp | Beskrivning |
|---|---|---|
counter |
nvarchar(4000) | Namn på statistikhändelse för optimerare. |
occurrence |
bigint | Antal förekomster av optimeringshändelse för den här räknaren. |
value |
flyttal | Genomsnittligt egenskapsvärde per händelsehändelse. |
pdw_node_id |
int | Identifieraren för noden som den här fördelningen är på. gäller för: Azure Synapse Analytics, Analytics Platform System (PDW) |
Behörigheter
SQL Server 2019 (15.x) och tidigare versioner och Azure SQL Managed Instance kräver VIEW SERVER STATE behörighet.
SQL Server 2022 (16.x) och senare versioner kräver VIEW SERVER PERFORMANCE STATE behörighet på servern.
I Azure SQL Database Basic, S0och S1 servicemål och för databaser i elastiska pooler, kontot serveradministratör konto, Microsoft Entra-administratör konto eller medlemskap i ##MS_ServerStateReader##serverrollen. För alla andra SQL Database-tjänstmål krävs antingen VIEW DATABASE STATE behörighet för databasen eller medlemskap i ##MS_ServerStateReader## serverroll.
Anmärkningar
sys.dm_exec_query_optimizer_info innehåller följande egenskaper (räknare). Alla förekomstvärden är kumulativa och är inställda på 0 vid omstart av systemet. Alla värden för värdefält är inställda på NULL vid omstart av systemet. Alla värdekolumnvärden som anger ett medelvärde använder förekomstvärdet från samma rad som nämnaren i beräkningen av medelvärdet. Alla frågeoptimeringar mäts när SQL Server fastställer ändringar i dm_exec_query_optimizer_info, inklusive både användargenererade och systemgenererade frågor. Körning av en redan cachelagrad plan ändrar inte värden i dm_exec_query_optimizer_info, endast optimeringar är betydande.
| Räknare | Förekomst | Värde |
|---|---|---|
optimizations |
Totalt antal optimeringar. | Ej tillämpligt |
elapsed time |
Totalt antal optimeringar. | Genomsnittlig förfluten tid per optimering av en enskild instruktion (fråga) i sekunder. |
final cost |
Totalt antal optimeringar. | Genomsnittlig uppskattad kostnad för en optimerad plan i interna kostnadsenheter. |
trivial plan |
Endast internt | Endast internt |
tasks |
Endast internt | Endast internt |
no plan |
Endast internt | Endast internt |
search 0 |
Endast internt | Endast internt |
search 0 time |
Endast internt | Endast internt |
search 0 tasks |
Endast internt | Endast internt |
search 1 |
Endast internt | Endast internt |
search 1 time |
Endast internt | Endast internt |
search 1 tasks |
Endast internt | Endast internt |
search 2 |
Endast internt | Endast internt |
search 2 time |
Endast internt | Endast internt |
search 2 tasks |
Endast internt | Endast internt |
gain stage 0 to stage 1 |
Endast internt | Endast internt |
gain stage 1 to stage 2 |
Endast internt | Endast internt |
timeout |
Endast internt | Endast internt |
memory limit exceeded |
Endast internt | Endast internt |
insert stmt |
Antal optimeringar som gäller för INSERT-instruktioner. |
Ej tillämpligt |
delete stmt |
Antal optimeringar som gäller för DELETE-instruktioner. |
Ej tillämpligt |
update stmt |
Antal optimeringar som gäller för UPDATE-instruktioner. |
Ej tillämpligt |
merge stmt |
Antal optimeringar som gäller för MERGE-instruktioner. |
Ej tillämpligt |
contains subquery |
Antal optimeringar för en fråga som innehåller minst en underfråga. | Ej tillämpligt |
unnest failed |
Endast internt | Endast internt |
tables |
Totalt antal optimeringar. | Genomsnittligt antal tabeller som refereras till per fråga optimerad. |
hints |
Antal gånger som vissa tips har angetts. Tips som räknas är: JOIN, GROUP, UNION och FORCE ORDER frågetips, FORCE PLAN ange alternativ och kopplingstips. |
Ej tillämpligt |
order hint |
Antal gånger som anslutningsordningen tvingades. Den här räknaren är inte begränsad till FORCE ORDER tips. Om du anger en kopplingsalgoritm i en fråga, till exempel en INNER HASH JOIN, framtvingas även kopplingsordningen, vilket ökar räknaren. |
Ej tillämpligt |
join hint |
Antal gånger som kopplingsalgoritmen tvingades av ett kopplingstips. Det FORCE ORDER frågetipset ökar inte den här räknaren. |
Ej tillämpligt |
view reference |
Antal gånger som en vy refereras till i en fråga. | Ej tillämpligt |
remote query |
Antal optimeringar där frågan refererade till minst en fjärrdatakälla, till exempel en tabell med ett namn i fyra delar eller ett OPENROWSET resultat. |
Ej tillämpligt |
maximum DOP |
Totalt antal optimeringar. | Genomsnittligt effektivt MAXDOP värde för en optimerad plan. Som standard bestäms effektiva MAXDOP av maximal grad av parallellitet serverkonfigurationsalternativet och kan åsidosättas för en specifik fråga med värdet för MAXDOP frågetips. |
maximum recursion level |
Antal optimeringar där en MAXRECURSION nivå som är större än 0 angavs med frågetipset. |
Genomsnittlig MAXRECURSION nivå i optimeringar där en maximal rekursionsnivå angavs med frågetipset. |
indexed views loaded |
Endast internt | Endast internt |
indexed views matched |
Antal optimeringar där en eller flera indexerade vyer matchas. | Genomsnittligt antal visningar som matchas. |
indexed views used |
Antal optimeringar där en eller flera indexerade vyer används i utdataplanen efter matchning. | Genomsnittligt antal vyer som används. |
indexed views updated |
Antal optimeringar av en DML-instruktion som skapar en plan som underhåller en eller flera indexerade vyer. | Genomsnittligt antal vyer som underhålls. |
dynamic cursor request |
Antal optimeringar där en begäran om dynamisk markör har angetts. | Ej tillämpligt |
fast forward cursor request |
Antal optimeringar där en snabbsnabb markörbegäran har angetts. | Ej tillämpligt |
Exempel
A. Visa statistik om körning av optimerare
Vilken är den aktuella körningsstatistiken för optimeraren för den här instansen av SQL Server?
SELECT * FROM sys.dm_exec_query_optimizer_info;
B. Visa det totala antalet optimeringar
Hur många optimeringar utförs?
SELECT occurrence AS Optimizations
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations';
C. Genomsnittlig förfluten tid per optimering
Vad är den genomsnittliga förflutna tiden per optimering?
SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'elapsed time';
D. Del av optimeringar som omfattar underfrågor
Vilken del av optimerade frågor innehöll en underfråga?
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. Visa det totala antalet tips under optimeringen
Hur många tips räknas när FORCE ORDER ingår som ett frågetips?
-- 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'
);