Tag Archives: sys.sysprocesses

SQL Server – Looking at Currently Running Processes and Queries

SQL Server has a number of ways to see what is happening on the server. If you just need a quick look you have a few options.

Activity Monitor

The Activity Monitor is a great way to get a quick overview. It has an Overview (graphs), Current processes, Resource Waits grouped by type, Data File I/O and Recent Expensive Queries. This is a good place to start if you are new to SQL Server.
Activity Monitor

Activity Monitor Processes

System Stored Procedures

Before the Activity Monitor was available there were a couple of system stored procedures that would give you the highlights – really just enough information to figure out where you needed to start digging for more information.

EXEC sp_who

sp_who
And the updated version:

EXEC sp_who2

sp_who2

These are both good at giving some basic information – you get the SPID, status, login name, if the command is being blocked and what SPID is blocking, the database and the command type. You can see that sp_who2 gives a little more information CPU time, disk I/O, last batch date and program name.

If you want to see the actual SQL command that is running you can use dbcc inputbuffer. The SPID for the process you are trying to view is the parameter – 85 in the example below. You can see my test query was running in another window.

dbcc inputbuffer(85)
EventType      Parameters EventInfo
-------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Language Event 0          
DECLARE @Count int = 0

WHILE @Count < 100
BEGIN
	SELECT COUNT(*)
	FROM sys.all_objects

	WAITFOR DELAY '00:00:05'
	SELECT @Count += 1
END

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

System Views

There are some system management views and system compatibility views that give even more detailed information. Sys.sysprocesses returns all the same rows that sp_who2 returns and gives additional information.

SELECT *
FROM sys.sysprocesses

sys.sysprocesses

There is a lot of information available in that view. Usually you don’t want everything in there and there are a few additional items that would be useful, so I add in sys.dm_exec_requests. The following query shows everything but I am adding it to get cpu_time, total_elapsed_time, reads, writes, logical_reads, sql_handle and the plan_handle.

SELECT *
FROM sys.sysprocesses SP
    LEFT JOIN sys.dm_exec_requests ER
        ON SP.spid = ER.session_id

The reason the sql_handle and the plan_handle are important is to get detailed information about the query and the query plan.

SELECT
    spid
    ,DB_NAME(SP.dbid)    DBName
    ,nt_username    NTUser
    ,sp.Loginame    LoginName
    ,Hostname
    ,nt_domain
    ,ER.status        QueryStatus
    ,ER.command        QueryType
    ,ER.blocking_session_id    BlockingSessionID
    ,program_name        ProgramName
    ,start_time
    ,SP.cpu                CPUTime
    ,SP.physical_io        DiskIO
    ,SP.memusage            MemoryUsage
    ,lastwaittype
    ,SUBSTRING(ST.text, (ER.statement_start_offset/2)+1, 
        ((CASE ER.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE ER.statement_end_offset
        END - ER.statement_start_offset)/2) + 1)    RunningQuery
    ,ST.text        ParentQuery
    ,CONVERT(xml,(SELECT 1 AS Tag,NULL AS Parent, text [CurrentProcesses!1!Query!cdata] FROM sys.dm_exec_sql_text(ER.sql_handle)  FOR XML EXPLICIT))    ParentQueryXML
    ,QP.query_plan
FROM sys.sysprocesses SP WITH(NOLOCK)
    LEFT JOIN sys.dm_exec_requests ER WITH(NOLOCK)
        ON ER.session_id = sp.spid
    OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle)as ST
    OUTER APPLY sys.dm_exec_query_plan(ER.plan_handle) AS QP
WHERE spid <> @@SPID --Exclude the session this query is running in.
ORDER BY spid

sys.sysprocesses with additions

This version has a few things in it that makes troubleshooting easier. The text of the query is added, the query plan is there (if available) and the query text is available as XML in case it is large.

Resources

This script can also be found on our script page or downloaded below. It also excludes all system processes.

PROCESSES.Current.Runnable.Statements
I-Tech SQL Server Scripts