方法一:
SELECT TOP 10 TEXT AS 'SQL Statement'
,last_execution_time AS 'Last Execution Time'
,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
,execution_count AS "Execution Count",qs.total_physical_reads,qs.total_logical_writes
,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC
示例:
方法二:
SELECT DB_ID(DB.dbid) '数据库名'
, OBJECT_ID(db.objectid) '对象'
, QS.creation_time '编译计划的时间'
, QS.last_execution_time '上次执行计划的时间'
, QS.execution_count '执行的次数'
, QS.total_elapsed_time / 1000 '占用的总时间(秒)'
, QS.total_physical_reads '物理读取总次数'
, QS.total_worker_time / 1000 'CPU 时间总量(秒)'
, QS.total_logical_writes '逻辑写入总次数'
, QS.total_logical_reads N'逻辑读取总次数'
, QS.total_elapsed_time / 1000 N'总花费时间(秒)'
, 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 '执行语句'
FROM sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN
( SELECT *
FROM sys.dm_exec_cached_plans cp CROSS APPLY
sys.dm_exec_query_plan(cp.plan_handle)
) DB
ON QS.plan_handle = DB.plan_handle
where 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) not like '%fetch%'
ORDER BY QS.total_elapsed_time / 1000 DESC
示例:
方法三:
SELECT s2.dbid,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time,
max_worker_time, total_physical_reads, last_physical_reads,
min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.total_worker_time desc
示例:
方式四:
选取了前10个最耗CPU时间的会话:
SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb'
ORDER BY [cpu_time] DESC
示例:
资料来源:
http://www.cnblogs.com/sdadx/p/6510213.html
http://blog.csdn.net/tianlianchao1982/article/details/5562035
https://www.cnblogs.com/xunziji/archive/2012/03/20/2408348.html