Foreword

This is a guide to explain in more detail than BOL what each column does and how to use it. I am sure that these will have been documented in various places but I wrote this to be a good place to start and for beginners to understand a little more about what is available.

While I was writing this there was some discussion about whether people should still be using sysprocesses. I think that while it is important that we learn and use the new views which do give us more detail than we have ever had - when initially troubleshooting an issue sysprocesses is a one stop shop for an all over view of SQL, no matter what version you have - even dare I say it for Sybase instances.

What is a sysprocess?

When you create a connection to Sql Server, internally it creates a number of structures to help manage and run your queries. There are two which essentially make up a sysprocess, the PSS (Process Storage Structure) and the Execution Context. These two structures while not documented are alluded to in various knowledge base articles. The PSS holds information about who you are, where you come from and what it is you are doing (your login details, network address, last command run, query and query position etc). The execution context (EC) is what is passed to the CPU queue to describe the work, when your process is sleeping or running, it is the EC that is in that mode.

What is sysprocesses?

All of the PSS objects are stored in an array and are constantly updated as any changes happen, it isn't a normal table that exists in your mdf file, but a way to view the array of PSS structures - if you have a look at the view you will see:

CREATE VIEW sys.sysprocesses AS SELECT * FROM OpenRowSet(TABLE SYSPROCESSES)

If you open the master.mdf file with a hex editor you would not be able to find the data.

Lets go in and take a look at each column in turn and describe what it is and when it is useful:

SPID

The spid is the identifier of your process (PSS), you use it to find out who is blocking who and to tie other views and profiler together. The first 50 are dedicated to sql and user processes start at 51.

KPID

The KPID is useful in that it helps us tie up what has been passed to the operating system to run commands and is actually working. Although the SPID is constant throughout the life of the connection a KPID is allocated to each task that needs to be carried out.

The KPID maps back to an actual windows thread and so it is possible using performance monitor to get actual physical statistics about a task instead of the purely logical statistics which SQL shows through the CPU column.

The KPID is the actual o/s thread id and you can use the "Thread" performance counter using "ID Thread" and "% Processor Time" to match the thread to the actual cpu stats.

Here is a script which takes a snapshot using the sys.dm_os_threads dmv and then 2 seconds later compares it to the latest figures to give you a snapshot of which SPID / task is using the physical CPU.

DECLARE @oldStats TABLE( os_thread_id int, kernel_time bigint, usermode_time bigint)

INSERT INTO @oldStats
Select os_thread_id, kernel_time, usermode_time from sys.dm_os_threads WHERE os_thread_id IN (SELECT KPID FROM sys.sysprocesses WHERE kpid <> 0)

WAITFOR DELAY '0:0:2'

SELECT sp.SPID, sp.CPU AS LocgicalCPU, (new.kernel_time + new.usermode_time) - (old.kernel_time + old.usermode_time) AS PhysicalTime FROM sys.sysprocesses SP
LEFT OUTER JOIN @oldStats old ON SP.kpid = old.os_thread_id
LEFT OUTER JOIN sys.dm_os_threads new ON sp.kpid = new.os_thread_id
ORDER BY (new.kernel_time + new.usermode_time) - (old.kernel_time + old.usermode_time) DESC, SPID

If you change the delay you may find that the KPID is no longer active for the second pass of the sys.dm_os_threads so you will see null's.

BLOCKED

This shows you what if anything is blocking a SPID.

It will either show itself which means that it has issued a read / write (or other request) and is waiting for the response. This shows that although the spid is not technically blocked it can't process anything as it is waiting for an i/o (page latch) request to complete. If the WAITTIME column is low then this isn't anything to worry about. If it is high then it indicates a possible i/o issue.

If it shows a separate SPID then it is being blocked by that so you can have a look and see what the sql is on each process and identify any long term locking issues.

There are three other values for BLOCKED (copied directly from BOL):

-2 = The blocking resource is owned by an orphaned distributed transaction.

-3 = The blocking resource is owned by a deferred recovery transaction.

-4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions.

SPID's with a negative BLOCKED cannot be killed using the SPID, instead in the case of the DTC transactions you need to kill the DTC unit of work which you get from the MSDTC management console. See (http://www.eraofdata.com/blog/2008/12/orphaned-msdtc-transactions-2-spids/) for an excellent set of instructions on this.

When using the column to find blocked processes it is important to include all the possible values i.e.

SELECT * FROM sys.sysprocesses WHERE blocked <> 0

WAITTYPE

This shows what if anything the process is waiting on, it is a decimal(2) column and holds the internal wait type, if it is 0x0 then the process is not waiting on anything. It is used in conjunction with LASTWAITTYPE so I will cover this along with the LASTWAITTYPE.

WAITTIME

This is the time it has been waiting (or 0 if it is not waiting) - the time is in milliseconds.

LASTWAITTYPE

This shows the last type of wait in a readable form (as opposed to WAITTYPE which shows the internal identifier). This column can sometimes be misleading because it doesn't just show the current wait type but it also shows the last thing, so if WAITTYPE is 0x0 then this column is no longer valid.

Going through the wait types, what they mean and how to troubleshoot issues using them could easily take up an article and has already been done many times before. I would strongly recommend using this KB article and understanding what they different types mean (ideally before you are struggling with a real life issue!)

http://support.microsoft.com/kb/822101

WAITRESOURCE

This shows us specifically what the process is waiting for, it differs between WAITTYPES but is particularly useful when it shows a page id as it will show us exactly what index or table the process is currently waiting on. Using this with the currently executing sql you can work out where you are in your batch.

The page id is in the format "DatabaseID:FileId:PageNumber" i.e. "1:1:20" will show you the twentieth page in the first file of the master database. To display the contents of the page, use the DBCC command "PAGE(1,1,20,3)" (the fourth parameter is the output type 3 = verbose) - you will need to enable the trace flag 3604 to show the data "DBCC TRACEON(3604)".

