As DBAs, we tend to think of size in terms of 8K pages and 64K extents or in terms of how many GBs of storage we need. Rarely do we think of something so small that it’s still measured in bytes. The default network packet size in SQL Server is 4096 bytes or 4KB. There are recommendations out there to make this value larger for scenarios where network is a bottleneck, but very little guidance on knowing when to make this change or whether we should. I suspect that very little is known about the impact of increasing this value. Let’s investigate.
Network Packet Size and Maximum Transmission Unit
As I stated above, network packet size is the size of the packet sent out by SQL Server. That needs to be correlated to the maximum transmission unit (MTU) of the network path from the host server to the end recipient. The key here is that if you increase the network packet size above the maximum transmission unit, it could hurt performance. If the MTU of the network is a smaller size, then the packets being sent out have to be broken down into smaller packets. This adds overhead in the network and can even cause smaller packets sent behind the larger packets to be delayed.
Let’s do a quick test with ping from the command line. The following test will be performed on my laptop by simply pinging my wireless router. The MTU of my wireless network is 1500 bytes which is smaller than the default network packet size so I already know that some packet breakup will occur for external connections to my laptop. For my ping test, I am going to use the -l switch to tell it the size of packet to send. The ping itself has a 28 byte overhead, so I subtract 28 bytes from the size I specify to get the appropriate size. Below are the results of a ping test with the MTU (1500 bytes) of my network, the default network packet size (4096 bytes), and the maximum network packet size (32767 bytes) to see the difference.
C:\Users\SQLSoldier>ping 192.168.1.1 -l 1472 Pinging 192.168.1.1 with 1472 bytes of data: Reply from 192.168.1.1: bytes=1472 time=6ms TTL=64 Reply from 192.168.1.1: bytes=1472 time=5ms TTL=64 Reply from 192.168.1.1: bytes=1472 time=3ms TTL=64 Reply from 192.168.1.1: bytes=1472 time=3ms TTL=64 Ping statistics for 192.168.1.1: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 3ms, Maximum = 6ms, Average = 4ms C:\Users\SQLSoldier>ping 192.168.1.1 -l 4086 Pinging 192.168.1.1 with 4086 bytes of data: Reply from 192.168.1.1: bytes=4086 time=7ms TTL=64 Reply from 192.168.1.1: bytes=4086 time=7ms TTL=64 Reply from 192.168.1.1: bytes=4086 time=5ms TTL=64 Reply from 192.168.1.1: bytes=4086 time=6ms TTL=64 Ping statistics for 192.168.1.1: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 5ms, Maximum = 7ms, Average = 6ms C:\Users\SQLSoldier>ping 192.168.1.1 -l 32739 Pinging 192.168.1.1 with 32739 bytes of data: Reply from 192.168.1.1: bytes=32739 time=21ms TTL=64 Reply from 192.168.1.1: bytes=32739 time=19ms TTL=64 Reply from 192.168.1.1: bytes=32739 time=14ms TTL=64 Reply from 192.168.1.1: bytes=32739 time=13ms TTL=64 Ping statistics for 192.168.1.1: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 13ms, Maximum = 21ms, Average = 16ms
Notice the difference in time? Average round trip goes from 4ms to 6ms to 16ms. Sending larger packets of data can be slower, per packet, than smaller ones, particularly when there is additional overhead from having to break down the packet size. Once the packets reach the network, they are going at the speed of the smaller packet, so nearly all of the above difference in round trip time can be attributed to breaking down the packets into smaller packets.
Determining Maximum Transmission Unit
There is a chart of some well known MTUs here: Wikipedia: Maximum transmission unit.
But let’s assume that you don’t know the MTU of every hop and every piece of hardware between your SQL Server and the destination machine. Your network connection may be a 10Gb (gigabit) connection, but that doesn’t mean it is for the whole path. We can determine the MTU using the ping test. For this test, we will use the –-f switch to mark the packets as “don’t fragment”. This means the packets will not get broken down into smaller packets. They will be rejected. This is an especially good test for cases where there is a known destination like a web server. If you are planning to increase the packet size, you should make sure that the network between the two servers support it. for my examples below, I’m going to continue to ping my network router, but if this was a real scenario, I would ping my web server’s IP Address.
First, I test with the maximum network packet size, and I see that the ping attempts all result in lost packets due to the packets being marked to not be fragmented:
C:\Users\SQLSoldier>ping 192.168.1.1 -l 32739 -f Pinging 192.168.1.1 with 32739 bytes of data: Packet needs to be fragmented but DF set. Packet needs to be fragmented but DF set. Packet needs to be fragmented but DF set. Packet needs to be fragmented but DF set. Ping statistics for 192.168.1.1: Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),
If I repeat the test while reducing the size by 1/2 until I reach a size that doesn’t fail, I eventually end up at a size of 1024 bytes:
C:\Users\SQLSoldier>ping 192.168.1.1 -l 1024 -f Pinging 192.168.1.1 with 1024 bytes of data: Reply from 192.168.1.1: bytes=1024 time=4ms TTL=64 Reply from 192.168.1.1: bytes=1024 time=4ms TTL=64 Reply from 192.168.1.1: bytes=1024 time=3ms TTL=64 Reply from 192.168.1.1: bytes=1024 time=39ms TTL=64 Ping statistics for 192.168.1.1: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 3ms, Maximum = 39ms, Average = 12ms
Then by making small incremental changes, I can finally find the point at where it fails.
C:\Users\SQLSoldier>ping 192.168.1.1 -l 1472 -f Pinging 192.168.1.1 with 1472 bytes of data: Reply from 192.168.1.1: bytes=1472 time=6ms TTL=64 Reply from 192.168.1.1: bytes=1472 time=4ms TTL=64 Reply from 192.168.1.1: bytes=1472 time=5ms TTL=64 Reply from 192.168.1.1: bytes=1472 time=3ms TTL=64 Ping statistics for 192.168.1.1: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 3ms, Maximum = 6ms, Average = 4ms C:\Users\SQLSoldier>ping 192.168.1.1 -l 1473 -f Pinging 192.168.1.1 with 1473 bytes of data: Packet needs to be fragmented but DF set. Packet needs to be fragmented but DF set. Packet needs to be fragmented but DF set. Packet needs to be fragmented but DF set. Ping statistics for 192.168.1.1: Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),
In the above, we can see that the largest packet I could send without fragmentation is 1500 bytes (1472 + the ping overhead of 28 bytes). Remember that this was on a wireless home network. Your mileage will hopefully vary … a lot.
Update
Friend and fellow SQL professional (Microsoft PFE) Thomas Stringer (blog|@SQLife) sent me the following PowerShell script that he wrote to figure out the current MTU:
$UpperBoundPacketSize = 2048 if ($args -eq "-Q") {$QuietMode = 1} else {$QuietMode = 0} $IpToPing = "192.168.1.1" do { if ($QuietMode -eq 0) {Write-Host "Testing packet size $UpperBoundPacketSize"} $PingOut = ping $IpToPing -n 1 -l $UpperBoundPacketSize -f $UpperBoundPacketSize -= 1 } while ($PingOut[2] -like "*fragmented*") $UpperBoundPacketSize += 1 $Mtu = $UpperBoundPacketSize + 28 Write-Host "MTU: $Mtu" -ForegroundColor Green
Save this script as PowerShell script and run it. I added a check for argument -Q that will suppress the intermediate messages and only output the final MTU value. Call it with -Q to run it in quiet mode.
Wes Brown
TDS is a streaming protocol for data after the initial handshake and the like. TDS packets will automatically be split at the MTU frame limit minus protocol overhead and reassembled on the other end. If you are pushing lots of data around then increasing the TDS packet size can be helpful. If you can set jumbo frames on your network that will also help since you lose less data to protocol overhead, ACK latency and can match a larger TDS packet to the larger TCP payload size. You sacrifice performance on the low chatty end of things where you are working with a single return row or small number of rows as latency will be greater.
Again, as with anything like this testing is 100% needed.
Good write up!
SQLSoldier
Thanks Wes! And thanks for the explanation you provided.
Something for the Weekend - SQL Server Links 29/03/13 • John Sansom
[…] Network Packet Size: to Fiddle With or Not to Fiddle With – Robert L. Davis(Blog|Twitter) discusses. […]
Julia
Hi,
If you wanted to fiddle with network packet size in SQL, i.e. increase it to 8096, do you also need to consider it might overwhelm the mem-to-leave area in 32-bit on SQL version prior to SQL 2012?
SQLSoldier
Hi Julia. No, this does not change how anything is stored in memory. It won’t cause your external memory to be over-whelmed.
Lee Everest
Saw your tweet…no I did not attend any of your SQL rally stuff in Dallas.
SQLSoldier
My apologies then. I must have had you confused with someone else.
Weekly Links – 12/04/2013 | DB NewsFeed
[…] Robert Davis – Network Packet Size: to Fiddle With or Not to Fiddle With […]
Boris Hristov
Thank you for providing this insight! Indeed not many people know how to work with this server option!
SQLSoldier
You’re welcome! Glad you find it useful.
Chirag Shah
Robert,
Looking at your reply to Julia,
I thought if you increase the network packet size beyond default value of 4KB , it requires memory allocation outside buffer pool prior to SQL 2012.
http://blogs.msdn.com/b/sqlsakthi/archive/2011/11/10/large-network-packet-size-and-memory-pressure-in-sql-server.aspx
SQLSoldier
Hi Chirag. Thanks for posting the link. I’ll do some investigation into that. I am a little troubled by the blog post though for a couple of reasons:
1. He provided no actual evidence that it was the cause of the problem. He seems to be guessing and posting nothing that would validate if that was actually it.
2. He clearly does not fully understand the Mem-to-leave setting (-g). Mem-to-leave is reserved at startup time only. It is not enforced. So even setting a larger MTL would not ensure that 1700 connections later it is still available.
mike
This is an awesome post. More stuff I don’t know and need to learn. 🙂
SQLSoldier
Thanks! 🙂
Brent Willis
Robert,
If the max MTU is only 1500 would it make since to reduce the Network Packet Size so there is less packet splitting on the network or would this cause more overhead on the server?
Thanks, Brent
SQLSoldier
Only change the network packet size if you know for a fact it will improve performance. Test it with a smaller value and see if you see improvements. I am highly doubtful you will see any, but testing is never a wrong thing to do.
VC
I have seen installers using SQLCmd with -a 18000 parameter not running into issues with Out-of-the-box SQLServer installations at around 80% of the time. Only the highly secure IT environments are not letting the installer successfully complete. Even the -a 4096 failed. Only when we finally removed the -a 18000 parameter completely, the installer carrying the sqlcmd line succeeds. am dealing with SQLServer 2008 R2
I don’t understand the above observations as to why
1) -a 18000 works for some
2) -a 4096 parameter fails for some where removing the parameter entirely works
Any light that you can share on the above would be appreciated ?
SQLSoldier
Some firewalls and IPsec (IP security) can be configured to block packets that contain certain text patterns.
sql_handle
Hello!
Just stumbled across this tonight.
The KBa is for SQL Server 2005, 2008 and it mentions that packet sizes larger than 8060 bytes cause an allocation from a region that uses a size of 384 mb :-). I think that’s a roundabout way of saying that the allocation comes from memtoleave on 32 bit systems.
http://support.microsoft.com/kb/903002
SQL Server 2012 documentation still refers to this KBa, and still references the 8060 bytes max as best practice.
http://technet.microsoft.com/en-us/library/bb402885.aspx
However, I think that on a 64 bit system it’s likely a moot point, and increased packet size when warranted by workload and accommodated by the network is likely beneficial with little drawback due to SQL Server or Windows memory management.
SQLSoldier
Great info! Thanks for sharing. I agree with your assessment about 64 bit SQL.
Size Matters: 2 Products to Accelerate SQL Traffic | SQLSoldier
[…] talked previously about network pipelines and how to get the most out of a fat network in the post Network Packet Size: to Fiddle With or Not to Fiddle With. If you need to move large amounts of data and have a network infrastructure that can support a […]
What does Microsoft mean by low / moderate / high / very high / extremely high Azure network bandwidth (part 2.4) | Viorel Iftode
[…] https://ask.wireshark.org/questions/24699/tcp-packet-length-was-much-greater-than-mtu https://sqlsoldier.net/wp/sqlserver/networkpacketsizetofiddlewithornottofiddlewith https://en.wikipedia.org/wiki/Large_segment_offload […]
SQL Server Instance Configuration Settings | Michellea David, Sr. DBA
[…] Network – I have never messed with these properties. SQL Soldier To fiddle with or not to fiddle with […]