DMV를 사용하여 사용량 통계 및 뷰 성능 확인

  • 아티클

이 문서에서는 뷰를 사용하는 쿼리의 성능에 대한 정보를 가져오는 데 사용되는 방법론 및 스크립트에 대해 설명합니다. 이러한 스크립트는 데이터베이스에 있는 다양한 뷰의 사용 및 성능 지표를 제공하기 위한 것입니다.

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 의 결과와 결합하여 쿼리 통계, 쿼리 텍스트 및 캐시된 실행 계획을 식별합니다.

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

참고 항목

동적 관리 뷰 및 함수