How to Get SQL Server Activity Monitor’s Output Using T-SQL

activity-monitorsql server

Is there any script to get the output just like Activity Monitor?

Or
Which DMV's can I use to get the output as Activity Monitor displays on the screen?

It should be able to display the stored procedure name as well as the statement from the stored procedure which is currently running and the bloking related info as well.

Best Answer

To get the exact output as Activity Monitor:

I have modified the given script as follows. You can play the activity monitor on one side and this script in another window and verify the output.

You can also view the currently running expensive queries by using this script and for that just need to do ORDER BY [Total CPU (ms)] desc.

This script will display the following things:

[Session ID]
[User Process]
[Login]
[Blocked By]
[Head Blocker]
[DatabaseName]
[Task State]
[Command]
[statement_text] --It will display the statement which is being executed presently and it can be from the SP or the normal T-sql .
[command_text] ----- It will display the Stored Procedure's Name.
[Total CPU (ms)]
'Elapsed Time (in Sec)'
[Wait Time (ms)]
[Wait Type]
[Wait Resource]
[Memory Use (KB)]
[Host Name]
[Net Address]
[Workload Group]
[Application]

You can also add or remove the columns whichever you need .

I have commented out some columns in the query, like: --[Open Transactions Count] = ISNULL(r.open_transaction_count,0), --[Login Time] = s.login_time, --[Last Request Start Time] = s.last_request_start_time, So if you want can also add or remove the columns as per your requirement and you can also filter out the data DatabaseName wise. I hope this script will help many of us.

    /* ACTIVITY MONITOR'S OUTPUT along with statement_text and command_text */ /* Processes */ 
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
        -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
        WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
        -- session is either not blocking someone, or is blocking someone but is blocked by another party
        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, 
 --[Open Transactions Count] = 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'',
                     [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
  ( -- 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.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 -- Ignore system spids.

ORDER BY s.session_id --,[Total CPU (ms)] desc ;

Related Solutions

SQL Server – Get Individual Rowcounts Like SSMS

The SqlCommand.StatementCompleted event will fire after each statement in a batch, and one of the properties of the event (well, pretty much the only property) is the number of rows affected by the statement that fired the event.

Some notes:

A requirement of getting this info is that you did not specify SET NOCOUNT ON;, or conversely, you did specify SET NOCOUNT OFF;.
All events fire at the completion of each Execute___(), not during the execution.
The StatementCompletedEventArgs.RecordCount includes row counts from SELECT statements, whereas the SqlDataReader.RecordsAffected property only reports row counts from DML statements (INSERT, UPDATE, DELETE, etc).
The StatementCompleted event does not include the individual SQL statement from the batch that fired the event. However, the event handler is sent the sender as an input parameter and this is the SqlCommand of the query batch, and you can see that batch by casting sender to SqlCommand and then looking at the CommandText property (this is shown in the example below).

The documentation is very sparse on this so I have worked up an example that shows this event firing for both ExecuteNonQuery and ExecuteScalar, as well as for both ad hoc queries and stored procedures (i.e. SqlCommand.CommandType of Text vs StoredProcedure):

using System;
using System.Data;
using System.Data.SqlClient;

namespace StatementCompletedFiring
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection _Connection =
                          new SqlConnection("Integrated Security = True;"))
            {
                using (SqlCommand _Command = new SqlCommand(@"
SET NOCOUNT OFF; --  ensures that the 'StatementCompleted' event fires

EXEC('
CREATE PROCEDURE #TestProc
AS
SELECT * FROM sys.objects;

SELECT * FROM sys.tables;
');

SELECT * FROM sys.objects;
", _Connection))
                {

                    _Command.StatementCompleted += _Command_StatementCompleted;

                    try
                    {
                        _Connection.Open();

                        _Command.ExecuteNonQuery();

                        _Command.CommandText = @"
SELECT 123 AS [Bob];

WAITFOR DELAY '00:00:05.000'; --5 second pause to shows when the events fire

SELECT 2 AS [Sally]
UNION ALL
SELECT 5;
";
                        Console.WriteLine("\n\t");
                        Console.WriteLine(_Command.ExecuteScalar().ToString());
                        Console.WriteLine("\n");


                        _Command.CommandType = CommandType.StoredProcedure;
                        _Command.CommandText = "#TestProc";
                        _Command.ExecuteNonQuery();
                    }
                    catch (Exception _Exception)
                    {
                        throw new Exception(_Exception.Message);
                    }
                }
            }
        }

        static void _Command_StatementCompleted(object sender,
                                                StatementCompletedEventArgs e)
        {
            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("\nQuery Batch: ");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(((SqlCommand)sender).CommandText);

            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("Row(s) affected: ");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(e.RecordCount.ToString() + "\n");

            Console.ResetColor();
        }
    }
}

OUTPUT:

Query Batch:
SET NOCOUNT OFF; -- ensures that the 'StatementCompleted' event fires

EXEC(' CREATE PROCEDURE #TestProc AS SELECT * FROM sys.objects;

SELECT * FROM sys.tables; ');

SELECT * FROM sys.objects;

Row(s) affected: 453

Query Batch:
SELECT 123 AS [Bob];

WAITFOR DELAY '00:00:05.000'; --5 second pause

SELECT 2 AS [Sally] UNION ALL SELECT 5;

Row(s) affected: 1

Query Batch:
SELECT 123 AS [Bob];

WAITFOR DELAY '00:00:05.000'; --5 second pause

SELECT 2 AS [Sally] UNION ALL SELECT 5;

Row(s) affected: 2

123

Query Batch: #TestProc
Row(s) affected: 453

Query Batch: #TestProc
Row(s) affected: 17

Sql-server – Grouping by more than 1 column using Partion By or any other method – Sql Server

Just create 2 separate row numbers for change of Type and SubType. I used Id field since it looks like you don't want it to be sorted by Name:

Select
  case row_number() over (partition by T.Id order by T.Id, S.Id, P.Id)
    when 1 then T.Name else '' end as Type,
  case row_number() over (partition by T.Id, S.Id order by T.Id, S.Id, P.Id)
    when 1 then S.Name else '' end as SubType,
  P.Name
FROM 
  Product P
  INNER JOIN Type T ON T.Id = P.TypeId
  INNER JOIN SubType S ON S.Id = P.SubTypeId
 order by T.Id, S.Id, P.Id

Example in SQL Fiddle

Best Answer

Related Solutions

SQL Server – Get Individual Rowcounts Like SSMS

Sql-server – Grouping by more than 1 column using Partion By or any other method – Sql Server

Related Question