Database query super Slow, database deadlock view and resolution

Today to help colleagues solve the problem, the page report "Waiting for the operation is outdated", set breakpoints to find the database query statement at the exception, check the database a pass, found that the connection database is not connected, searched a circle to find a solution. Keep the spare.

First, the deadlock is detected, SQL statements are available
SELECT blocking_session_id ' Blocking process id ', Wait_duration_ms ' Wait Time (msec) ', session_id ' (Session ID) ' from sys.dm_os_waiting_tasks

or create the following stored procedure, query it out

 Use [Master]GO/** * * * object:storedprocedure [dbo].    [Sp_who_lock] Script date:01/03/2017 10:58:03 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOALTER procedure [dbo].[Sp_who_lock] asbeginDeclare @spid int,@bl int, @intTransactionCountOnEntry  int,        @intRowcount    int,        @intCountProperties   int,        @intCounter    int Create Table#tmp_lock_who (IDint Identity(1,1), spidsmallint, BLsmallint)  IF @ @ERROR<>0 RETURN @ @ERROR  Insert  into#tmp_lock_who (SPID,BL)Select  0, blocked from(Select *  fromsysprocesseswhereBlocked>0) Awhere  not exists(Select *  from(Select *  fromsysprocesseswhereBlocked>0) bwhereA.blocked=spid)Union SelectSpid,blocked fromsysprocesseswhereBlocked>0 IF @ @ERROR<>0 RETURN @ @ERROR   --number of records found for temporary table Select  @intCountProperties = Count(*),@intCounter = 1  from#tmp_lock_whoIF @ @ERROR<>0 RETURN @ @ERROR   if @intCountProperties=0  Select 'No blocking and deadlock information now'  asmessage--Cycle Start while @intCounter <= @intCountPropertiesbegin--take the first record  Select  @spid =spid@bl =BL from#tmp_lock_whowhereId= @intCounter  begin  if @spid =0             Select 'causing the database deadlock is:'+ CAST(@bl  as VARCHAR(Ten))+ 'process number, which executes the following SQL syntax' Else            Select 'process number spid:'+ CAST(@spid  as VARCHAR(Ten))+ 'was' + 'process number spid:'+ CAST(@bl  as VARCHAR(Ten))+'The SQL syntax that the current process executes is blocked as follows' DBCCInputBuffer (@bl ) End --The loop pointer moves down Set @intCounter = @intCounter + 1EndDrop Table#tmp_lock_whoreturn 0End

The deadlock process ID can be detected by the above and the kill deadlock process ID can be executed (kill 63)

Database query super Slow, database deadlock view and resolution