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)…


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

try this instead

SELECT
S.login_name
, 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.

Advertisements

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

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s