Udostępnij za pośrednictwem

sys.dm_exec_query_optimizer_info (Transact-SQL)

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Zwraca szczegółowe statystyki dotyczące działania optymalizatora zapytań programu SQL Server. Ten widok można użyć podczas dostrajania obciążenia w celu identyfikowania problemów lub ulepszeń optymalizacji zapytań. Można na przykład użyć łącznej liczby optymalizacji, wartości czasu, której upłynął czas, oraz ostatecznej wartości kosztu, aby porównać optymalizacje zapytań bieżącego obciążenia i wszelkie zmiany zaobserwowane podczas procesu dostrajania. Niektóre liczniki zapewniają dane, które są istotne tylko w przypadku wewnętrznego użycia diagnostycznego programu SQL Server. Te liczniki są oznaczone jako "Tylko wewnętrzne".

Nuta

Aby wywołać to z usługi Azure Synapse Analytics lub Analytics Platform System (PDW), użyj nazwy sys.dm_pdw_nodes_exec_query_optimizer_info. Ta składnia nie jest obsługiwana przez bezserwerową pulę SQL w usłudze Azure Synapse Analytics.

Nazwa Typ danych Opis
counter nvarchar(4000) Nazwa zdarzenia statystyk optymalizatora.
occurrence bigint Liczba wystąpień zdarzenia optymalizacji dla tego licznika.
value zmiennoprzecinkowe Średnia wartość właściwości na wystąpienie zdarzenia.
pdw_node_id Identyfikator węzła, w ramach którego znajduje się ta dystrybucja.

dotyczy: Azure Synapse Analytics, Analytics Platform System (PDW)

Uprawnienia

Program SQL Server 2019 (15.x) i starsze wersje oraz usługa Azure SQL Managed Instance wymagają uprawnień VIEW SERVER STATE.

Program SQL Server 2022 (16.x) i nowsze wersje wymagają VIEW SERVER PERFORMANCE STATE uprawnienia na serwerze.

W usłudze Azure SQL Database w warstwie Podstawowa, S0i cele usługi S1 oraz bazy danych w pulach elastycznych konto administratora serwera , konto administratora Microsoft Entra lub członkostwo w ##MS_ServerStateReader## roli serwera. We wszystkich innych celach usługi SQL Database wymagane jest uprawnienie VIEW DATABASE STATE w bazie danych lub członkostwo w ##MS_ServerStateReader## roli serwera.

Uwagi

sys.dm_exec_query_optimizer_info zawiera następujące właściwości (liczniki). Wszystkie wartości wystąpień są skumulowane i są ustawione na 0 podczas ponownego uruchamiania systemu. Wszystkie wartości pól wartości są ustawione na NULL podczas ponownego uruchamiania systemu. Wszystkie wartości kolumny wartości, które określają średnią, używają wartości wystąpienia z tego samego wiersza co mianownik w obliczeniu średniej. Wszystkie optymalizacje zapytań są mierzone, gdy program SQL Server określa zmiany w dm_exec_query_optimizer_info, w tym zapytania generowane przez użytkownika i generowane przez system. Wykonanie już buforowanego planu nie zmienia wartości w dm_exec_query_optimizer_info, tylko optymalizacje są znaczące.

Lada Zdarzenie Wartość
optimizations Łączna liczba optymalizacji. Nie dotyczy
elapsed time Łączna liczba optymalizacji. Średni czas, który upłynął na optymalizację pojedynczej instrukcji (zapytania), w sekundach.
final cost Łączna liczba optymalizacji. Średni szacowany koszt zoptymalizowanego planu w jednostkach kosztów wewnętrznych.
trivial plan Tylko wewnętrzne Tylko wewnętrzne
tasks Tylko wewnętrzne Tylko wewnętrzne
no plan Tylko wewnętrzne Tylko wewnętrzne
search 0 Tylko wewnętrzne Tylko wewnętrzne
search 0 time Tylko wewnętrzne Tylko wewnętrzne
search 0 tasks Tylko wewnętrzne Tylko wewnętrzne
search 1 Tylko wewnętrzne Tylko wewnętrzne
search 1 time Tylko wewnętrzne Tylko wewnętrzne
search 1 tasks Tylko wewnętrzne Tylko wewnętrzne
search 2 Tylko wewnętrzne Tylko wewnętrzne
search 2 time Tylko wewnętrzne Tylko wewnętrzne
search 2 tasks Tylko wewnętrzne Tylko wewnętrzne
gain stage 0 to stage 1 Tylko wewnętrzne Tylko wewnętrzne
gain stage 1 to stage 2 Tylko wewnętrzne Tylko wewnętrzne
timeout Tylko wewnętrzne Tylko wewnętrzne
memory limit exceeded Tylko wewnętrzne Tylko wewnętrzne
insert stmt Liczba optymalizacji dla instrukcji INSERT. Nie dotyczy
delete stmt Liczba optymalizacji dla instrukcji DELETE. Nie dotyczy
update stmt Liczba optymalizacji dla instrukcji UPDATE. Nie dotyczy
merge stmt Liczba optymalizacji dla instrukcji MERGE. Nie dotyczy
contains subquery Liczba optymalizacji zapytania zawierającego co najmniej jedną podzapytę. Nie dotyczy
unnest failed Tylko wewnętrzne Tylko wewnętrzne
tables Łączna liczba optymalizacji. Średnia liczba tabel, do których odwołuje się zapytanie zoptymalizowane.
hints Ile razy określono wskazówkę. Zliczane wskazówki obejmują: JOIN, GROUP, UNION i wskazówki dotyczące zapytań FORCE ORDER, opcję zestawu FORCE PLAN i wskazówki sprzężenia. Nie dotyczy
order hint Liczba przypadków, w których wymuszono kolejność sprzężenia. Ten licznik nie jest ograniczony do wskazówki FORCE ORDER. Określenie algorytmu sprzężenia w zapytaniu, takiego jak INNER HASH JOIN, wymusza również kolejność sprzężenia, co zwiększa licznik. Nie dotyczy
join hint Ile razy algorytm sprzężenia został wymuszony przez wskazówkę sprzężenia. Wskazówka zapytania FORCE ORDER nie zwiększa tego licznika. Nie dotyczy
view reference Liczba odwołań do widoku w zapytaniu. Nie dotyczy
remote query Liczba optymalizacji, w których zapytanie odwołuje się do co najmniej jednego zdalnego źródła danych, takiego jak tabela z nazwą czteroczęściową lub wynikiem OPENROWSET. Nie dotyczy
maximum DOP Łączna liczba optymalizacji. Średnia efektywna wartość MAXDOP dla zoptymalizowanego planu. Domyślnie obowiązująca MAXDOP jest określana przez maksymalny stopień równoległości opcji konfiguracji serwera i może zostać zastąpiona dla określonego zapytania przez wartość wskazówki MAXDOP zapytania.
maximum recursion level Liczba optymalizacji, w których został określony poziom MAXRECURSION większy niż 0 z wskazówką zapytania. Średni poziom MAXRECURSION w optymalizacjach, w których określono maksymalny poziom rekursji z wskazówką zapytania.
indexed views loaded Tylko wewnętrzne Tylko wewnętrzne
indexed views matched Liczba optymalizacji, w których jest dopasowywany co najmniej jeden indeksowany widok. Średnia liczba dopasowanych widoków.
indexed views used Liczba optymalizacji, w których co najmniej jeden indeksowany widok jest używany w planie danych wyjściowych po dopasowaniu. Średnia liczba używanych widoków.
indexed views updated Liczba optymalizacji instrukcji DML, która generuje plan, który utrzymuje jeden lub więcej widoków indeksowanych. Średnia liczba obsługiwanych widoków.
dynamic cursor request Liczba optymalizacji, w których określono dynamiczne żądanie kursora. Nie dotyczy
fast forward cursor request Liczba optymalizacji, w których określono żądanie kursora szybkiego przesyłania dalej. Nie dotyczy

Przykłady

A. Wyświetlanie statystyk dotyczących wykonywania optymalizatora

Jakie są bieżące statystyki wykonywania optymalizatora dla tego wystąpienia programu SQL Server?

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. Wyświetlanie całkowitej liczby optymalizacji

Ile optymalizacji jest wykonywanych?

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

C. Średni czas, który upłynął na optymalizację

Jaki jest średni czas, który upłynął na optymalizację?

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

D. Ułamek optymalizacji obejmujących podzapytania

Jaki ułamek zoptymalizowanych zapytań zawierał podzapytywanie?

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. Wyświetlanie całkowitej liczby wskazówek podczas optymalizacji

Ile wskazówek jest liowanych, gdy FORCE ORDER jest uwzględniona jako wskazówka zapytania?

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

Powiązana zawartość