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
