3

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.

  • 1
    Interesting question .. read this blog post by (late) Robert Davis - sqlsoldier.net/wp/sqlserver/… – Kin Shah May 29 '18 at 16:04
  • Thanks, I did see this post which is where I learned how to experimentally determine MTU. And it gave me the impression that setting packet size to equal MTU might be a good idea. But then I read something like nitrosphere.com/wp-content/uploads/2016/08/… which says not to set below 4096. So not being knowledgeable about TDS/MTU interaction, I was looking real world experience. Maybe it just comes down to trying it out and seeing the results. – JohnH May 29 '18 at 16:43
  • That is written by same person (late) Robert Davis and @kennethfisher – Kin Shah May 29 '18 at 17:39
  • Unfortunately Robert is the one who wrote that part of that post. That said, he did say he wouldn't recommend decreasing the TDS below 4096 even if the MTU is lower than that. Of course he also said that frequently the cost of breaking the (TDS) packets up and re-assembling them on the other end can be more than the gain of the larger packet. I'm just not sure which takes precedence here. – Kenneth Fisher May 29 '18 at 18:56
  • You might consider moving your question over to SO in hopes of finding a networking expert. – Kenneth Fisher May 29 '18 at 19:23
3

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 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

  • Thanks Ed. I will have to carve out some time to investigate MMA. And as it turns out we had several episodes of the AG not syncing last night, so the packet size was not a magic bullet. I will work with our network guy in that region to see if there is another approach. – JohnH May 30 '18 at 12:55
1

AGs that do not use a cluster (WSFC or Pacemaker) is NOT an availability configuration. You need to use a cluster for availability.

That said, you need to sort out your networking stuff. Poor connectivity = bad. Unless you have a bigger pipe, it won't fix things. By default, network stuff is compressed with async. So that helps, but at the cost of CPU. You know your problem.

  • 1
    Not sure why this was downvoted. It's true. As Ed points out, compression can help and async does that by default. You don't need something else. The problem is bandwidth overall, and that needs to be fixed. Plus, if you want this for availability, you should use a cluster. The ability to configure an AG without a cluster was put in for read-scale and can potentially also be used for migrations. – Allan Hirt May 29 '18 at 19:43
  • Not sure why it would be a down vote either, or who did it. At any rate, just a comment that sometimes the terminology gets in the way. Our environment is more accurately described as a read-scale availability group according to MS docs.microsoft.com/en-us/sql/database-engine/… . We understand its limitations, but it fits our needs to offload reporting and non-production activity. We just call it our availability group, so sometimes it slips into my typing. – JohnH May 30 '18 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.