死锁查询

SELECT SPID=p.spid,

       DBName = convert(CHAR(20),d.name),

       ProgramName = program_name,

       LoginName = convert(CHAR(20),l.name),

       HostName = convert(CHAR(20),hostname),

       Status = p.status,

       BlockedBy = p.blocked,

       LoginTime = login_time,

       QUERY = CAST(TEXT AS VARCHAR(MAX))

FROM   MASTER.dbo.sysprocesses p

       INNER JOIN MASTER.dbo.sysdatabases d

         ON p.dbid = d.dbid

       INNER JOIN MASTER.dbo.syslogins l

         ON p.sid = l.sid

       CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE  p.blocked = 0

       AND EXISTS (SELECT 1

                   FROM   MASTER..sysprocesses p1

                   WHERE  p1.blocked = p.spid)

 

 

   kill spid

 

--2、查看当前耗时SQL
SELECT top 10
s.[session_id],
r.[start_time],
DATEDIFF(SECOND,r.start_time,GETDATE()) AS elapsed_seconds,
r.[status] AS RequestStatus,
DB_NAME(r.database_id) AS DatabaseName,
r.[reads],
r.[writes],
r.[logical_reads],
r.cpu_time,
r.total_elapsed_time,s.[status] AS SessionStatus,
SUBSTRING (qt.text,
r.statement_start_offset/2,
(CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE r.statement_end_offset END -r.statement_start_offset)/2) AS ExecutingSQL,
qp.query_plan
FROM sys.dm_exec_requests (nolock) r
INNER JOIN sys.dm_exec_sessions (nolock) s
ON r.session_id=s.session_id
LEFT JOIN sys.dm_exec_connections (nolock) c
ON c.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) as qp
order by 3 desc