How It Works: SQL Trace and MARS Connections

  • Article

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.

image

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.

image

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

Bob Dorr
SQL Server Principal Escalation Engineer