The network packet size can be changed in SQL server 2016 using the T-SQL and GUI methods.

Here at Bobcares, we have seen several such SQL related queries as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at how to change the network packet size.

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.

Change Existing Network Packet Size SQL server 2016 using T-SQL

Here are the steps our Support Engineers provide to our customers to change the 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

Changing Network Packet Size using GUI

We can also check or change the network packet size using the GUI.

• First, connect to the SQL Server instance in SQL Server Management Studio (SSMS).
• Next, right-click on the SQL Server Instance in SSMS and choose properties.
• Now the Server Properties window will appear to make server-level changes.
• Then click on the “Advanced” tab from the left side pane. You will be able to see the value of the network packet size.
• 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 changes 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 any assistance with SQL queries? – We’ll help you]

Conclusion

Today, we saw how our Support Engineers change the network packet size SQL server 2016 using the T-SQL and GUI method.