A question about "network packet size" to all DBA experts in the crowd

How can significantly increasing network packet size in the connection string have a positive impact on web performance when the MTU across the network is the default 1500?

When deploying SolarWinds HCO for an enterprise client we stumbled upon sporadic slowness of the web GUI with no obvious pattern.
After heaps of troubleshooting with a DBA and network expert, adding more pollers, additional web servers behind a load balancer, leaving only the bare minimum on the MPE,
the only solution that had a consistent results is setting the packet size in the application connection string to a large number (32,768).

Now we are trying to figure out why that works as we feel like this is a band-aid solution that does not address the source of the problem.

All guesses and speculations are welcomed, I'd love to hear your thoughts!

Find more posts tagged with

Performance

sql

HCO

web

Accepted answers

All comments

Daran

I think that number relates to the amount of data SW trties to get from the back-end databse in one go and has nothing to do with the MTU default packet size of the network.

HerrDoktor

With enterprise customers I have encountered security solutions that were scanning the Database Server causing high load during the scans, leading to slowness in the GUI

LevB

Hi Daran, thank you for your reply!

I would assume so, it would only make sense that it's something application related and not network related.

I have yet to figure out what to check as there isn't a lot of information available on how SQL server actually uses this configuration.

LevB

Hi HerrDoktor, thank you for your reply!

That is indeed one of the things we made sure to check. The security team excluded all the drives that are used by the SQL server from antivirus scans.

HerrDoktor

It’s not only limited to Antivirus scanning. In the case that I was thinking of, they had SPLUNK do all kinds of queries to the database server. So it was adding a lot of pressure with those non Solarwinds related queries

LevB

Oh, understood. In our case the Sql sever was created and is being used for SolarWinds only. Thank you for the clarification

kekline

@Daran is correct. Network packet size, at least for SQL Server, represents the amount of data sent between the database and the endpoint within a single TCP/IP packet. It's one of several tunable configuration settings in SQL Server that should almost never be altered. Changing it will almost certainly make no significant difference in a web GUI. And if you ever do change it, be sure to test extensively. Increasing the size probably won't hurt performance, but it also is very unlikely to help except in a small handful of use-cases.

As an FYI, the use-cases where I've seen it make a big difference are when moving massive files around directly from within the database instance to a non-database target. For example, backing up a multi-TB database can potentially get faster with a tuned network packet size of 64Kb and enabling jumbo frames. But even then, it's not a guaranteed improvement.

Hope that helps! Cheers,

-Kev

kekline

Absolutely and reliably true! Make sure your security tools do not scan MDF, NDF, and LDF files! It'll crush performance every single time it runs.

By the same token, exclude your database tools from storage and SAN deduplication routines. They too will make performance worse.

LevB

Hi Kev, thanks a lot for the reply and the explanation! much appreciated.

In our case changing the packet size seems to provide an immediate improvement to the performance of the web GUI. It can be felt by a very slight increase in load times overall and the more relevant but harder to measure impact is that we experience the phenomenon of pages loading for up to two minutes significantly less or not at all.

The reason I resorted to trying to change that configuration is the fact that the web pages freezing occurs seemingly at random and not at consistent times, as well as effecting random users (one user can have a session stuck while another user's session is fine). We tried just about anything else to solve this issue and the packet size had the most consistent results over time.

That being said I do agree with you, I do not think this is what this setting is intended for and we are striving to continue troubleshooting and finding a more conventional solution.

Thanks again!

LevB

I will have to check about the deduplication routines, I am unfamiliar if it's something that the client has configured. Thanks for the tip!