Hi-

We are installing an app and the vendor recommends as part of their setup process to increase the network packet size on the instance of SQL where the DB is being installed.  After looking into this a bit more, I have a couple of questions about this before implementing it.

1. Can the network packet size be increased "on the fly?"  In other words, do I have to reset the SQL instance for the change to take effect?

2. I've read conflicting information about Network Packet Size vs. MTU of the network.  Some sources state that your network packet size should match your network's MTU.  However, I've read other sources stating that MTU doesn't really matter since SQL generally operates at the application layer of the OSI model.  So I guess the question is, as a guideline (YMMV of course), does increasing the network packet size beyond the MTU of the network help or hurt performance?

3. I've also read that the network packet size set in the Advanced server properties window is only the _default_ applied to a client that does not specify a packet size when connecting.  Is this true?  If so, how does a SQL admin actually manage the maximum network packet size a client can request?

Network Packet size is not something you normally would change.  What are they recommending you change it too?

Please see:

https://msdn.microsoft.com/en-us/library/ms177437(v=sql.120).aspx

Yes, I read that link which is what started me looking into this.

The vendor recommends the setting be changed to 8192.  Its a geo-database product and users are typically dealing with large data sets.

Also I won't recommend to change the packet size in general.

If the vendor thinks a higher packet size will increase performance then I wonder why he don't define one in connection string used by his application: https://www.connectionstrings.com/sqlconnection/specifying-packet-size/

Changing the packet size at the server to 8192 will likely have no increase on performance.  You could try it and then change it back after they are gone.

• Edited by Tom PhillipsModerator 20 hours 20 minutes ago

Also I won't recommend to change the packet size in general.

If the vendor thinks a higher packet size will increase performance then I wonder why he don't define one in connection string used by his application:  https://www.connectionstrings.com/sqlconnection/specifying-packet-size/

OK, so that goes to my third question.  The value set in the instance properties is only what gets used if the client doesn't specify a packet size to use.  Is that correct?

Yes, that far correct, but all data provider I know uses a default value on it's own; see the link I provided:

"By default, the Microsoft .NET Framework Data Provider for SQL Server sets the network packet size to 8192 Bytes"

OK, can anyone comment on a relationship (if any) between SQL's Network Packet Size and the MTU of the underlying link?

Try:
ping servername -f -l 8192

If answer is then MTU is too large in your network:
Packet needs to be fragmented but DF set.