Sdílet prostřednictvím

sys.dm_exec_query_optimizer_info (Transact-SQL)

  • Článek

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed InstanceAzure Synapse AnalyticsPlatform Platform System (PDW)

Vrátí podrobné statistiky o provozu optimalizátoru dotazů SQL Serveru. Toto zobrazení můžete použít při ladění úlohy k identifikaci problémů nebo vylepšení optimalizace dotazů. Můžete například použít celkový počet optimalizací, uplynulou časnou hodnotu a konečnou hodnotu nákladů k porovnání optimalizací dotazů aktuální úlohy a všech změn pozorovaných během procesu ladění. Některé čítače poskytují data, která jsou relevantní jenom pro interní diagnostické použití SQL Serveru. Tyto čítače jsou označeny jako "Pouze interní".

Poznámka

K volání z Azure Synapse Analytics nebo systému PDW (Analytics Platform System) použijte název sys.dm_pdw_nodes_exec_query_optimizer_info. Tato syntaxe není podporována bezserverovým fondem SQL ve službě Azure Synapse Analytics.

Jméno Datový typ Popis
counter nvarchar(4000) Název události statistiky optimalizátoru
occurrence bigint Počet výskytů události optimalizace pro tento čítač
value plovoucí Průměrná hodnota vlastnosti na výskyt události
pdw_node_id int Identifikátor uzlu, na který je tato distribuce zapnutá.

platí pro: Azure Synapse Analytics, Analytics Platform System (PDW)

Dovolení

SQL Server 2019 (15.x) a starší verze a Azure SQL Managed Instance vyžadují oprávnění VIEW SERVER STATE.

SQL Server 2022 (16.x) a novější verze vyžaduje oprávnění VIEW SERVER PERFORMANCE STATE na serveru.

Ve službě Azure SQL Database Basic, S0a S1 cíle služby a pro databáze v elastických fondech účet správce serveru serveru, účet správce Microsoft Entra nebo členství v roli ##MS_ServerStateReader## role serveru. U všech ostatních cílů služby SQL Database je vyžadována role serveru VIEW DATABASE STATE nebo členství v ##MS_ServerStateReader## role serveru.

Poznámky

sys.dm_exec_query_optimizer_info obsahuje následující vlastnosti (čítače). Všechny hodnoty výskytů jsou kumulativní a jsou nastaveny na 0 při restartování systému. Všechny hodnoty polí hodnot jsou nastaveny na NULL při restartování systému. Všechny hodnoty sloupce hodnot, které určují průměr, používají hodnotu výskytu ze stejného řádku jako jmenovatel při výpočtu průměru. Všechny optimalizace dotazů se měří, když SQL Server určuje změny dm_exec_query_optimizer_info, včetně dotazů generovaných uživatelem i systémem. Provádění plánu, který už je uložený v mezipaměti, nemění hodnoty v dm_exec_query_optimizer_info, jsou významné pouze optimalizace.

Počitadlo Výskyt Hodnota
optimizations Celkový počet optimalizací Nejde použít
elapsed time Celkový počet optimalizací Průměrná uplynulá doba na optimalizaci jednotlivého příkazu (dotazu) v sekundách.
final cost Celkový počet optimalizací Průměrné odhadované náklady na optimalizovaný plán v interních nákladových jednotkách
trivial plan Pouze interní Pouze interní
tasks Pouze interní Pouze interní
no plan Pouze interní Pouze interní
search 0 Pouze interní Pouze interní
search 0 time Pouze interní Pouze interní
search 0 tasks Pouze interní Pouze interní
search 1 Pouze interní Pouze interní
search 1 time Pouze interní Pouze interní
search 1 tasks Pouze interní Pouze interní
search 2 Pouze interní Pouze interní
search 2 time Pouze interní Pouze interní
search 2 tasks Pouze interní Pouze interní
gain stage 0 to stage 1 Pouze interní Pouze interní
gain stage 1 to stage 2 Pouze interní Pouze interní
timeout Pouze interní Pouze interní
memory limit exceeded Pouze interní Pouze interní
insert stmt Počet optimalizací pro příkazy INSERT Nejde použít
delete stmt Počet optimalizací pro příkazy DELETE Nejde použít
update stmt Počet optimalizací pro příkazy UPDATE Nejde použít
merge stmt Počet optimalizací pro příkazy MERGE Nejde použít
contains subquery Počet optimalizací dotazu, který obsahuje alespoň jeden poddotaz. Nejde použít
unnest failed Pouze interní Pouze interní
tables Celkový počet optimalizací Průměrný počet tabulek odkazovaných na dotaz optimalizovaný
hints Počet zadání nápovědy Mezi počítané rady patří: JOIN, GROUP, UNION a FORCE ORDER nápovědy k dotazům, možnost FORCE PLAN nastavení a nápovědy pro spojení. Nejde použít
order hint Počet vynucených objednávek spojení Tento čítač není omezen na nápovědu FORCE ORDER. Určení spojovacího algoritmu v rámci dotazu, například INNER HASH JOIN, také vynutí pořadí spojení, které zvýší čítač. Nejde použít
join hint Kolikrát byl algoritmus spojení vynucen nápovědou spojení. Nápovědu k FORCE ORDER dotazu se tento čítač nezvýší. Nejde použít
view reference Počet odkazů na zobrazení v dotazu Nejde použít
remote query Počet optimalizací, na které dotaz odkazoval alespoň na jeden vzdálený zdroj dat, například na tabulku s názvem čtyř částí nebo na výsledek OPENROWSET. Nejde použít
maximum DOP Celkový počet optimalizací Průměrná efektivní MAXDOP hodnota pro optimalizovaný plán Ve výchozím nastavení je efektivní MAXDOP určena maximálním stupněm paralelismu serveru a může být přepsána pro konkrétní dotaz hodnotou nápovědy MAXDOP dotazu.
maximum recursion level Počet optimalizací, ve kterých byla zadána úroveň MAXRECURSION větší než 0 pomocí nápovědy dotazu. Průměrná úroveň MAXRECURSION v optimalizacích, kde byla zadána maximální úroveň rekurze pomocí nápovědy dotazu.
indexed views loaded Pouze interní Pouze interní
indexed views matched Počet optimalizací, ve kterých se porovnává jedno nebo více indexovaných zobrazení Průměrný počet odpovídajících zobrazení
indexed views used Počet optimalizací, kdy se po párování použije jedno nebo více indexovaných zobrazení ve výstupním plánu. Průměrný počet použitých zobrazení
indexed views updated Počet optimalizací příkazu DML, které vytvářejí plán, který udržuje jedno nebo více indexovaných zobrazení. Průměrný počet udržovaných zobrazení
dynamic cursor request Počet optimalizací, ve kterých byl zadán požadavek dynamického kurzoru. Nejde použít
fast forward cursor request Počet optimalizací, ve kterých byl zadán požadavek rychlého kurzoru. Nejde použít

Příklady

A. Zobrazení statistik při provádění optimalizátoru

Jaké jsou aktuální statistiky provádění optimalizátoru pro tuto instanci SQL Serveru?

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. Zobrazení celkového počtu optimalizací

Kolik optimalizací se provádí?

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

C. Průměrná uplynulá doba na optimalizaci

Jaký je průměrný uplynulý čas na optimalizaci?

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

D. Zlomek optimalizací, které zahrnují poddotazy

Jaký zlomek optimalizovaných dotazů obsahoval poddotaz?

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. Zobrazení celkového počtu tipů během optimalizace

Kolik tipů se počítá při zahrnutí FORCE ORDER jako nápovědy k dotazu?

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

Související obsah