SQL Server – Extended Events – Internal Session Definitions

Background

Wanted to track Extended Events, but ran into a stumbling block.

And, we go…

 

Metadata

Extended Events

Active Sessions ( sys.dm_xe_sessions )

Code


select 
		  [sessionName] = tblXES.[name]
		, [sessionCreateTime] = tblXES.[create_time]
		, [sessionSource] = tblXES.[session_source]
		--  tblXES.*

from   sys.dm_xe_sessions tblXES

Output

Explanation

  1. session_source
    • We can group Extended Event based on Source
      • Source :-
        • server
        • internal
    • In our case
      • server
        • system_health
          • MSFT Defined
        • performance
          • Defined by us
      • internal
        • hkenginexesession
          • Added in SQL Server v2016
          • Tracks Hekaton ( “In-Memory Database” Events )
        • sp_server_diagnostics session
          • Added in SQL Server v2014
          • SQL Server Diagnostic Events

 

Extended Event Definitions ( sys.server_event_sessions )

Code


select 
		  [sessionID] = tblSES.[event_session_id]

		, [extendedEvent]
			 = tblSES.[name]

		, [eventRetentionMode] 
			= tblSES.[event_retention_mode]

		, [trackCasualty]
			 = tblSES.[track_causality]

		, [startupState] 
			= tblSES.[startup_state]

		--, tblSES.*

from   sys.server_event_sessions tblSES

Output

Explanation

  1. sessionID
    • event_session_id
      • Starts at 65534
  2. Extended Event
    • name
  3. Event Retention Mode
    • Column :- event_retention_mode
  4. Track Casualty
    • track_causality
  5. Startup Start
    • startup_state

Tie things together

Let us join our two sources,  sys.server_event_sessions & sys.dm_xe_sessions.

 

Inner Join – sys.server_event_sessions & sys.dm_xe_sessions

Code

select 
		  [sessionID] =  tblSES.[event_session_id]
		, [extendedEvent] = tblSES.[name]
		, [eventRetentionMode] = tblSES.[event_retention_mode]
		, [trackCausality ] = tblSES.[track_causality]
		, [startupState] = tblSES.[startup_state]

		 , [sessionCreateTime] = tblXES.[create_time]
		 , [sessionSource] = tblXES.[session_source]

from  sys.server_event_sessions tblSES

left outer join sys.dm_xe_sessions tblXES

        on tblSES.[name]  = tblXES.[name]

Output

Explanation

  1. sessionID
    • event_session_id
  2. Extended Event
    • name
  3. Event Retention Mode
    • Column :- event_retention_mode
  4. Track Casualty
    • Column :- track_causality
  5. Startup State
    • Column :- startup_state
  6. sessionCreateTime
    • Column :- sys.dm_xe_sessions.create_time
  7. sessionSource
    • Column :- sys.dm_xe_sessions.session_source

Workaround ( using cte ) – sys.server_event_sessions & sys.dm_xe_sessions

Code

; with cteExtendedEvent
(
      [name]
    , [id]
    , [sessionSource]
)
as
(

    /*
        Get Defined Event Sessions
    */ 
    select 
              tblSES.[name]

            , [sessionID]
                = tblSES.[event_session_id]

            , [sessionSource]
                = null


    from   sys.server_event_sessions tblSES

    union
    
    /*
        Get Running Event Sessions that are shown in  sys.server_event_sessions
    */ 

    select 
              tblXES.[name]

            , cast(null as int)

            , [sessionSource]
                = tblXES.[session_source]

    from   sys.dm_xe_sessions tblXES

    where  tblXES.[name] not in
                (
                    select [name]
                    from   sys.server_event_sessions tblSES
                )


)
select 

          [extendedEvent] 
            = cteEE.[name]

        , [sessionID] 
             =  tblSES.[event_session_id]


        , [eventRetentionMode]
             = tblSES.[event_retention_mode]

        , [trackCausality] 
            = tblSES.[track_causality]

        , [startupState] 
            = tblSES.[startup_state]

        , [sessionCreateTime]
             = tblXES.[create_time]

        , [sessionSource]
             = tblXES.[session_source]


from  cteExtendedEvent cteEE

left outer join sys.server_event_sessions tblSES

        on cteEE.[name] = tblSES.[name] 

left outer join sys.dm_xe_sessions tblXES

        on cteEE.[name]  = tblXES.[name]

Output

Summary

An astute mind will see that there are some events listed in sys.dm_xe_sessions, but not in sys.server_event_sessions.

Again sys.dm_xe_sessions tracks running Event Sessions.  Whereas sys.server_event_sessions tracks sessions definitions.

Also, keep in mind that sys.dm_xe_sessions is a dynamic management view.  And, sys.server_event_sessions is a system catalog view.

It appears that sys.server_event_sessions has chosen not to expose internal Extended events within the sys.server_event_sessions view.

MSFT choice makes some sense as sys.server_event_sessions by definition exposes definitions.

And, MSFT is likely saying these definitions are private and personal to us and we have chosen not to expose them.

 

References

  1. Microsoft
    • Docs / SQL / Relational databases / System catalog views/
      • sys.server_event_sessions ( Transact-SQL )
      • sys.dm_xe_sessions ( Transact-SQL )

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s