TCP Storm in SQL Server

In this blog post I’ll be demonstrating how a TSQL loop can cause a massive amount of TCP packets going from SQL Server to client. I used Wireshark to record the traffic and then the Tabular Data Stream (TDS) dissector for reconstructing the TDS stream.

First, let’s examine the exchange between client and server in a normal case.

The following loop has a 1 second delay:

WHILE 1 = 1
BEGIN
  IF 1 = 0 SELECT 1
  WAITFOR DELAY '00:00:01'
END;

And its execution will produce the following messages:

Source      Destination Protocol Length Info
client      server      TDS      112    SQL batch
server      client      TDS      91     Response
client      server      TDS      234    SQL batch
server      client      TCP      54     49501 → 58205 [ACK] Seq=38 Ack=239 Win=8207 Len=0

As you can see, there were two batches sent from the client. One is the implicit “select from @@SPID” command. The other is our endless loop. After receiving the SQL batch, the server sent a zero size ACK to the client. There weren’t any other messages while the loop was running.

But something strange happened after removing the delay from the loop:

WHILE 1 = 1
BEGIN
  IF 1 = 0 SELECT 1
END;

After receiving the SQL batch from the client, the SQL Server started sending an “endless” byte stream instead of a zero size ACK.

Source      Destination Protocol Length Info
client      server      TDS      112    SQL batch
server      client      TDS      107    Response
client      server      TDS      178    SQL batch
server      client      TCP      1514   49501 → 63979 [ACK] Seq=54 Ack=183 Win=8211 Len=1460 [TCP segment of a reassembled PDU]
server      client      TCP      1514   49501 → 63979 [ACK] Seq=1514 Ack=183 Win=8211 Len=1460 [TCP segment of a reassembled PDU]
server      client      TDS      1230   Response (Not last buffer)
server      client      TCP      1514   49501 → 63979 [ACK] Seq=4150 Ack=183 Win=8211 Len=1460 [TCP segment of a reassembled PDU]
server      client      TCP      1514   49501 → 63979 [ACK] Seq=5610 Ack=183 Win=8211 Len=1460 [TCP segment of a reassembled PDU]
server      client      TDS      1230   Response (Not last buffer)
...

This anomaly can be reproduced on SQL Server 2014, 2016 and 2017. On SQL Server 2014, it can even cause a serious performance bottleneck when TDS encryption is turned on, because every encrypted message will result in a context switch. Fortunately, there isn’t such context switching in the later releases.

By the way, I elaborated on this in my previous blog post.