找出性能差的语句
找出性能差的语句
1 找出效率低的语句
SELECT
TOP
10
qs.total_worker_time ,qs.total_logical_reads,qs.last_logical_writes, qs.plan_handle,
qs.execution_count,
( SELECT SUBSTRING ( text , qs.statement_start_offset / 2 + 1 ,
( CASE WHEN qs.statement_end_offset = - 1
THEN LEN ( CONVERT ( nvarchar ( max ), text )) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2 )
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats qs
ORDER BY total_worker_time DESC
qs.total_worker_time ,qs.total_logical_reads,qs.last_logical_writes, qs.plan_handle,
qs.execution_count,
( SELECT SUBSTRING ( text , qs.statement_start_offset / 2 + 1 ,
( CASE WHEN qs.statement_end_offset = - 1
THEN LEN ( CONVERT ( nvarchar ( max ), text )) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2 )
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats qs
ORDER BY total_worker_time DESC
2 查看这些语句对应的client, hostname,loginname
select
s.
host_name
,s.PROGRAM_NAME,s.login_name, t.
text
From
sys.dm_exec_requests r
cross apply
sys.dm_exec_sql_text(r.sql_handle) t
inner join sys.dm_exec_sessions s on r.session_id = s.session_id
where t. text like ' %sqlstatement% ' -- --- place your sql statement here
and s.session_id <> @@SPID
cross apply
sys.dm_exec_sql_text(r.sql_handle) t
inner join sys.dm_exec_sessions s on r.session_id = s.session_id
where t. text like ' %sqlstatement% ' -- --- place your sql statement here
and s.session_id <> @@SPID
3确定这些语句隶属的存储过程
通过第一步得到的plan_handle,代入dm_exec_query_plan,得到具体的存储过程的名称。如果返回空,则说明该语句是ad-hoc或者是prepared或者是在存储过程内部使用批结字符串弄出来的语句。如果是以上几种情况,我们是无法定位对象的,只能让程序员们逐个排查了
select
OBJECT_NAME
(objectid)
from
sys.dm_exec_query_plan ( plan_handle )