Używanie widoków DMV do określania statystyk użycia i wydajności widoków

W tym artykule opisano metodologię i skrypty używane do uzyskiwania informacji o wydajności zapytań korzystających z widoków. Celem tych skryptów jest zapewnienie wskaźników użycia i wydajności różnych widoków znalezionych w bazie danych.

sys.dm_exec_query_optimizer_info

sys.dm_exec_query_optimizer_info DMV uwidacznia statystyki dotyczące optymalizacji wykonywanych przez optymalizator zapytań programu SQL Server. Te wartości są skumulowane i rozpoczynają rejestrowanie po uruchomieniu programu SQL Server. Aby uzyskać więcej informacji na temat optymalizatora zapytań, zobacz przewodnik po architekturze przetwarzania zapytań .

Poniższe wyrażenie common_table_expression (CTE) używa tego dynamicznego widoku zarządzania, aby dostarczyć informacje o obciążeniu, takie jak procent zapytań odwołujących się do widoku. Wyniki zwrócone przez to zapytanie nie wskazują na sam problem z wydajnością, ale mogą uwidaczniać podstawowe problemy w połączeniu z skargami użytkowników na wolno działające zapytania.

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

Połącz wyniki tego zapytania z wynikami widoku systemu sys.views, aby zidentyfikować statystyki zapytań, tekst zapytania i zapisany w pamięci podręcznej plan wykonywania.

sys.views

Poniższy kod CTE zawiera informacje o liczbie wykonań, łącznym czasie wykonywania i stronach odczytywanych z pamięci. Wyniki mogą służyć do identyfikowania zapytań, które mogą być kandydatami do optymalizacji.

Notatka

Wyniki tego zapytania mogą się różnić w zależności od wersji programu 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

Ostatnie zapytanie dostarcza informacji o nieużywanych widokach, wykorzystując DMV sys.dmv_exec_cached_plans. Pamięć podręczna planu wykonywania jest jednak dynamiczna, a wyniki mogą się zmieniać. Aby zatem określić, czy widok jest rzeczywiście używany, używaj tego zapytania z biegiem czasu.

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

Zobacz też

dynamiczne widoki zarządzania i funkcje