How It Works: SQL Trace and MARS Connections
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
Comments
- Anonymous
December 30, 2008
PingBack from http://www.sqldocumentor.com/report-manager-sql-server-no-longer-uses-rdtsc-performance-monitors