求sqlserver事件探查器的使用方法
求sqlserver事件探查器的使用方法 如何查找死锁语句 如何查找耗时耗资源的操作？
[解决办法]
SELECT * FROM sys.sysprocesses WHERE blocked>0
--查锁
select resource_type,resource_description,resource_associated_entity_id,
request_mode,request_status,request_session_id from sys.dm_tran_locks
--kill
kill 56
--也可以查
select * from master..syslockinfo
--做成视图查起来更直观
create view DBlocks
as
select request_session_id as spid,
DB_NAME(resource_database_id) as dbname,
case when resource_type='OBJECT' then
else object_id() from sys.dm_tran_locks t left join sys.partitions p
on p.hobt_id=t.resource_associated_entity_id
where request_exec_context_id=DB_ID()
--cpu
--单次执行时间最长的语句
select plan_generation_num,creation_time,
last_execution_time,execution_count,total_worker_time,
total_logical_writes,total_elapsed_time,
qs.max_worker_time,qs.min_worker_time,
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
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
order by max_worker_time desc
--执行次数最多的查询
select plan_generation_num,creation_time,
last_execution_time,execution_count,total_worker_time,
total_logical_writes,total_elapsed_time,
qs.max_worker_time,qs.min_worker_time,
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
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
order by execution_count desc
sp_configure 'showadvanced options',1;
GO
RECONFIGURE;
GO
sp_configure 'blockedprocess threshold',10;
GO
RECONFIGURE;
GO
