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