THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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. 

/* ACTIVITY MONITOR */

 

/* Processes */

SELECT

   [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]  =

        CASE

            -- 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 ''

        END,

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

LEFT OUTER JOIN

(

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

Comments

 

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:

Useful.

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
 

Where Is The Job Activity Monitor In Sql | New Job Today said:

November 18, 2014 3:30 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement