A DMV-k használata a nézetek használati statisztikáinak és teljesítményének meghatározásához

Ez a cikk a Nézetek használó lekérdezésekteljesítményével kapcsolatos információk lekéréséhez használt módszertant és szkripteket ismerteti. Ezeknek a szkripteknek a célja, hogy az adatbázisban található különböző nézetek használatának és teljesítményének mutatóit adják meg.

sys.dm_exec_query_optimizer_info

A DMV sys.dm_exec_query_optimizer_info statisztikát tesz közzé az SQL Server lekérdezésoptimalizáló által végrehajtott optimalizálásokról. Ezek az értékek kumulatívak, és az SQL Server indításakor megkezdik a rögzítést. A lekérdezésoptimalizálóról további információt a lekérdezésfeldolgozási architektúra útmutatójábantalál.

Az alábbi common_table_expression (CTE) ezt a DMV-t használja a számítási feladattal kapcsolatos információk megadására, például a nézetre hivatkozó lekérdezések százalékos arányára. A lekérdezés által visszaadott eredmények önmagukban nem jeleznek teljesítményproblémát, de a lassú lekérdezések felhasználói panaszaival kombinálva felfedhetik a mögöttes problémákat.

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

Kombinálja ennek a lekérdezésnek az eredményeit a rendszernézetek eredményeivel sys.views, hogy azonosítsa a lekérdezési statisztikákat, a lekérdezési szöveget és a gyorsítótárazott végrehajtási tervet.

sys.views

Az alábbi CTE információt nyújt a végrehajtások számáról, a teljes futási időről és a memóriából beolvasott lapokról. Az eredmények felhasználhatók olyan lekérdezések azonosítására, amelyek optimalizálási jelöltek lehetnek.

Jegyzet

A lekérdezés eredményei az SQL Server verziójától függően változhatnak.

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

A végső lekérdezés a DMV sys.dmv_exec_cached_planshasználatával nyújt információt a fel nem használt nézetekről. A végrehajtási terv gyorsítótára azonban dinamikus, és az eredmények eltérőek lehetnek. Ezért használja ezt a lekérdezést annak megállapítására, hogy valóban használják-e a nézetet vagy sem.

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

Lásd még:

Dinamikus felügyeleti nézetek és függvények