Menggunakan DMV untuk Menentukan Statistik Penggunaan dan Performa Tampilan

Artikel ini membahas metodologi dan skrip yang digunakan untuk mendapatkan informasi tentang performa kueri yang menggunakan Tampilan. Niat skrip ini adalah untuk memberikan indikator penggunaan dan performa berbagai Tampilan yang ditemukan dalam database.

sys.dm_exec_query_optimizer_info

DMV sys.dm_exec_query_optimizer_info mengekspos statistik tentang pengoptimalan yang dilakukan oleh pengoptimal kueri SQL Server. Nilai-nilai ini bersifat kumulatif dan mulai merekam saat SQL Server dimulai. Untuk informasi selengkapnya tentang pengoptimal kueri, lihat Panduan Arsitektur Pemrosesan Kueri.

Common_table_expression di bawah ini (CTE) menggunakan DMV ini untuk memberikan informasi tentang beban kerja, seperti persentase kueri yang mereferensikan tampilan. Hasil yang dikembalikan oleh kueri ini tidak menunjukkan masalah performa sendiri, tetapi dapat mengekspos masalah yang mendasarinya saat dikombinasikan dengan keluhan pengguna tentang kueri berkinerja lambat.

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

Gabungkan hasil kueri ini dengan hasil tampilan sistem sys.views untuk mengidentifikasi statistik kueri, teks kueri, dan rencana eksekusi yang di-cache.

sys.views

CTE di bawah ini menyediakan informasi tentang jumlah eksekusi, total waktu proses, dan halaman yang dibaca dari memori. Hasilnya dapat digunakan untuk mengidentifikasi kueri yang mungkin menjadi kandidat pengoptimalan.

Catatan

Hasil kueri ini dapat bervariasi tergantung pada versi 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

Kueri akhir menyediakan informasi tentang tampilan yang tidak digunakan dengan menggunakan sys.dmv_exec_cached_plans DMV. Namun, cache rencana eksekusi bersifat dinamis, dan hasilnya dapat bervariasi. Dengan demikian, gunakan kueri ini dari waktu ke waktu untuk menentukan apakah tampilan benar-benar digunakan atau tidak.

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

Baca juga

Tampilan dan fungsi manajemen dinamis