Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



Use Microsoft Message Analyzer to capture TDS Packets
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-02-21









Tabular Data Stream Protocol:


The Tabular Data Stream (TDS) protocol is an application layer request/response protocol that facilitates interaction with a database server and provides for the following:
- Authentication and channel encryption negotiation.
- Specification of requests in SQL (including Bulk Insert).
- Invocation of a stored procedure or user-defined function, also known as a remote procedure call (RPC).
- The return of data. (self-describing and record-oriented)
- Transaction manager requests.

TDS Protocol does transfer of requests and responses between Clients and Database Servers, where the Client will typically establish a long-lived connection with the server. Once the connection is established using a transport-level protocol, TDS messages are used to communicate between the client and the server. The TDS session is directly tied to the transport-level session, meaning that a TDS session is established when the transport-level connection is established and the server receives a request to establish a TDS connection. It persists until the transport-level connection is terminated (for example, when a TCP socket is closed). In addition, TDS does not make any assumption about the transport protocol used, but it does assume the transport protocol supports reliable, in-order delivery of the data.

A TDS data stream can span multiple network data packets.

SQL Server Network Interface (SNI) protocol layer encapsulates the TDS packet inside a standard communication protocol such as TCP/IP. The SNI protocol layer is common to both the Database Engine ("Server") and SQL Server Native Client ("Clients"). When the client wants to send a message to the server, T-SQL query for example, the SNI packages the message into a TDS packet and ships it over the network. On the Server, there are a set of endpoints listening for TDS packets. The TDS endpoints are installed by SQL Server during SQL Server installation. Each network protocol has one of these TDS endpoints assigned to it, whether the protocol has been enabled or not. There is also one for DAC connections. You can view these system endpoints in the sys.endpoints system view. You can view details on TCP/IP-specific endpoints using sys.tcp_endpoints.


High-Level flow of communications in the TDS Protocol:






Microsoft Message Analyzer:


Microsoft Message Analyzer is a tool for capturing, displaying & analyzing protocol messaging traffic and other system messages. It is the successor to Microsoft Network Monitor 3.4 and Message Analyzer v1.3. It is available for download here.


I used Microsoft Message Analyzer to capture TDS packets and it's containing Stored Procedure calls.
To Start a new trace - File..FavoriteScenarios..Local Network Interfaces.
Add Filter - ((*address==x.x.x.x)) and (TDS)
Add Column - SQLBatchPacketData.SQLText


Capture RPC Calls:





Message Stack Details:




As per TDS Protocol Specification, Server Response: 0xFE 254 Process Done




SQL Server Management Studio - Include Client Statistics


Use Client Statistics to gather information about execution times & the amount of data sent between client and server.
To turn on or off the gathering of client statistics: Open a Transact-SQL editor session. On the Query menu, click Include Client Statistics.