It is possible to modify the network packet size in SQL server 2016 using the T-SQL and GUI methods.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform SQL related tasks ans queries.
In this context, we shall look into the process to implement change in SQL server network packet size.

More about Network Packet Size Server Configuration?

Network Packets are the pieces of data that transfer requests and results between clients and servers.
By default, the value of the network packet is 4096 bytes which are 4KB. Moreover, Microsoft suggests not changing this value unless it is required or it may improve any performance. Because the SQL Server performs best with default network packet size.
Also, there is a limitation that the network packet size can not be more than 16,383 bytes for an encrypted connection.

How to modify an existing Network Packet Size SQL server 2016 using T-SQL?

Follow the following steps to modify he network packet size;
Execute the below command to check the current value of the network packet size;

EXEC sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE ;

GO

EXEC sp_configure ‘network packet size’

In case, the current config value is set to 4096 then SQL Server will use a network packet size of 4KB to send out data over the network. Here, we can set the minimum value to 512 byte and maximum value to 32767 bytes.

However, if you wish to change this value from 4KB to some other value then you can do it by running the below T-SQL statement;

EXEC sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE ;

GO

EXEC sp_configure ‘network packet size’, 8192 ;

GO

RECONFIGURE;

GO 

How to change Network Packet Size using GUI?

In order to change or check the network packet size using the GUI, follow the steps below;
i. First, connect to the SQL Server instance in SQL Server Management Studio (SSMS).
ii. Next, right-click on the SQL Server Instance in SSMS and choose properties.
iii. Now the Server Properties window will appear to make server-level changes.
iv. Then click on the "Advanced" tab from the left side pane. You will be able to see the value of the network packet size.
iv. Now you can change the value to any value as per the requirement. For that, enter the value and click on the OK button.

These modifications will take immediate effect and doesn't require any reboot or restart.
Finally, you can validate the changes by running the below command or you can also use the GUI method;

sp_configure

[Need further support with SQL queries? – We are available to help you today.]