How to select the optimal setting for SQL Server Network Packet Size

Question

Hi All,

I recently ran into some connection problems with a SQL Server 2017 instance and the problem turned out to be Network Packet Size.

The Packet Size setting was initially set to 16384. By dropping it down to 8192, the problem was solved.

The error we were getting was 08501.

Although the problem has been resolved, this made me wonder:

1. How do we determine the optimal setting of SQL Server Network Packet Size?
2. How does the initial installation of SQL Server choose the default setting?
3. What are the advantages/disadvantages of choosing a fixed TCP/IP port vs Dynamic Ports?
4. Why might I want to change the default port value of 1433?

Thanks for your help!

--Dan

Answer 1

The default value for this setting is 4096. If you had it at 16384, someone have been playing around.

I recall that in some case I changed the network packet size, and I gained performance that way. But this was very long ago, probably SQL 6.5 or even older. And maybe the code I had written could better have been improved any other way.

Anyway, if you want to find what is the optimal value for you, you will need to run your benchmarks. I would not be surprised if you find that 4096 or 8192 makes very little difference. Or just reset the value to 4096 and be done with it.

What are the advantages/disadvantages of choosing a fixed TCP/IP port vs Dynamic Ports?

Some people disable the Broswer service to reduce the service area. In this case, you may prefer fixed ports, so that they don't move. You could also have users that connect is some funny way, so that they cannot use the Browser service. (Some my colleagues says this apply to them.)

Why might I want to change the default port value of 1433?

Some people say that you should do this as a security measure to stop evil hackers from trying to sneak into your server. But this is only security by obscurity, and a very thin layer of obscurity. Scanning 65000 ports to see if there an SQL Server instance on them does not take that much time.

Answer 2

Hi DJAdan-4490,
Did the answer help you?

How do we determine the optimal setting of SQL Server Network Packet Size?

In addition, unless you are sure that it will improve performance, it is recommended not to change the packet size. For most applications, the default packet size is the best. Please refer to this support article about the various issues that you need to be aware of when changing the network packet size are documented.

Why might I want to change the default port value of 1433?

Port 1433 is the known standard for SQL Server, some organizations specify that the SQL Server port number should be changed to enhance security. This might be helpful in some environments. However, the TCP/IP architecture permits a port scanner to query for open ports, so changing the port number is not considered a robust security measure. Please refer to this doc which might help.
Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you Amelia and Erland.

I could see no compelling reason to dink with either of these settings, but since I wasn't the original owner of the server, I wasn't sure what the legacy thought-process was.

For safety sake, I lowered it by half (16384 --> 8192) ... but it sounds like 4096 would have made no difference.

Thanks again.

--Dan