Kullanım İstatistiklerini ve Görünümlerin Performansını Belirlemek için DMV'leri Kullanma

Bu makale, Görünümler kullanan sorgularınperformansı hakkında bilgi edinmek için kullanılan metodoloji ve betikleri kapsar. Bu betiklerin amacı, veritabanında bulunan çeşitli Görünümlerin kullanım ve performans göstergelerini sağlamaktır.

sys.dm_exec_query_optimizer_info

DMV sys.dm_exec_query_optimizer_info, SQL Server sorgu iyileştiricisi tarafından gerçekleştirilen iyileştirmelerle ilgili istatistikleri kullanıma sunar. Bu değerler kümülatiftir ve SQL Server başladığında kayda başlar. Sorgu iyileştiricisi hakkında daha fazla bilgi için bkz. Sorgu İşleme Mimarisi Kılavuzu.

Aşağıdaki common_table_expression (CTE), bir görünüme başvuran sorguların yüzdesi gibi iş yükü hakkında bilgi sağlamak için bu DMV'yi kullanır. Bu sorgu tarafından döndürülen sonuçlar tek başına bir performans sorununa işaret etmese de, kullanıcıların yavaş çalışan sorgularla ilgili şikayetleriyle birleştirildiğinde temel sorunları ortaya çıkarabilir.

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

Sorgu istatistiklerini, sorgu metnini ve önbelleğe alınmış yürütme planını tanımlamak için bu sorgunun sonuçlarını sistem görünümü sys.views sonuçlarıyla birleştirin.

sys.views

Aşağıdaki CTE yürütme sayısı, toplam çalışma süresi ve bellekten okunan sayfalar hakkında bilgi sağlar. Sonuçlar, iyileştirme adayı olabilecek sorguları tanımlamak için kullanılabilir.

Not

Bu sorgunun sonuçları SQL Server sürümüne bağlı olarak değişebilir.

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

Son sorgu, DMV sys.dmv_exec_cached_planskullanarak kullanılmayan görünümler hakkında bilgi sağlar. Ancak yürütme planı önbelleği dinamiktir ve sonuçlar farklılık gösterebilir. Bu nedenle, bir görünümün gerçekten kullanılıp kullanılmadığını belirlemek için zaman içinde bu sorguyu kullanın.

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

Ayrıca bkz.

dinamik yönetim görünümleri ve işlevleri