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

sangam ravisekhar 21 Reputation points
2022-04-20T12:38:20.67+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bert Zhou-msft 3,421 Reputation points
    2022-04-21T01:54:05.743+00:00

    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.

    2 people found this answer helpful.
    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,101 Reputation points
    2022-04-20T13:19:37.863+00:00

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

    1 person found this answer helpful.

  2. Erland Sommarskog 106K Reputation points MVP
    2022-04-20T22:03:28.823+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  3. Erland Sommarskog 106K Reputation points MVP
    2022-04-21T21:22:21.9+00:00

    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.

    0 comments No comments

  4. sangam ravisekhar 21 Reputation points
    2022-04-22T04:47:24.427+00:00

    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.