patternMinor
Will matching SQL Server network packet size to mtu increase performance
Viewed 0 times
packetmtusqlsizewillincreaseperformanceservernetworkmatching
Problem
We have recently upgraded our production facility to sql server 2017, and moved to clusterless availability groups. There is a primary, a secondary on site, and a remote secondary. We were experiencing periodic interruptions to synchronizations with the remote secondary. The bandwidth is low at 6G, and sql traffic competes with all other traffic. The good thing is that the AG would "catch up" after 5-15 minutes. In looking into whether there was anything I could do to improve the situation, I discovered experimentally that the network MTU was 1400, and sql's network packet size was set to the default at 4092. As an experiment I set the packet size to 1400 to match the MTU. It has been days since we had an alert about the AG, so it "seems" to have help.
My question is whether this was the right thing to do? I have read many times not to change the network packet size unless MS advises you too, and to never set it less than the default of 4096. And yet...it seems to have helped. So I am looking for input from more experienced folks in a similar situation.
My question is whether this was the right thing to do? I have read many times not to change the network packet size unless MS advises you too, and to never set it less than the default of 4096. And yet...it seems to have helped. So I am looking for input from more experienced folks in a similar situation.
Solution
TLDR: set it lower if it helps you, monitor your packet sizes to see if you are sending more packets than you need.
The MTU controls how large each network segment can be on all the points between each server, think traceroute hops - the MTU controls how large the segments are between 2 hops roughly. The smaller the MTU on a particular segment the more likely that the packet will be sent over more than one segment. This is normally fine unless you have a segment that is particularly busy.
The packet size controls how much data can be sent per TDS packet in sql. Each TDS packet has a header so some additional overhead. The smaller the packet size, the more packets you need to send your data with this caveat:
If you only call stored procs called “a” with no parameters and the response is only ever a stored proc status code then you could set max packet size to something tiny like 50. If you had very large requests (massive multi hundred line select statements) and lots of rows in the response then you would want as big a packet size as possible to avoid the overhead of more packet headers.
When you have a high error connection then TCP gets in the way and sends retransmits which further clog up the network and when packets go missing then everything gets held up until the missing ones sent on, so a high error rate with lots of retransmits is normally really bad for performance. Having a smaller packet size, results in more packets which results in more likelihood of lost packets which in turn means more retransmits and delays waiting for lost bits.
So is a smaller packet size bad? Normally but it really depends on your network.
I would do two things, first grab Microsoft Message Analyzer ( https://www.microsoft.com/en-gb/download/details.aspx?id=44226) and trace on each server and just look for TCP retransmits, if you have lots of these then you have a high error rate which would suggest you need to send less packets so a higher packet size.
In message analyzer you can add a column to show TDS PacketSize so if you have max packet size set to 1000 and you see lots of packets with a size of 1000 then lots of packets with size of 1 then the ideal is probably 1001 or 1002.
The second thing is look at something like tds nitro which compresses TDS so you can end up with less packets for your data which will likely help in high latency high error connections (http://nitrosphere.com/nitroaccelerator/)
Ed
The MTU controls how large each network segment can be on all the points between each server, think traceroute hops - the MTU controls how large the segments are between 2 hops roughly. The smaller the MTU on a particular segment the more likely that the packet will be sent over more than one segment. This is normally fine unless you have a segment that is particularly busy.
The packet size controls how much data can be sent per TDS packet in sql. Each TDS packet has a header so some additional overhead. The smaller the packet size, the more packets you need to send your data with this caveat:
- If the packets you want to send are always smaller than the max packet size then you could set max packet size to like 16k and it would make no difference because you can fit within one packet.
If you only call stored procs called “a” with no parameters and the response is only ever a stored proc status code then you could set max packet size to something tiny like 50. If you had very large requests (massive multi hundred line select statements) and lots of rows in the response then you would want as big a packet size as possible to avoid the overhead of more packet headers.
When you have a high error connection then TCP gets in the way and sends retransmits which further clog up the network and when packets go missing then everything gets held up until the missing ones sent on, so a high error rate with lots of retransmits is normally really bad for performance. Having a smaller packet size, results in more packets which results in more likelihood of lost packets which in turn means more retransmits and delays waiting for lost bits.
So is a smaller packet size bad? Normally but it really depends on your network.
I would do two things, first grab Microsoft Message Analyzer ( https://www.microsoft.com/en-gb/download/details.aspx?id=44226) and trace on each server and just look for TCP retransmits, if you have lots of these then you have a high error rate which would suggest you need to send less packets so a higher packet size.
In message analyzer you can add a column to show TDS PacketSize so if you have max packet size set to 1000 and you see lots of packets with a size of 1000 then lots of packets with size of 1 then the ideal is probably 1001 or 1002.
The second thing is look at something like tds nitro which compresses TDS so you can end up with less packets for your data which will likely help in high latency high error connections (http://nitrosphere.com/nitroaccelerator/)
Ed
Context
StackExchange Database Administrators Q#208133, answer score: 4
Revisions (0)
No revisions yet.