Do not use sysprocesses

If you are regularly using a check on the sysprocesses table to see what is slowing down your sql server, looking for that usual culprit in middle management who absolutely must have access to the database despite the fact that he/she has no idea about what they are doing to the performance, then heres a sql 2005+ alternative to using sysprocesses.

The dynamic management views offer access to much more information that the catalog views used to uery the server compatible with the old methods. The following script does not offer much more but it can be imporved and expanded upon to provide more in if you need it. Just check out the three views referenced in the query. I have purposefully kept this simple to keep it similar to sysprocesses, as a building block for development.

You probably were doing something like this in 2000 (perhaps with a few different columns or just an asterisk)…

, blocked
, hostname
, program_name
, hostprocess
, cmd
, status
, open_tran
, dbid
, cpu
, physical_io
, memusage
, login_time
, last_batch
FROM sys.sysprocesses

try this instead

, S.login_time
, BS.login_name
, BS.login_time
, R.session_id
, R.start_time
, R.command
, R.status
, R.cpu_time
, R.reads
, R.writes
, R.logical_reads
, R.blocking_session_id
, R.database_id
, DB_NAME(R.database_id) as database_name
, C.connect_time
, C.net_transport
, C.protocol_type
, C.client_net_address
FROM sys.dm_exec_requests R
INNER JOIN sys.dm_exec_sessions S on R.session_id = S.session_id
INNER JOIN sys.dm_exec_connections C on R.connection_id = C.connection_id
LEFT JOIN sys.dm_exec_sessions BS on R.blocking_session_id = BS.session_id

Note that in this statement I have used an inner join to the connection view so that you only see sessions with connections. This filters out the internal sql operations.


About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in SQL Stuff and tagged , , , . Bookmark the permalink.

3 Responses to Do not use sysprocesses

  1. Thiago says:

    That’s a great insight!

  2. Pingback: SQL Server – Table Lock – Identificando processo responsável | Thiago Timm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s