.NET Musings

Quite often in development, either .Net or SQL Server, you want to watch what is running on your server as part of your debugging process.  Or, as a DBA, you need to watch what is running for DBA type duties.  My good friend an coworker Chris Barth gave me a script to use for just this task.

  SELECT 'Process' AS Process
  , SP.spid, SP.kpid, SP.blocked AS blockedby, SP.cmd, SP.open_tran
  , SP.status, RTRIM(SP.waittype) AS waittype
  , RTRIM(SP.lastwaittype) AS lastwaittype
  , RTRIM(SP.waittime) AS waittime
  , RTRIM(SP.waitresource) AS waitresource, SP.ecid
  , 'Session' AS Session
  , ES.cpu_time, ES.memory_usage, ES.reads, ES.writes, ES.logical_reads
  , ES.last_request_start_time, ES.last_request_end_time
  , 'Request' AS Request
  , ER.reads AS reads_Request, ER.writes AS writes_Request
  , ER.logical_reads AS logical_reads_Request, ER.text_size
  , ES.last_request_start_time, ES.last_request_end_time
  , 'Connection' AS Connection
  , EC.last_read, EC.last_write, SP.stmt_start, SP.stmt_end
  , SP.loginame, SP.program_name
  , TL.LockCnt
  , CASE WHEN SP.stmt_start > 0 AND (SP.stmt_end-SP.stmt_start) > 0
  THEN SUBSTRING(ST.text,(SP.stmt_start+2)/2,(SP.stmt_end-SP.stmt_start)/2)
  ELSE ST.text
  END AS SQL_Piece
  , ST.text AS SQL_Text
  FROM sys.sysprocesses SP WITH (nolock)
  INNER JOIN sys.dm_exec_connections EC WITH (nolock) 
     ON EC.session_id = SP.spid
  CROSS APPLY sys.dm_exec_sql_text(EC.most_recent_sql_handle) AS ST
  INNER JOIN sys.dm_exec_sessions ES WITH (nolock) 
     ON ES.session_id = SP.spid
  LEFT JOIN sys.dm_exec_requests ER WITH (nolock) 
     ON ER.session_id = SP.spid
  LEFT JOIN ( SELECT request_session_id AS spid
  , resource_associated_entity_id
  , resource_database_id,COUNT(1) AS LockCnt 
  FROM sys.dm_tran_locks WITH (nolock) 
  where request_session_id = 56
  GROUP BY request_session_id,resource_associated_entity_id
  , resource_database_id ) TL ON TL.spid = SP.spid
  WHERE 
  ( SP.cmd NOT IN( 'AWAITING COMMAND', 'LAZY WRITER', 'CHECKPOINT SLEEP' )
  OR blocked <> 0 )
  ORDER BY SP.spid, SP.ecid;
  

The script returns a LOT of information, which most of the time you won't need. But it is all there for those times when you need it. I converted this script to a stored procedure in the master database so it is accessible from all of my databases.

Note, I updated this to remove the line numbers - good suggestion from a reader!

Happy coding!