آيا از وضعيت رويه‌هاي ذخيره شده‌ي ديتابيس‌هاي اس كيوال سرور خود خبر داريد؟

به لطف امكانات سيستمي اس كيوال سرورهاي 2005 به بعد و DMV هاي آن‌ها، آمارگيري از ريز اتفاقات رخ داده در يك اس كيوال سرور اين روزها بسيار ساده شده است و نيازي به ابزارهاي جانبي براي انجام اين نوع عمليات نيست (يا كمتر هست). در ادامه مروري خواهيم داشت بر يك سري كوئري كه اطلاعات جالبي را در مورد وضعيت رويه‌هاي ذخيره شده‌ي ديتابيس‌هاي شما ارائه مي‌دهند. لازم به ذكر است كه اكثر اين آمارها با هر بار ري استارت سرور، صفر خواهند شد.

آيا مي‌دانيد در يك ديتابيس خاص كداميك از رويه‌هاي ذخيره شده‌ي شما بيش از سايرين مورد استفاده بود و آماري از اين دست؟

use dbName;
SELECT TOP(100) qt.text AS 'SP Name',
     qs.execution_count AS 'Execution Count',
     qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS
     'Calls/Second',
     qs.total_worker_time / qs.execution_count AS 'AvgWorkerTime',
     qs.total_worker_time AS 'TotalWorkerTime',
     qs.total_elapsed_time / qs.execution_count AS 'AvgElapsedTime',
     qs.max_logical_reads,
     qs.max_logical_writes,
     qs.total_physical_reads,
     DATEDIFF(Minute, qs.creation_time, GETDATE()) AS 'Age in Cache'
FROM   sys.dm_exec_query_stats AS qs
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE  qt.dbid = DB_ID() -- Filter by current database
ORDER BY
     qs.execution_count DESC

البته مرتب سازي پيش فرض اين كوئري بر اساس تعداد بار اجرا است (رويه‌هاي ذخيره شده‌ي محبوب!)، مي‌شود آن‌را بر اساس total_worker_time (فشار بر روي CPU سيستم)، total_logical_reads (فشار بر روي حافظه)، total_physical_reads (فشار I/O كوئري‌ها)، total_logical_writes نيز مرتب كرد و نتايج جالب توجهي را بدست آورد.


آيا مي‌دانيد كداميك از رويه‌هاي ذخيره شده‌ي شما بيش از سايرين كامپايل مجدد شده است؟

select top 50
  sql_text.text,
  sql_handle,
  plan_generation_num,
  execution_count,
  dbid,
  objectid
from
  sys.dm_exec_query_stats a
  cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
  plan_generation_num >1
order by plan_generation_num desc

آيا مي‌دانيد آخرين باري كه رويه‌هاي ذخيره شده‌ي شما ويرايش شده‌اند چه زماني بوده است؟

SELECT NAME,
     create_date,
     modify_date
FROM   sys.objects
WHERE  TYPE = 'P'
ORDER BY
     Modify_Date DESC,
     NAME