If you do this for the WAITRESOURCE you will see in the page header details "Metadata: ObjectId = " if you get the value it will show the object id which you can use sys.sysobjects to display and the "Metadata: IndexId =" to get the index id which you can get from sys.sysindexes using a combination of the object id and index id. If necessary you are also given the partition details if the table is partitioned.

DBID

This shows which database the spid is currently connecting to - to display the name either use sys.sysdatabases or SELECT DB_NAME(dbid).

UID

If the connection is made with a SQL login then the uid will show the user id of the authenticated user. You can convert the id to a specific login using sys.sysusers - but you will need to be connected to the database shown in DBID otherwise you will get a different list as it is per database and not per server.

CPU, PHYSICAL_IO, MEMUSAGE

I am covering these under one section as they show the performance statistics for each spid - they are an easy way to see how busy a particular SPID is. There is only one thing you should be aware of when using connection pooling as although the values are reset when a connection is reused, sysprocesses leaves the old value until the new connection carries out some work.

LOGIN_TIME

This shows the login time or the time the pooled connection was reset

LAST_BATCH

The time the last batch was started by the SPID.

ECID

This shows the sub task id of the process, when SQL uses parallelism each separate task of the process is given a separate ECID (Execution Context ID).

OPEN_TRAN

This shows how many open transactions a process has. It is very useful for tracking down sql that is missing commit / rollback tran statements.

The value returned is the same as the @@TRANCOUNT variable that can be used in a batch.

STATUS

Status is fairly straight forward, the only difficulty is that books online does not document all of the possible values and I wouldn't be surprised if there are more that are only seen very occaisionly. The documented values in the current version of BOL are (My comments are in italics)

dormant = SQL Server is resetting the session. This can happen with connection pooling.

running = The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS).

background = The session is running a background task, such as deadlock detection.

rollback = The session has a transaction rollback in process.

pending = The session is waiting for a worker thread to become available.

runnable = The task in the session is in the runnable queue of a scheduler while waiting to get a time quantum.

spinloop = The task in the session is waiting for a spinlock to become free.

suspended = The session is waiting for an event, such as I/O, to complete.

Other possible values:

sleeping = SPID is not currently executing. This shows the spid has completed all of the tasks and is waiting for input.

Defwakeup = This means the SPID has been set to sleep until a resource is available - check the WAITRESOURCE column to see what it is waiting on.

SID

If you connect using windows security (trusted connection) then this will show the sid of the user.

HOSTNAME

This shows the computer name that the connection started from. This can be set by the client so can be misleading.

PROGRAM_NAME

Shows the application that created the connection, again this can be set by the client so can be misleading.

HOSTPROCESS

This shows the pid of the process that created the connection.

CMD

This shows the command that is being run. As well as showing obvious commands like SELECT, UPDATE or INSERT it shows things for background tasks like LOG WRITER, CHECKPOINT and LAZY WRITER. The list isn't documented but the values are fairly self explanatory.

NT_DOMAIN

This is the domain the windows login is connected from (either domain, workgroup or computername). This is not shown for sql logins.

NT_USERNAME

The use name of the windows account, again this is not shown for sql logins i.e. It is only for trusted connections.

NET_ADDRESS

This shoes the mac address of the network card that made the connection. Even if you connect locally using LPC you still get a mac address.

NET_LIBRARY

This shows the protocol that was used LPC, TCP/IP etc.

LOGINAME

The "Logi Name" as I like to call it shows which user is connected, either the full domain name or the sql account name.

CONTEXT_INFO

This is a user settable column you can store binary data by using "SET CONTEXT_INFO" - to see this in action go http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/2765/.

SQL_HANDLE

Before the wonderful sys.dm_exec_query_stats and sys.dm_exec_sql_text made it easy to get to the executing sql, we had to use the sql_handle column and pass it into "fn_get_sql" for example:

SELECT (SELECT text FROM fn_get_sql(sql_handle)) FROM sys.sysprocesses

STMT_START

If the batch that is being executed is split into statements then this will point to the fiirst character of the current statement. Because the batch is stored in unicode you need to divide this number by 2 (as there are 2 bytes in a unicode string but the fn_get_sql returns an ansi string - the actual type is TEXT).

STMT_END

This points to the end of the statement in the batch, again this is in unicode. -1 indicates that the statement runs to the end of the batch and 0 indicates the batch isn't running so it isn't at a specific point.

This example code shows why I described the sys.dm_exec_sql_text view as wonderful:

SELECT SPID, KPID, SUBSTRING((SELECT TEXT FROM fn_get_sql(sql_handle)), stmt_start/2,
CASE stmt_end
WHEN -1 THEN LEN(CONVERT(VARCHAR(8000), (SELECT TEXT FROM fn_get_sql(sql_handle)))) - (stmt_end/2)
WHEN 0 THEN LEN(CONVERT(VARCHAR(8000), (SELECT TEXT FROM fn_get_sql(sql_handle))))
ELSE stmt_end /2
END

) FROM sys.sysprocesses WHERE SPID = @@SPID

REQUEST_ID

This is used for MARS connections, each SPID has a number of request_id's. You can retrieve the value in a similar way to using @@SPID by using CURRENT_REQUEST_ID().

Conclusion

Sysprocesses is like task manager when you need to get an overall view of what is going on then it is a good (the best?) place to start. It has a number of different columns used in different situations but it can be a bit daunting so it is best to understand what is available and what it means. As a DBA I use this as well as most administrators on a fairly regular basis. If you have used sysprocesses to track down an issue and it isn't covered here then I would encourage you to add a comment to this article.