DMV-3 : What is currently going on ?……..sys.dm_exec_requests

sys.dm_exec_requests DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms177648.aspx

Returns information about each request that is executing within SQL Server.

sys.dm_exec_requests DMV is used to get details of currently running sessions. This DMV help us is getting details like used database, transaction isolation level, open transaction count, status, blocked session, percentage of task completed etc.

Query 1 : Complete details of currently running queries

SELECT

R.SESSION_ID,

R.REQUEST_ID AS SESSION_REQUEST_ID,

R.STATUS,

S.HOST_NAME,

C.CLIENT_NET_ADDRESS,

CASE WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME ELSE S.LOGIN_NAME + ‘ (‘ + S.ORIGINAL_LOGIN_NAME + ‘)’ END AS LOGIN_NAME,

S.PROGRAM_NAME,

DB_NAME(R.DATABASE_ID) AS DATABASE_NAME,

R.COMMAND,

ST.TEXT AS QUERY_TEXT,

QP.QUERY_PLAN AS XML_QUERY_PLAN,

R.WAIT_TYPE AS CURRENT_WAIT_TYPE,

R.LAST_WAIT_TYPE,

R.BLOCKING_SESSION_ID,

R.ROW_COUNT,

R.GRANTED_QUERY_MEMORY,

R.OPEN_TRANSACTION_COUNT,

R.USER_ID,

R.PERCENT_COMPLETE,

CASE R.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’

ELSE CAST(R.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))

END AS TRANSACTION_ISOLATION_LEVEL_NAME

FROM

SYS.DM_EXEC_REQUESTS R

LEFT OUTER JOIN SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = R.SESSION_ID

LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.CONNECTION_ID = R.CONNECTION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) ST

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) QP

WHERE

R.STATUS NOT IN (‘BACKGROUND’,’SLEEPING’)

Query 2 : How much task is completed in percentage? It’s my one of the favorite query while doing backup, restore check DB or index rebuild.

SELECT

A.SESSION_ID,

DB_NAME(A.DATABASE_ID) AS DATABASE_NAME,

A.START_TIME,A.COMMAND,

A.CPU_TIME,

A.PERCENT_COMPLETE,

A.ESTIMATED_COMPLETION_TIME,

B.TEXT

FROM SYS.DM_EXEC_REQUESTS A

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE)AS B

WHERE SESSION_ID > 50

Remarks

1. All Possible status of sessions :

· Background

· Running

· Runnable

· Sleeping

· Suspended

2. Percentage of work completed can be viewed for the following commands:

· ALTER INDEX REORGANIZE

· AUTO_SHRINK option with ALTER DATABASE

· BACKUP DATABASE

· DBCC CHECKDB

· DBCC CHECKFILEGROUP

· DBCC CHECKTABLE

· DBCC INDEXDEFRAG

· DBCC SHRINKDATABASE

· DBCC SHRINKFILE

· RECOVERY

· RESTORE DATABASE,

· ROLLBACK

· TDE ENCRYPTION

3. Exceptions for Blocking Session ID :

· -2 = The blocking resource is owned by an orphaned distributed transaction.

· -3 = The blocking resource is owned by a deferred recovery transaction.

· -4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions

4. All possible Transaction isolation level of the session, on basis on integer values :

· 0 = Unspecified

· 1 = ReadUncomitted

· 2 = ReadCommitted

· 3 = Repeatable

· 4 = Serializable

· 5 = Snapshot

5. Permissions : User required VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server, otherwise, the user will see only the current session.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)