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

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


2 Comments

  1. Alex says:

    Pretty usefull query. The only thing is that it is for SQL-2012 only, since earlier versions of DM_EXEC_SESSIONS don’t have database_id column.

    • Yes, earlier versions of DM_EXEC_SESSIONS don’t have database_id column.We can remove that & it will work in earlier versions as well OR we can make this query a bit long by referencing database id from other system catalogs.

      SELECT
      S.SESSION_ID,
      S.STATUS,
      S.HOST_NAME,
      DB_NAME(SP.DBID) 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
      JOIN sys.sysprocesses SP on SP.spid = S.SESSION_ID
      WHERE
      S.SESSION_ID IS NULL
      OR S.SESSION_ID > 50
      ORDER BY
      S.SESSION_ID

Leave a Reply

Join us on Facebook

microsoftcommunitycontributor
%d bloggers like this: