How It Works: SQL Trace and MARS Connections

Published Jan 15 2019 11:13 AM 73 Views
Microsoft
First published on MSDN on Dec 12, 2008

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

Version history
Last update:
‎Jan 15 2019 11:13 AM
Updated by: