Home » Posts tagged 'SYS.DM_EXEC_SESSIONS'

Tag Archives: SYS.DM_EXEC_SESSIONS

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

Advertisements

DMV-2 : Explore the secrets of session…… sys.dm_exec_sessions


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

Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.

sys.dm_exec_sessions is extremely useful DMV when you need to fetch details like login name, transaction isolation level, status, used databases, language etc.

Query 1 : Complete details of every SQL Session

SELECT

S.SESSION_ID,

S.STATUS,

S.HOST_NAME,

DB_NAME(S.DATABASE_ID) DBNAME,

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,

C.CONNECT_TIME, — DIFFRENT BETWEEN CONNECT & LOGIN TIME IS TIME TAKEN BY PRELOGON ACTIVITIES

S.LOGIN_TIME,

CASE S.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(S.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))

END AS TRANSACTION_ISOLATION_LEVEL_NAME,

–S.LAST_SUCCESSFUL_LOGON, — REQUIRES ‘COMMON CRITERIA COMPLIANCE ENABLED’ OPTION VIA SP_CONFIGURE.

–S.LAST_UNSUCCESSFUL_LOGON, — REQUIRES ‘COMMON CRITERIA COMPLIANCE ENABLED’ OPTION VIA SP_CONFIGURE.

–S.UNSUCCESSFUL_LOGONS, — REQUIRES ‘COMMON CRITERIA COMPLIANCE ENABLED’ OPTION VIA SP_CONFIGURE.

S.CPU_TIME AS CPU_TIME_MS,

S.MEMORY_USAGE AS MEMORY_USAGE_PAGES,

S.ROW_COUNT,

S.PREV_ERROR,

S.LAST_REQUEST_START_TIME,

S.LAST_REQUEST_END_TIME,

C.NET_TRANSPORT,

C.PROTOCOL_TYPE,

S.LANGUAGE,

S.DATE_FORMAT,

ST.TEXT AS QUERY_TEXT

FROM

SYS.DM_EXEC_SESSIONS S

FULL OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.SESSION_ID = S.SESSION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) ST

WHERE

S.SESSION_ID IS NULL

OR S.SESSION_ID > 50

ORDER BY

S.SESSION_ID

Query 2 :  What number session every login have?

SELECT LOGIN_NAME , COUNT(SESSION_ID) AS [SESSION_COUNT]

FROM SYS.DM_EXEC_SESSIONS

GROUP BY LOGIN_NAME ORDER BY COUNT(SESSION_ID) DESC ;

Remarks

1.       All Possible status of sessions :

·         Running – Currently running one or more requests

·         Sleeping – Currently running no requests

·         Dormant – Session has been reset because of connection pooling and is now in prelogin state.

·         Preconnect – Session is in the Resource Governor classifier.

2.       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

3.       Common Criteria Compliance Enabled : If this option in server configuration is enabled, logon statistics are displayed in the following columns. If this option is not enabled, these columns will return null values.

·         last_successful_logon

·         last_unsuccessful_logon

·         unsuccessful_logons

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

DMV-1 : Who All Are connected ? …. sys.dm_exec_connections


The sys.dm_exec_connections DMV (Dynamic Management View) is represented by Books Online (BOL) as follows : http://msdn.microsoft.com/en-us/library/ms181509.aspx

Returns information about the connections established to this instance of SQL Server and the details of each connection.

sys.dm_exec_connections is the most common DMV used to get connection details. We will get details info regarding every connection like protocol user, last read time, last write time, last executed SQL query, connection time etc.

Query 1 : Complete details of every SQL Connection

SELECT

C.SESSION_ID,

C.MOST_RECENT_SESSION_ID,

C.CONNECT_TIME,

C.LAST_READ,

C.LAST_WRITE,

C.NUM_READS,

C.NUM_WRITES,

C.NET_TRANSPORT,

C.ENCRYPT_OPTION,

C.AUTH_SCHEME,

C.PROTOCOL_TYPE,

C.PROTOCOL_VERSION,

C.NET_PACKET_SIZE,

C.ENDPOINT_ID,

C.CLIENT_NET_ADDRESS,

C.CLIENT_TCP_PORT,

C.LOCAL_NET_ADDRESS,

C.LOCAL_TCP_PORT,

C.NODE_AFFINITY,

C.CONNECTION_ID,

C.PARENT_CONNECTION_ID,

C.MOST_RECENT_SQL_HANDLE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,

CASE WHEN ST.DBID IS NULL THEN NULL ELSE OBJECT_SCHEMA_NAME(ST.OBJECTID, ST.DBID) END AS OBJECT_SCHEMA_NAME,

CASE WHEN ST.DBID IS NULL THEN NULL ELSE OBJECT_NAME(ST.OBJECTID, ST.DBID) END AS OBJECT_NAME,

ST.TEXT AS QUERY_TEXT

FROM

SYS.DM_EXEC_CONNECTIONS C

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) ST

Query 2 : Sample Query Get a count of SQL connections by IP address

SELECT EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ,

ES.[HOST_NAME] ,

ES.LOGIN_NAME ,

COUNT(EC.SESSION_ID) AS [CONNECTION COUNT]

FROM SYS.DM_EXEC_SESSIONS AS ES

INNER JOIN SYS.DM_EXEC_CONNECTIONS AS EC

ON ES.SESSION_ID = EC.SESSION_ID

GROUP BY EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ,

ES.[HOST_NAME] ,

ES.LOGIN_NAME

ORDER BY EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ;

Permissions : User required VIEW SERVER STATE permission on the server, to use this DMV.

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

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