Query the statement that is currently executing:
1 SELECT 2Der.[session_id], Der.[blocking_session_id], 3 Sp.lastwaittype,sp.hostname,sp.program_name,sp.loginame,4Der.[start_time] as 'Start Time', 5Der.[Status] as 'Status', 6Dest.[text] as 'SQL statements', 7 db_name(Der.[database_id]) as 'Database name', 8Der.[Wait_type] as 'wait for resource type', 9Der.[Wait_time] as 'Wait Time', TenDer.[Wait_resource] as 'Resources to wait', OneDer.[logical_reads] as 'number of logical reads' A fromSys.[dm_exec_requests] asder - INNER JOINmaster.dbo.sysprocesses asSp onder.session_id=Sp.spid - CrossAPPLY sys.[Dm_exec_sql_text](Der.[sql_handle]) asdest the --WHERE [session_id]>50 and session_id<>@ @SPID - ORDER byDer.[session_id] - GO
View Code
Whether or not clogging:
1 SELECTSpid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran2, Status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text 3 fromMaster.dbo.sysprocesses P CrossAPPLY sys.dm_exec_sql_text (p.sql_handle) s4 WHEREBlocked> 0 ORspidinch(SELECTBlocked frommaster.dbo.sysprocessesWHEREBlocked> 0) 5 Go
View Code
Whether there are uncommitted things:
1 Select Spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran 2 , Status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text 3 from cross apply Sys.dm_exec_sql_text (p.sql_handle) s 4 where<>0 5Go
View Code
Deadlock:
1 --drop table #deadlock2 3 CREATE TABLE#deadlock (logdateDATETIME, ProcessInfoVARCHAR( -),Text VARCHAR( -)) 4 Go 5 INSERT into#deadlockEXECXp_readerrorlog0,1,'Deadlock victim',NULL,'2016-07-20 14:00:00','2016-07-30','DESC' 6 Go
View Code
Dead Lock plus Nolock optimization
SQL Server Routine Maintenance-queries the currently executing statements, deadlocks, blockages