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
--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

------解决方案--------------------
profile 的话 把  Blocked Process Report勾上

------解决方案--------------------
可以用下面的语句来赛选超过多久的堵塞会被记录下来
SQL code

sp_configure 'showadvanced options',1;
GO

RECONFIGURE;
GO

sp_configure 'blockedprocess threshold',10;
GO

RECONFIGURE;
GO

------解决方案--------------------
使用 SQL Server Profiler 分析死锁

------解决方案--------------------
这个在网上找有图的会比在论坛一大串文字告诉你有意义得多

------解决方案--------------------

看看这个 http://www.cnblogs.com/qiuweiguo/archive/2011/11/29/2267828.html 希望对你有帮助