THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Using the Processes query outside of Activity Monitor

After Adam and Linchi posted their queries for analyzing performance, I thought I'd show you one I run. I'm sure you run it, too. It's the Processes query from the Activity Monitor. Usually I run it from Activity Monitor, but sometimes the Activity Monitor UI just doesn't do what I want. I ran SQL Profiler to find out exactly what is executed behind the scenes. Executing the Processes query in a query window is great for when you need to persist the results. 



/* Processes */


   [Session ID]    = s.session_id,

   [User Process]  = CONVERT(CHAR(1), s.is_user_process),

   [Login]         = s.login_name,  

   [Database]      = ISNULL(db_name(r.database_id), N''),

   [Task State]    = ISNULL(t.task_state, N''),

   [Command]       = ISNULL(r.command, N''),

   [Application]   = ISNULL(s.program_name, N''),

   [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),

   [Wait Type]     = ISNULL(w.wait_type, N''),

   [Wait Resource] = ISNULL(w.resource_description, N''),

   [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),

   [Head Blocker]  =


            -- session has an active request, is blocked, but is blocking others

            WHEN r2.session_id IS NOT NULL AND r.blocking_session_id = 0 THEN '1'

            -- session is idle but has an open tran and is blocking others

            WHEN r.session_id IS NULL THEN '1'

            ELSE ''


   [Total CPU (ms)] = s.cpu_time,

   [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024,

   [Memory Use (KB)]  = s.memory_usage * 8192 / 1024,

   [Open Transactions] = ISNULL(r.open_transaction_count,0),

   [Login Time]    = s.login_time,

   [Last Request Start Time] = s.last_request_start_time,

   [Host Name]     = ISNULL(s.host_name, N''),

   [Net Address]   = ISNULL(c.client_net_address, N''),

   [Execution Context ID] = ISNULL(t.exec_context_id, 0),

   [Request ID] = ISNULL(r.request_id, 0),

   [Workload Group] = N''

FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)

LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)

LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)



    -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as

    -- waiting for several different threads.  This will cause that thread to show up in multiple rows

    -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread,

    -- and use it as representative of the other wait relationships this thread is involved in.

    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num

    FROM sys.dm_os_waiting_tasks

) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1

LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id)

ORDER BY s.session_id;


Published Monday, August 24, 2009 11:10 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



SQLuser said:

Thanks for the great article!

March 26, 2010 2:51 PM

Vinnie said:

Great script.  Thanks for sharing!!!

August 20, 2010 9:41 AM

User said:

Thanks a Lot. How did you manage to get that?

October 13, 2010 1:10 PM

John Paul Cook said:

I used the SQL Server Profiler to capture what SSMS was executing.

October 13, 2010 7:07 PM

Skyline69 said:

Great, this is easier for me to use than Activity monitor as I can capture the information and use it to track problems.

October 21, 2010 11:23 AM

Preet_S said:


To also see what sql is actually executing :

Add the following just before the ORDER BY clause ;

OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as st

WHERE s.session_Id > 50              -- Ignore system spids.

AND s.session_Id NOT IN (@@SPID)     -- do not include the query we are running

In the Select list add :

   [Query] =SUBSTRING(st.text, (r.statement_start_offset/2)+1,

       ((CASE r.statement_end_offset

         WHEN -1 THEN DATALENGTH(st.text)

        ELSE r.statement_end_offset

        END - r.statement_start_offset)/2) + 1) AS statement_text

Not sure where Activity Monitor sources its "Last T-SQL command batch" when you right-click a SPID and choose "Details". Anybody know ?

February 16, 2012 7:45 AM

Wlad I.Abraham said:

Nice job, John!

My contribution for SQ 2005: add for Preet_S's query some code :

[Last Command Batch] = (select text from sys.dm_exec_sql_text(c.most_recent_sql_handle)) - instead of his nullable [Query].

[Database] also is NULL, use : [DB] = ISNULL(DB_NAME(sp.dbid), N'') and use : LEFT OUTER JOIN sys.sysprocesses sp ON (s.session_id = sp.spid) - just for right view. SQL 2008 also workable.

Good luck, boys!

Wlad, MCITP, Russia.

April 25, 2012 7:56 AM

Harsh said:

Thanks you for this.. It really helped..

November 8, 2012 5:56 AM

anuj said:

Great script

October 1, 2013 2:58 AM

David Kelly said:

For all of my DB's bar 1 in Activity Monitor it shows under the application column Application, Server Name, User, IP etc. (all use ODBC connections from proprietary applications). Where does it get this information from? i.e. Where should I be looking to make sure that all SQL connections show this info

October 1, 2013 4:03 PM

Kevin said:

This query does not return the same results as activity monitor.  Sorry, but it is just not the same and therefore not so useful.

October 22, 2013 9:48 AM

Kiem.Nguyen said:

It's great, thanks for sharing

January 20, 2015 12:09 AM

Adam said:

Also, if you want to see a lock's details. Here's the SQL (also obtained using the profiler). You'll need to replace "<PROCESS ID>" with the actual process ID:

create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512))

insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(<PROCESS ID>)')

select [Event Info] from #tmpDBCCinputbuffer

August 3, 2016 5:21 AM

Amir Navabi said:

tank you so much for this script.

February 13, 2017 3:36 AM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement