CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

How It Works: SQL Trace and MARS Connections

How It Works: SQL Trace and MARS Connections

  • Comments 1

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

Leave a Comment
  • Please add 3 and 4 and type the answer here:
  • Post