ReadTrace Generates Unexpected Connect Node - Not Really

  • Article

At first glance it appears that ReadTrace has generated an unexpected connect node.  Upon closer inspection the behavior is correct in order to maintain the proper set options for the connection.

Trace has both physical and logical connect/disconnect events.   The logical is indicated with a sub-class value of 2.   You can observe the following pattern.

Existing Connection - sub-class 1  (Should be output as connect node)

RPC Events
SQL:Batch Events

RPC:Starting  - sp_reset_connection
Disconnect - sub-class 2  (Should be ignored)

RPC:Completed - sp_reset_connection

Connect - sub-class 2   (Should be ignored) 

Notice that the RML output contains a connect node for the logical connection associated with the reset connection.   This is because the set options changed.  ReadTrace tracks the set options for the session and if they change forces a connect to occur.   The reason this scenarios changes the options is because of the existing connection.   When the trace was started the connection options are output based on the current running values.  The sp_reset_connection resets the options to that of the original connection.   So ReadTrace detects that the session would not replay the sp_reset_connection properly and forces the session to disconnect and reconnect in order to establish the proper set options.

<CONNECT SEQ="-1" NEXTSEQ="88535316">

<CMD>-- network protocol: TCP/IP
  set quoted_identifier off
</CMD>
</CONNECT>

<LANG SEQ="88539227" NEXTSEQ="88539481" REPLAY_DURATION="1" DELTA="1700000">

<CMD>IF @@TRANCOUNT &gt; 0 COMMIT TRAN</CMD>
</LANG>

<RPC SEQ="88567008" NEXTSEQ="88567024" REPLAY_DURATION="1" DELTA="8000000">

<CMD>{call sp_reset_connection}</CMD>

</RPC>

<CONNECT SEQ="88567037" NEXTSEQ="88567049" DELTA="8000000">

<CMD>-- network protocol: TCP/IP

  set quoted_identifier on

</CMD>
</CONNECT>

 

Bob Dorr
SQL Server Principal Escalation Engineer