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/)
