Sql-server – SQL Server Network Packet Size

configurationsql server

During the analysis of network traffic from Citrix clients to our database server, we noticed that we had a lot of RETRANSMITs occurring during the transport of TCP packets. The packets were being split and resent.

To determine the MTU size in our network I set off the following command:

ping 10.28.212.22 -l 4096 -f

Ah yes, for those interested: The -f parameter tells ping to not fragment the ping packet, if it would have to be split up and then resent.

I then started reducing the -l parameter value until I received the following message:

PS C:\temp> ping 10.28.212.22 -l 1474 -f

Ping wird ausgefhrt fr 10.28.212.22 mit 1474 Bytes Daten:
Paket msste fragmentiert werden, DF-Flag ist jedoch gesetzt.
Paket msste fragmentiert werden, DF-Flag ist jedoch gesetzt.
Paket msste fragmentiert werden, DF-Flag ist jedoch gesetzt.
Paket msste fragmentiert werden, DF-Flag ist jedoch gesetzt.


Ping-Statistik fr 10.28.212.22:
    Pakete: Gesendet = 4, Empfangen = 0, Verloren = 4
    (100% Verlust),

PS C:\temp> ping 10.28.212.22 -l 1472 -f

Ping wird ausgefhrt fr 10.28.212.22 mit 1472 Bytes Daten:
Antwort von 10.28.212.22: Bytes=1472 Zeit=3ms TTL=123
Antwort von 10.28.212.22: Bytes=1472 Zeit=2ms TTL=123
Antwort von 10.28.212.22: Bytes=1472 Zeit=2ms TTL=123
Antwort von 10.28.212.22: Bytes=1472 Zeit=2ms TTL=123

Ping-Statistik fr 10.28.212.22:
    Pakete: Gesendet = 4, Empfangen = 4, Verloren = 0
    (0% Verlust),
Ca. Zeitangaben in Millisek.:
    Minimum = 2ms, Maximum = 3ms, Mittelwert = 2ms

Because the ping packet itself has a header of 28 bytes, the value 1472 which was successfully returned from the ping command, corresponds with our network's MTU size of 1500 bytes.

1472 + 28 = 1500

A SQL Server instance has an advanced configuration setting named network packet size, which has a default value of 4096 (bytes). Microsoft recommends that one do not alter this parameter unless one is an experienced databases administrator or certified SQL Server professional:

This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server professional.

Reference: Configure the network packet size Server Configuration Option (Microsoft | SQL Docs)

Sadly, the document does not go into any detail and does not provide much insight as to whether or not one should alter this parameter if one is encountering lots of RETRANSMITs of network packets. I do consider myself a seasoned DBA, but I have never had to deal with this situation. At one point in time, this value used to be 512 (SQL Server 7.0?).

I have read the previous question:

…but it doesn't provide a definite answer.

On further reading I came across the TDS protocol which has this bit of information in the introduction:

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.
  • Transaction manager requests.

Sections 1.5, 1.8, 1.9, 2, and 3 of this specification are normative. All other sections and examples in this specification are informative.

Reference: 1 Introduction (Microsoft | Docs)

Well why TDS? Apparently, this is how the data is transported back to the client via the TDS protocol.

  • The return of data.

Please don't ask which OSI layer this is on. It should be layer 7 as it is an application layer…

The TDS "packet" has a header of 8 bytes.

Questions

Based on what I have read and the situation at hand, should I …

  1. Set the network packet size parameter to 1500?
  2. Set the network packet size parameter to 1492? (TDS packet size – TDS header)
  3. Set the network packet size parameter to a different value?
  4. Leave the network packet size at 4096?

I do understand that splitting the data into smaller chunks can have a negative impact on performance, but the network stack will be (and is) doing that anyway.

In a perfect world I would allow Jumbo packets to flow freely between clients and databases servers or between application servers and database servers, but that is not possible (as in: allowed) in our environment.