Saltar al contenido

¿Cómo obtener la salida del Monitor de actividad de SQL Server usando T-SQL?

Investigamos en diferentes espacios y de esta forma traerte la respuesta para tu dilema, si tienes preguntas puedes dejarnos tu comentario y te contestamos sin falta, porque estamos para ayudarte.

Solución:

Para obtener el resultado exacto como Monitor de actividad:

He modificado el script dado de la siguiente manera. Puede reproducir el monitor de actividad en un lado y este script en otra ventana y verificar la salida.

También puede ver las consultas costosas que se están ejecutando actualmente utilizando este script y para eso solo necesita hacer ORDENAR POR [Total CPU (ms)] desc.

Este script mostrará lo siguiente:

  1. [Session ID]
  2. [User Process]
  3. [Login]
  4. [Blocked By]
  5. [Head Blocker]
  6. [DatabaseName]
  7. [Task State]
  8. [Command]
  9. [statement_text] – Mostrará la declaración que se está ejecutando actualmente y puede ser del SP o del T-sql normal.
  10. [command_text] —– Mostrará el nombre del procedimiento almacenado.
  11. [Total CPU (ms)]
  12. ‘Tiempo transcurrido (en segundos)’
  13. [Wait Time (ms)]
  14. [Wait Type]
  15. [Wait Resource]
  16. [Memory Use (KB)]
  17. [Host Name]
  18. [Net Address]
  19. [Workload Group]
  20. [Application]

También puede agregar o quitar las columnas que necesite.

He comentado algunas columnas en la consulta, como: -[Open Transactions Count] = ISNULL (r.open_transaction_count, 0), -[Login Time] = s.login_time, -[Last Request Start Time] = s.last_request_start_time, por lo tanto, si lo desea, también puede agregar o eliminar las columnas según sus requisitos y también puede filtrar los datos DatabaseName. Espero que este guión nos ayude a muchos de nosotros.

    /* 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 ;

También tengo mis scripts para hacer esto, pero recomiendo encarecidamente sp_whoisactive, que ha sido ampliamente utilizado, incluye muchas características y puede usarse para una variedad de propósitos, incluido el monitoreo.

Solo eche un vistazo a los siguientes enlaces para tener una idea:

Cómo utilizar sp_WhoIsActive para encontrar consultas lentas de SQL Server

Whoisactive capturando un servidor SQL consultas Performance Tuning

Esta consulta devolverá información muy similar a la que devuelve el monitor de actividad, incluido el texto de la consulta que está ejecutando el proceso.

SELECT 
       SessionId    = s.session_id, 
       UserProcess  = CONVERT(CHAR(1), s.is_user_process),
       LoginInfo    = s.login_name,   
       DbInstance   = ISNULL(db_name(r.database_id), N''), 
       TaskState    = ISNULL(t.task_state, N''), 
       Command      = ISNULL(r.command, N''), 
       App            = ISNULL(s.program_name, N''), 
       WaitTime_ms  = ISNULL(w.wait_duration_ms, 0),
       WaitType     = ISNULL(w.wait_type, N''),
       WaitResource = ISNULL(w.resource_description, N''), 
       BlockBy        = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
       HeadBlocker  = 
            CASE 
                -- session has active request; is blocked; blocking others
                WHEN r2.session_id IS NOT NULL AND r.blocking_session_id = 0 THEN '1' 
                -- session idle; has an open tran; blocking others
                WHEN r.session_id IS NULL THEN '1' 
                ELSE ''
            END, 
       TotalCPU_ms        = s.cpu_time, 
       TotalPhyIO_mb    = (s.reads + s.writes) * 8 / 1024, 
       MemUsage_kb        = s.memory_usage * 8192 / 1024, 
       OpenTrans        = ISNULL(r.open_transaction_count,0), 
       LoginTime        = s.login_time, 
       LastReqStartTime = s.last_request_start_time,
       HostName            = ISNULL(s.host_name, N''),
       NetworkAddr        = ISNULL(c.client_net_address, N''), 
       ExecContext        = ISNULL(t.exec_context_id, 0),
       ReqId            = ISNULL(r.request_id, 0),
       WorkLoadGrp        = N'',
       LastCommandBatch = (select text from sys.dm_exec_sql_text(c.most_recent_sql_handle)) 
    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 
    (
        -- Using row_number to select longest wait for each thread, 
        -- should be representative of other wait relationships if thread has multiple involvements. 
        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)
    OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as st

    WHERE s.session_Id > 50                         -- ignore anything pertaining to the system spids.

    AND s.session_Id NOT IN (@@SPID)     -- let's avoid our own query! :)

    ORDER BY s.session_id;

Para obtener más información sobre el Monitor de actividad de SQL Server, puede seguir el blog de Milena Petrovic aquí y también el blog de MSDN aquí.

Eres capaz de añadir valor a nuestro contenido informacional dando tu experiencia en las acotaciones.

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *