Используйте динамические административные представления для определения статистики использования и производительности представлений

В этой статье рассматриваются методология и сценарии, используемые для получения сведений о производительности запросов, использующих представления. Цель этих скриптов — предоставить показатели использования и производительности различных представлений, найденных в базе данных.

sys.dm_exec_query_optimizer_info

DMV sys.dm_exec_query_optimizer_info предоставляет статистику об оптимизациях, выполненных оптимизатором запросов SQL Server. Эти значения являются накопительными и начинают запись при запуске SQL Server. Дополнительные сведения об оптимизаторе запросов см. в руководстве по архитектуре обработки запросов.

В приведенной ниже выражении общего табличного выражения (CTE) это динамическое административное представление используется для предоставления сведений о рабочей нагрузке, таких как доля запросов, ссылающихся на представление. Результаты, возвращаемые этим запросом, не указывают на проблему производительности сами по себе, но могут предоставлять базовые проблемы при сочетании с жалобами пользователей на медленные запросы.

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

См. также

динамические административные представления и функции