Использование динамических административных представлений для определения статистики использования и производительности представленийUse DMVs to Determine Usage Statistics and Performance of Views

В этой статье рассматриваются методы и скрипты, используемые для получения информации о производительности запросов, которые используют представления.This article covers methodology and scripts used to get information about the performance of queries that use Views. Цель этих скриптов — предоставить показатели использования и производительности различных представлений, найденных в базе данных.The intention of these scripts is to provide indicators of use and performance of various Views found in a database.

sys.dm_exec_query_optimizer_infosys.dm_exec_query_optimizer_info

Динамическое административное представление sys.dm_exec_query_optimizer_info предоставляет статистику об оптимизации, выполняемой оптимизатором запросов SQL ServerSQL Server.The DMV sys.dm_exec_query_optimizer_info exposes statistics about the optimizations performed by the SQL ServerSQL Server query optimizer. Эти значения являются накопительными. Их запись начинается при запуске SQL ServerSQL Server.These values are cumulative and begin recording when SQL ServerSQL Server starts. Дополнительные сведения об оптимизации запросов: Руководство по архитектуре обработки запросов.For more information on the query optimizer, see the Query Processing Architecture Guide.

Приведенное ниже обобщенное табличное выражение (CTE) common_table_expression использует это динамическое административное представление, чтобы предоставить сведения о рабочей нагрузке, например долю запросов, которые ссылаются на представление.The below common_table_expression (CTE) uses this DMV to provide information about the workload, such as the percentage of queries that reference a view. Результаты, возвращенные этим запросом, сами по себе не указывают на проблему производительности, но помогают выявлять исходные проблемы в сочетании с жалобами пользователей на запросы с низкой производительностью.The results returned by this query do not indicate a performance problem by themselves, but can expose underlying issues when combined with users' complaints of slow-performing queries.

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, чтобы определить статистику запросов, текст запроса и кэшированный план выполнения.Combine the results of this query with the results of the system view sys.views to identify query statistics, query text, and the cached execution plan.

sys.viewssys.views

В приведенном ниже обобщенном табличном выражении предоставляются сведения о количестве выполнений, общем времени работы и количестве прочитанных страниц из памяти.The below CTE provides information about the number of executions, total run time, and pages read from memory. На основе результатов можно определить запросы, требующие оптимизации.The results can be used to identify queries that may be candidates for optimization.

Примечание

Результаты этого запроса могут различаться в зависимости от используемой версии SQL ServerSQL Server.The results of this query can vary depending on the version of SQL ServerSQL 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.schemaname, st.text, 1) > 0)
      AND (st.dbid = DB_ID())
  ) AS t;
GO

sys.dmv_exec_cached_planssys.dmv_exec_cached_plans

Окончательный запрос предоставляет сведения о неиспользуемых представлениях при помощи динамического административного представления sys.dmv_exec_cached_plans.The final query provides information about unused views by using the DMV sys.dmv_exec_cached_plans. Однако кэшированный план выполнения динамический и результаты могут различаться.However, the execution plan cache is dynamic, and results can vary. Таким образом, необходимо использовать этот запрос через какое-то время, чтобы определить, используется ли представление.As such, use this query over time to determine whether or not a view is actually being used or not.

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

См. также разделSee also

Динамические административные представления и функцииDynamic management views and functions