MSDN, TechNet, Books Online and other sources outline what Multiple Active Result Sets (MARS) is, how to use it and transaction scopes.   Working on an issue this week I found I needed to understand what the trace looked like.  In doing so I found the following helpful.

The trace column (RequestId) represents the logical connection id for a MARS connection.  

ODBC / ADO / SQLConnection / SQLOLEDB

The following, simplistic example, shows

• HBDC: ODBC connection opening (RequestId = 0)
• HSTMT: (1) select * from dm_os_workers  (RequestId = 1)
• HSTMT: (2) select * from dm_os_workers, dm_os_threads (RequestId = 2)

The HSTMT's become logical connections for SPID/Session 52.

HTTP

This can become a bit muddy when you use SQL Server HTTP end-points.  HTTP endpoints set the RequestId but HTTP is not MARS enabled.   Multiple requests submitted on the same HTTP session will be processed in received.

TraceLoginEvent(AUDIT_LOGIN, LOGICAL)   Request 1650

    Command starting  Request 1650

    Command completed   Request 1650

TraceLoginEvent(AUDIT_LOGOUT, LOGICAL) Request 1650

 

 

TraceLoginEvent(AUDIT_LOGIN, LOGICAL)   Request 1651

    Command starting  Request 1651

    Command completed   Request 1651

TraceLoginEvent(AUDIT_LOGOUT, LOGICAL) Request 1651 

NOT  (Valid for MARS not HTTP)

TraceLoginEvent(AUDIT_LOGIN, LOGICAL)   Request 1650

    Command starting  Request 1650

 

TraceLoginEvent(AUDIT_LOGIN, LOGICAL)   Request 1651

    Command starting  Request 1651

    Command completed   Request 1651

TraceLoginEvent(AUDIT_LOGOUT, LOGICAL) Request 1651

 

    Command completed   Request 1650

TraceLoginEvent(AUDIT_LOGOUT, LOGICAL) Request 1650

The RequestId assigned to an HTTP request is the batch sequence for the session, incremented as the session processes a new request.

Use The Login Event

The binary, trace column of the login event (Audit Login / Existing Connection) contains a bit to indicate if the connection was MARS enabled.

The '1' highlighed in the binary data indicates IS MARS == TRUE.

Bob Dorr
SQL Server Principal Escalation Engineer