SQL Server Routine Maintenance-queries the currently executing statements, deadlocks, blockages

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