DMV를 사용하여 사용량 통계 및 뷰 성능 확인
이 문서에서는 Views 사용하는 쿼리의성능에 대한 정보를 가져오는 데 사용되는 방법론 및 스크립트에 대해 설명합니다. 이러한 스크립트는 데이터베이스에 있는 다양한 뷰의 사용 및 성능 지표를 제공하기 위한 것입니다.
sys.dm_exec_query_optimizer_info
DMV sys.dm_exec_query_optimizer_info SQL Server 쿼리 최적화 프로그램에서 수행하는 최적화에 대한 통계를 노출합니다. 이러한 값은 누적되며 SQL Server가 시작될 때 기록을 시작합니다. 쿼리 최적화 프로그램에 대한 자세한 내용은 쿼리 처리 아키텍처 가이드참조하세요.
아래 CTE(common_table_expression)는 이 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 시스템 뷰의 결과와 결합하여 쿼리 통계, 쿼리 텍스트 및 캐시된 실행 계획을 식별합니다.
sys.views
아래 CTE는 실행 수, 총 실행 시간 및 메모리에서 읽은 페이지에 대한 정보를 제공합니다. 결과를 사용하여 최적화 후보가 될 수 있는 쿼리를 식별할 수 있습니다.
메모
이 쿼리의 결과는 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