How to get the output of SQL Server Activity Monitor using SQL script?

Question

Hi,

How to get the data present in the "Activity Monitor" like the data shown in the panel's Recent Expensive Queries or Active Expensive Queries? Like some SQL script that returns the same output.

Answer 1

Hi，@sangam ravisekhar

Welcome to Microsoft T-SQL Q&A Forum!

I don't know if you know sp_WhoIsActive, as a powerful software is widely used in the industry to sort queries from longest running time to shortest.
In addition to showing system process information, it will also show you who is blocking whom, which machine the query is coming from, and more. You can click this link to get it. Here is a solution, maybe you can try it.

SELECT [Session ID] = s.session_id,   
       [User Process] = CONVERT(CHAR(1), s.is_user_process),   
       [Login] = s.login_name,   
       [Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),   
         [Head Blocker]  =  
    CASE  
        WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'  
        ELSE ''  
    END,  
                        [DatabaseName] = ISNULL(db_name(r.database_id), N''),   
                        [Task State] = ISNULL(t.task_state, N''),   
                        [Command] = ISNULL(r.command, N''),   
                        [statement_text] = 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), ----It will display the statement which is being executed presently.  
  
 [command_text] =Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), ''), -- It will display the Stored Procedure's Name.  
  
 [Total CPU (ms)] = r.cpu_time,  
 r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)',  
                                 [Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),  
                                 [Wait Type] = ISNULL(w.wait_type, N''),  
                                 [Wait Resource] = ISNULL(w.resource_description, N''),  
                                 [Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,  
                                 [Memory Use (KB)] = s.memory_usage * 8192 / 1024,   
  
 [Host Name] = ISNULL(s.host_name, N''),  
 [Net Address] = ISNULL(c.client_net_address, N''),   
 [Workload Group] = N'',  
                     [Application] = ISNULL(s.program_name, 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  
  (  
 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.session_id = w.session_id)  
AND w.row_num = 1   
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id) OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st   
  
WHERE s.session_Id > 50   
  
ORDER BY s.session_id   

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

SQL Server Activity Monitor is a GUI interface of external application (built-in the SSMS application). You can get a screenshot of the GUI using different development languages, but this is not related to SQL Server and I assume that this not what you want.

What you probably need is NOT to get the SQL Server Activity Monitor, but to get the information which SQL Server Activity Monitor presents.

As I said, SQL Server Activity Monitor is simply an application and it get the data for the GUI interface using simple queries like any other external application. It gets from the server information about running Processes, Resource Waits, Data File I/O, Recent Expensive Queries, Active Expensive Queries and so on.

Do you need specific data or we can just go over some data you can pull from the server and the relevant queries

list of tables, dmv and other object which you can query in order to get the information for the monitoring

sys.dm_exec_connections : Established connection
sys.dm_exec_sessions : Authenticated sessions
sys.dm_exec_requests : Current requests
sys.dm_exec_cached_plans : Cached execution plans
sys.dm_exec_query_plan : Show plan for a given cached plan_handle
sys.dm_exec_query_stats : Query performance stats
sys.dm_exec_sql_text : SQL text given a sql_handle
sys.dm_db_index_physical_stats : Index size and fragmentation
sys.dm_db_index_usage_stats : index usage via the query optimizer
sys.dm_db_missing_index_details : Discover missing indexes
sys.dm_os_performance_counters : List of all SQL Server performance counters and values
sys.dm_os_schedulers : Detect CPU pressure
sys.dm_os_waiting_tasks : Tasks waiting on resources
sys.dm_os_wait_stats : All waits types and stats
sys.dm_io_virtual_file_stats : I/O stats for data and log files
sys.dm_io_pending_io_requests : Pending I/O requests
sys.dm_clr_loaded_assemblies : Loaded assemblies
sys.dm_clr_tasks : CLR related tasks
sp_who : current users, sessions, and processes

Using the objects above, you can get all the information which is displayed in the Activity Monitor (you might want to JOIN between a few of these to get the result in one set).

If you need specific information then please inform us and we can build exact query which fit your specific need :-)

Answer 3

One way to do this is to use my beta_lockinfo. It's not an exact clone of Activity Monitor, but it gives you the same information - and more, I think.

Answer 4

Thanks for sharing the table details. I need exactly the below information in the same way it is being shown. I tried but have not succeeded. I need two queries, one for Active expensive & another one for Recent queries.

I don't really see why you would want the exact same queries. You may get just as good or better information my beta_lockinfo or Adam Machanic's sp_whoIsActive as Bert suggested.

But if you want to know what SSMS, you can use Profiler to spy on SSMS and captures the queries this way. Do this on an idle server, and not in production, since running in Profiler casually in production can cause severe problems.

Answer 5

All your suggestions are good. Present I am using the query given by Bert. My requirement is very specific. If I can get the same as that is wonderful.