求sqlserver事件探查器的使用方法解决办法

求sqlserver事件探查器的使用方法
求sqlserver事件探查器的使用方法 如何查找死锁语句 如何查找耗时耗资源的操作？

[解决办法]

SQL code

SELECT * FROM sys.sysprocesses WHERE blocked>0
[解决办法]
SQL code
--查锁select resource_type,resource_description,resource_associated_entity_id,request_mode,request_status,request_session_id from sys.dm_tran_locks--killkill 56--也可以查select * from master..syslockinfo--做成视图查起来更直观create view DBlocksasselect 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 pon p.hobt_id=t.resource_associated_entity_idwhere 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_offsetwhen -1 then datalength(st.text) else qs.statement_end_offset end -qs.statement_start_offset/2)+1)) as statement_textfrom sys.dm_exec_query_stats as qscross apply sys.dm_exec_sql_text(qs.sql_handle) as storder 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_offsetwhen -1 then datalength(st.text) else qs.statement_end_offset end -qs.statement_start_offset/2)+1)) as statement_textfrom sys.dm_exec_query_stats as qscross apply sys.dm_exec_sql_text(qs.sql_handle) as storder by execution_count desc
[解决办法]
profile 的话 把  Blocked Process Report勾上
[解决办法]
可以用下面的语句来赛选超过多久的堵塞会被记录下来
SQL code
sp_configure 'showadvanced options',1;GORECONFIGURE;GOsp_configure 'blockedprocess threshold',10;GORECONFIGURE;GO
[解决办法]
使用 SQL Server Profiler 分析死锁
[解决办法]
这个在网上找有图的会比在论坛一大串文字告诉你有意义得多
[解决办法]
看看这个 http://www.cnblogs.com/qiuweiguo/archive/2011/11/29/2267828.html 希望对你有帮助