What's running on my SQL Server?

.NET Musings

Wandering thoughts of a developer, architect, speaker, and trainer


What's running on my SQL Server?

Technorati Tags:

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
  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!

Comments (2) -

Philip Japikse, MCSD, MCDBA, CSM

Removed the line numbers.  Good point!

This is very good stuff.  Please next time do not include the line numbers.  It makes it more difficult to copy.

Comments are closed
Managed Windows Shared Hosting by OrcsWeb