資料庫監控

--1.找出最耗費資料庫CPU前10 名的語法:
SELECT TOP 10
substring(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset WHEN -1 THEN DATALENGTH (st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset ) / 2 ) + 1 ) AS statement_text ,
total_worker_time / 1000 AS total_worker_time_ms,
execution_count,
( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms,
total_logical_reads,
total_logical_reads / execution_count AS avg_logical_reads,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle ) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC

--2.找出查詢時間最久的前50名sql語法:
SELECT TOP 50 [text],*, (total_worker_time / execution_count) AS avgworkertime
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avgworkertime DESC

--3.找出最常使用的sql語法
SELECT TOP 50 [text],*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.plan_generation_num DESC