Verwenden von DMVs zum Bestimmen von Verwendungsstatistiken und der Leistung von Sichten
Dieser Artikel behandelt Methoden und Skripts, mit denen Sie Informationen zur Leistung von Abfragen, die Sichten verwenden abrufen können. Der Zweck dieser Skripts besteht darin, Indikatoren für die Nutzung und Leistung verschiedener Sichten in einer Datenbank zu liefern.
sys.dm_exec_query_optimizer_info
Die dynamische Verwaltungssicht (DMV) sys.dm_exec_query_optimizer_info macht Statistiken zu den Optimierungen durch den SQL Server-Abfrageoptimierer verfügbar. Diese Werte sind kumulativ und werden erfasst, wenn SQL Server gestartet wird. Weitere Informationen zum Abfrageoptimierer finden Sie im Handbuch zur Architektur der Abfrageverarbeitung.
Der common_table_expression (CTE, allgemeiner Tabellenausdruck) unten verwendet diese DMV, um Informationen zur Workload bereitzustellen, z.B. den Prozentsatz der Abfragen, die auf eine Sicht verweisen. Die von dieser Abfrage zurückgegebenen Ergebnisse deuten an sich nicht auf ein Leistungsproblem hin, sondern weisen ggf. auf zugrunde liegende Probleme hin, wenn sie mit Benutzerbeschwerden über langsame Abfragen kombiniert werden.
WITH CTE_QO AS
(
SELECT
occurrence
FROM
sys.dm_exec_query_optimizer_info
WHERE
([counter] = 'optimizations')
),
QOInfo AS
(
SELECT
[counter]
,[%] = CAST((occurrence * 100.00)/(SELECT occurrence FROM CTE_QO) AS DECIMAL(5, 2))
FROM
sys.dm_exec_query_optimizer_info
WHERE
[counter] IN ('optimizations'
,'trivial plan'
,'no plan'
,'search 0'
,'search 1'
,'search 2'
,'timeout'
,'memory limit exceeded'
,'insert stmt'
,'delete stmt'
,'update stmt'
,'merge stmt'
,'contains subquery'
,'view reference'
,'remote query'
,'dynamic cursor request'
,'fast forward cursor request'
)
)
SELECT
[optimizations] AS [optimizations %]
,[trivial plan] AS [trivial plan %]
,[no plan] AS [no plan %]
,[search 0] AS [search 0 %]
,[search 1] AS [search 1 %]
,[search 2] AS [search 2 %]
,[timeout] AS [timeout %]
,[memory limit exceeded] AS [memory limit exceeded %]
,[insert stmt] AS [insert stmt %]
,[delete stmt] AS [delete stmt]
,[update stmt] AS [update stmt]
,[merge stmt] AS [merge stmt]
,[contains subquery] AS [contains subquery %]
,[view reference] AS [view reference %]
,[remote query] AS [remote query %]
,[dynamic cursor request] AS [dynamic cursor request %]
,[fast forward cursor request] AS [fast forward cursor request %]
FROM
QOInfo
PIVOT (MAX([%]) FOR [counter]
IN ([optimizations]
,[trivial plan]
,[no plan]
,[search 0]
,[search 1]
,[search 2]
,[timeout]
,[memory limit exceeded]
,[insert stmt]
,[delete stmt]
,[update stmt]
,[merge stmt]
,[contains subquery]
,[view reference]
,[remote query]
,[dynamic cursor request]
,[fast forward cursor request])) AS p;
GO
Kombinieren Sie die Ergebnisse dieser Abfrage mit den Ergebnissen der Systemansicht sys.views, um Abfragestatistiken, Abfragetext und den zwischengespeicherten Ausführungsplan zu ermitteln.
sys.views
Der folgende allgemeine Tabellenausdruck liefert Informationen über die Anzahl von Ausführungen, die Gesamtlaufzeit und die aus dem Arbeitsspeicher gelesenen Seiten. Mithilfe der Ergebnisse lassen sich Abfragen identifizieren, die möglicherweise optimiert werden können.
Hinweis
Die Ergebnisse dieser Abfrage können je nach SQL Server-Version variieren.
WITH CTE_VW_STATS AS
(
SELECT
SCHEMA_NAME(vw.schema_id) AS schemaname
,vw.name AS viewname
,vw.object_id AS viewid
FROM
sys.views AS vw
WHERE
(vw.is_ms_shipped = 0)
INTERSECT
SELECT
SCHEMA_NAME(o.schema_id) AS schemaname
,o.Name AS name
,st.objectid AS viewid
FROM
sys.dm_exec_cached_plans cp
CROSS APPLY
sys.dm_exec_sql_text(cp.plan_handle) st
INNER JOIN
sys.objects o ON st.[objectid] = o.[object_id]
WHERE
st.dbid = DB_ID()
)
SELECT
vw.schemaname
,vw.viewname
,vw.viewid
,DB_NAME(t.databaseid) AS databasename
,t.databaseid
,t.*
FROM
CTE_VW_STATS AS vw
CROSS APPLY
(
SELECT
st.dbid AS databaseid
,st.text
,qp.query_plan
,qs.*
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.plan_handle) AS st
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE
(CHARINDEX(vw.viewname, st.text, 1) > 0)
AND (st.dbid = DB_ID())
) AS t;
GO
sys.dmv_exec_cached_plans
Die letzte Abfrage stellt mithilfe der DMV sys.dmv_exec_cached_plans Informationen zu unbenutzte Ansichten bereit. Der Ausführungsplancache ist jedoch dynamisch und die Ergebnisse können variieren. Führen Sie diese Abfrage daher im Laufe der Zeit aus, um festzustellen, ob eine Ansicht tatsächlich verwendet wird.
SELECT
SCHEMA_NAME(vw.schema_id) AS schemaname
,vw.name AS name
,vw.object_id AS viewid
FROM
sys.views AS vw
WHERE
(vw.is_ms_shipped = 0)
EXCEPT
SELECT
SCHEMA_NAME(o.schema_id) AS schemaname
,o.name AS name
,st.objectid AS viewid
FROM
sys.dm_exec_cached_plans cp
CROSS APPLY
sys.dm_exec_sql_text(cp.plan_handle) st
INNER JOIN
sys.objects o ON st.[objectid] = o.[object_id]
WHERE
st.dbid = DB_ID();
GO