Active Processes


This time I'll show you a script developed to cature running processes details.

There are a number of similar scripts out of there, but I've tried to use a single query based on DMVs only (no more sys.sysprocesses).

Exluding the deprecated sys.sysprocesses was the only tricky task, because there is no straight way to obtain the database_id unless we are using Sql Server 2012 which added this column to sys.dm_exec_sessions.

Fortunately sys.dm_tran_locks contains the information we need.

SET TRANSACTION isolation level READ uncommitted

         WHEN ER.total_elapsed_time > 0 THEN
         CONVERT(VARCHAR, (ER.total_elapsed_time - ER.total_elapsed_time %
         3600000) /
         + ':'
         + RIGHT('00' + CONVERT(VARCHAR, (ER.total_elapsed_time -
         % 60000) % 3600000 /
         60000), 2)
         + ':'
         + RIGHT('00' + CONVERT(VARCHAR, (ER.total_elapsed_time -
         % 1000) % 60000 / 1000), 2)
         + '.'
         + RIGHT('000' + CONVERT(VARCHAR, ER.total_elapsed_time % 1000), 3)
       NULLIF(ER.blocking_session_id, 0)
       Db_name(COALESCE(ER.database_id, TL.resource_database_id, 1))
       ES.reads                                                      total_reads
         WHEN Isnull(ER.open_transaction_count, 0) > 0 THEN
         WHEN ST.open_tran > 0 THEN ST.open_tran
       CASE Isnull(ER.transaction_isolation_level,
         WHEN 0 THEN 'Unspecified'
         WHEN 1 THEN 'ReadUncomitted'
         WHEN 2 THEN 'ReadCommitted'
         WHEN 3 THEN 'Repeatable'
         WHEN 4 THEN 'Serializable'
         WHEN 5 THEN 'Snapshot'
       CASE AT.transaction_type
         WHEN 1 THEN 'read/write'
         WHEN 2 THEN 'read-only'
         WHEN 3 THEN 'system'
         WHEN 4 THEN 'distributed'
       CASE AT.transaction_state
         WHEN 0 THEN 'not completely initialized'
         WHEN 1 THEN 'initialized but not started'
         WHEN 2 THEN 'active'
         WHEN 3 THEN 'read-only transaction has ended'
         WHEN 4 THEN 'commit initiated'
         WHEN 5 THEN 'prepared and waiting resolution'
         WHEN 6 THEN 'committed'
         WHEN 7 THEN 'being rolled back'
         WHEN 8 THEN 'been rolled back'
FROM   sys.dm_exec_connections EC
       INNER JOIN sys.dm_exec_sessions ES
               ON ES.session_id = EC.session_id
       LEFT JOIN sys.dm_exec_requests ER
              ON ER.session_id = EC.session_id
       LEFT JOIN (SELECT Count(*) open_tran,
                  FROM   sys.dm_tran_session_transactions XT
                  GROUP  BY XT.session_id) ST
              ON ST.session_id = ES.session_id
       LEFT JOIN sys.dm_tran_active_transactions AT
              ON AT.transaction_id = ER.transaction_id
       OUTER apply sys.Dm_exec_sql_text(Isnull(ER.sql_handle,
                                        EC.most_recent_sql_handle)) ET
       OUTER apply (SELECT TOP 1 XL.resource_database_id
                    FROM   sys.dm_tran_locks XL
                    WHERE  XL.resource_type = 'DATABASE'
                           AND XL.request_session_id = EC.session_id) TL
WHERE  EC.most_recent_sql_handle > 0x00
ORDER  BY ER.total_elapsed_time DESC,

This script will be included in the next version of SqlDbAid.

No comments:

Post a Comment