sys.dm_exec_query_optimizer_info (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Gibt detaillierte Statistiken zum Vorgang des SQL Server-Abfrageoptimierrs zurück. Diese Sicht können Sie beim Optimieren einer Arbeitsauslastung verwenden, um Probleme oder Verbesserungen bei der Abfrageoptimierung zu identifizieren. Sie können beispielsweise anhand der Gesamtanzahl der Optimierungen, des Wertes für die verstrichene Zeit und des Endkostenwertes die Abfrageoptimierungen der aktuellen Arbeitsauslastung und sämtliche während des Optimierungsvorgangs beobachteten Änderungen vergleichen. Einige Leistungsindikatoren stellen Daten bereit, die nur für die interne Sql Server-Diagnoseverwendung relevant sind. Diese Leistungsindikatoren sind als "Internal only" gekennzeichnet.

Hinweis

Um dies von Azure Synapse Analytics oder Analytics Platform System (PDW) aufzurufen, verwenden Sie den Namen sys.dm_pdw_nodes_exec_query_optimizer_info. Diese Syntax wird vom serverlosen SQL-Pool in Azure Synapse Analytics nicht unterstützt.

Name Datentyp Beschreibung
counter nvarchar(4000) Name des Statistikereignisses des Abfrageoptimierers.
occurrence bigint Anzahl der Vorkommen von Optimierungsereignissen für diesen Leistungsindikator.
value float Durchschnittlicher Eigenschaftswert pro Ereignisvorkommen.
pdw_node_id int Der Bezeichner für den Knoten, auf dem sich diese Verteilung befindet.

Gilt für: Azure Synapse Analytics, Analytics Platform System (PDW)

Berechtigungen

SQL Server 2019 (15.x) und frühere Versionen sowie Azure SQL verwaltete Instanz erfordern VIEW SERVER STATE die Berechtigung.

SQL Server 2022 (16.x) und höhere Versionen erfordern VIEW SERVER PERFORMANCE STATE die Berechtigung auf dem Server.

Für Azure SQL-Datenbank Standard-, S0- und S1-Dienstziele sowie für Datenbanken in elastischen Pools ist das Serveradministratorkonto, das Microsoft Entra-Administratorkonto oder die Mitgliedschaft in der Serverrolle ##MS_ServerStateReader## erforderlich. Für alle anderen SQL-Datenbank Dienstziele ist entweder die VIEW DATABASE STATE Berechtigung für die Datenbank oder die Mitgliedschaft in der Serverrolle ##MS_ServerStateReader## erforderlich.

Hinweise

sys.dm_exec_query_optimizer_info enthält die folgenden Eigenschaften (Leistungsindikatoren). Alle Vorkommenswerte sind kumulativ und werden beim Systemneustart festgelegt 0 . Alle Werte für Wertefelder werden beim Systemneustart auf NULL "Gesetzt" festgelegt. Alle Wertspaltenwerte, die einen Durchschnitt angeben, verwenden den Vorkommenwert aus derselben Zeile als Nenner bei der Berechnung des Durchschnitts. Alle Abfrageoptimierungen werden gemessen, wenn SQL Server Änderungen dm_exec_query_optimizer_infoan , einschließlich vom Benutzer generierter und vom System generierter Abfragen, bestimmt. Die Ausführung eines bereits zwischengespeicherten Plans ändert keine Werte in dm_exec_query_optimizer_info, es sind nur Optimierungen von Bedeutung.

Leistungsindikator Vorkommen Wert
optimizations Gesamtzahl der Optimierungen. Nicht zutreffend
elapsed time Gesamtzahl der Optimierungen. Durchschnittlich verstrichene Zeit pro Optimierung einer einzelnen Anweisung (Abfrage), in Sekunden.
final cost Gesamtzahl der Optimierungen. Durchschnittliche geschätzte Kosten für einen optimierten Plan in internen Kosteneinheiten.
trivial plan Nur intern Nur intern
tasks Nur intern Nur intern
no plan Nur intern Nur intern
search 0 Nur intern Nur intern
search 0 time Nur intern Nur intern
search 0 tasks Nur intern Nur intern
search 1 Nur intern Nur intern
search 1 time Nur intern Nur intern
search 1 tasks Nur intern Nur intern
search 2 Nur intern Nur intern
search 2 time Nur intern Nur intern
search 2 tasks Nur intern Nur intern
gain stage 0 to stage 1 Nur intern Nur intern
gain stage 1 to stage 2 Nur intern Nur intern
timeout Nur intern Nur intern
memory limit exceeded Nur intern Nur intern
insert stmt Anzahl der Optimierungen, die für INSERT Anweisungen bestimmt sind. Nicht zutreffend
delete stmt Anzahl der Optimierungen, die für DELETE Anweisungen bestimmt sind. Nicht zutreffend
update stmt Anzahl der Optimierungen, die für UPDATE Anweisungen bestimmt sind. Nicht zutreffend
contains subquery Anzahl der Optimierungen für eine Abfrage, die mindestens eine Unterabfrage enthält. Nicht zutreffend
unnest failed Nur intern Nur intern
tables Gesamtzahl der Optimierungen. Gesamtzahl der Tabellen, auf die pro optimierte Abfrage verwiesen wird.
hints Häufigkeit, mit der ein Hinweis angegeben wurde. Zu den gezählten Hinweisen gehören: JOIN, GROUPund FORCE ORDER UNION Abfragehinweise, FORCE PLAN Festlegen von Optionen und Verknüpfungshinweisen. Nicht zutreffend
order hint Anzahl der Male, in denen die Verknüpfungsreihenfolge erzwungen wurde. Dieser Indikator ist nicht auf den FORCE ORDER Hinweis beschränkt. Wenn Sie einen Verknüpfungsalgorithmus innerhalb einer Abfrage angeben, z. B. eine INNER HASH JOIN, erzwingt auch die Verknüpfungsreihenfolge, wodurch der Zähler erhöht wird. Nicht zutreffend
join hint Häufigkeit, mit der der Joinalgorithmus von einem Joinhinweis erzwungen wurde. Der FORCE ORDER Abfragehinweis erhöht diesen Leistungsindikator nicht. Nicht zutreffend
view reference Häufigkeit, mit der in einer Abfrage auf eine Ansicht verwiesen wird. Nicht zutreffend
remote query Die Anzahl der Optimierungen, bei denen auf die Abfrage mindestens eine Remotedatenquelle verweist, z. B. eine Tabelle mit einem vierteiligen Namen oder einem OPENROWSET Ergebnis. Nicht zutreffend
maximum DOP Gesamtzahl der Optimierungen. Durchschnittlicher effektiver MAXDOP Wert für einen optimierten Plan. Standardmäßig wird der effektive MAXDOP Wert durch die maximale Parallelitätsserverkonfigurationsoption bestimmt und kann für eine bestimmte Abfrage durch den Wert des MAXDOP Abfragehinweiss überschrieben werden.
maximum recursion level Die Anzahl der Optimierungen, bei denen eine MAXRECURSION Ebene größer als 0 mit dem Abfragehinweis angegeben wurde. Durchschnittliche MAXRECURSION Ebene in Optimierungen, bei denen eine maximale Rekursionsstufe mit dem Abfragehinweis angegeben wurde.
indexed views loaded Nur intern Nur intern
indexed views matched Die Anzahl der Optimierungen, bei denen eine oder mehrere indizierte Ansichten übereinstimmen. Durchschnittliche Anzahl der übereinstimmenden Sichten.
indexed views used Anzahl der Optimierungen, bei denen nach dem Abgleich mindestens eine indizierte Sicht im Ausgabeplan verwendet wird. Durchschnittliche Anzahl der verwendeten Sichten.
indexed views updated Anzahl der Optimierungen einer DML-Anweisung, die einen Plan erstellen, von dem mindestens eine indizierte Sicht verwaltet wird. Durchschnittliche Anzahl der verwalteten Sichten.
dynamic cursor request Anzahl der Optimierungen, in denen eine dynamische Cursoranforderung angegeben wurde. Nicht zutreffend
fast forward cursor request Anzahl der Optimierungen, bei denen eine Schnellweiterleitungscursoranforderung angegeben wurde. Nicht zutreffend
merge stmt Anzahl der Optimierungen, die für MERGE Anweisungen bestimmt sind. Nicht verfügbar

Beispiele

A. Anzeigen von Statistiken zur Optimiererausführung

Was sind die aktuellen Optimiererausführungsstatistiken für diese Instanz von SQL Server?

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. Anzeigen der Gesamtzahl der Optimierungen

Wie viele Optimierungen werden ausgeführt?

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

C. Durchschnittliche verstrichene Zeit pro Optimierung

Wie lange dauert eine Optimierung im Durchschnitt?

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

D: Anteil der Optimierungen mit Unterabfragen

Wie hoch liegt der Anteil der optimierten Abfragen mit einer Unterabfrage?

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. Anzeigen der Gesamtzahl der Hinweise während der Optimierung

Wie viele Hinweise werden gezählt, wenn FORCE ORDER sie als Abfragehinweis enthalten sind?

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

Zugehöriger Inhalt