この記事では、 Viewsを使ったクエリのパフォーマンスに関する情報を得るために使われる方法論とスクリプトについて説明します。 これらのスクリプトの目的は、データベース内で見つかるさまざまなビューの使用やパフォーマンスを示す指標を提供することです。
sys.dm_exec_query_optimizer_info
DMV sys.dm_exec_query_optimizer_infoは、SQL Serverクエリオプティマイザによる最適化に関する統計を公開します。 これらの値は累積され、SQL Serverが起動すると記録が始まります。 クエリオプティマイザーの詳細については、 クエリ処理アーキテクチャガイドをご覧ください。
以下の common_table_expression(CTE)は、この DMV を使って、ビューを参照するクエリの割合など、ワークロードに関する情報を提供します。 このクエリで返される結果自体はパフォーマンスの問題を示すものではありませんが、ユーザーのクエリの動作遅延に対する苦情と組み合わさることで根本的な問題を露呈する可能性があります。
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
このクエリの結果とシステムビューの シスビュー の結果を組み合わせて、クエリ統計、クエリテキスト、キャッシュされた実行計画を特定します。
sys.views
以下のCTEは、実行回数、総実行時間、メモリから読み取ったページ数に関する情報を提供します。 その結果は、最適化の候補となるクエリを特定するために利用できます。
Note
このクエリの結果はSQL Serverのバージョンによって異なります。
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
最後のクエリはDMV sys.dmv_exec_cached_plansを使って未使用のビュー情報を提供します。 しかし、実行計画キャッシュは動的であり、結果は変動することがあります。 したがって、このクエリを時間をかけて、そのビューが実際に使用されているかどうかを判断してください。
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