Have you ever thought how SQL Server controls the size of packets which are send over the network. There is a server configuration option network packet size in SQL Server. The value of this configuration are used to control the size of network packet in which data are send over the network. Here, i will describe about network packet size server configuration option and also how can we change its value.
Network Packet Size Server Configuration
Network Packets are the fixed-size chunks of data that transfer requests and results between clients and servers. The default value of network packet size is 4096 bytes which is 4KB. Microsoft suggests to not change this value until it is required or it will improve any performance because SQL Server performs best with default network packet size. There is a limitation for encrypted connection that the network packet size can not be more than 16,383 bytes.
Change Existing Network Packet Size using T-SQL
Let’s check the current value of this option on SQL Server Instance. Run below command to see the details. If you are getting Error 15123: This Configuration Option does not exist then you should first read attached article to fix this issue and then proceed with the solution given in this article.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'network packet size'
You can see the current config value is set to 4096 that means SQL Server will use network packet size of 4KB to send out data over the network. We can set minimum value to 512 byte and maximum value to 32767 byte.
If you want to change this value from 4KB to 8KB or any other user value, you can do that by running below T-SQL statement. Microsoft suggest to not change the default value although if you are facing any network related performance issue you can change this value as per your requirement. Make sure to do proper testing in lower life cycle before applying this change in to productions.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'network packet size', 8192 ;
GO
RECONFIGURE;
GO
See the below screenshot in which we have changed the value from 4KB to 8KB.
Changing Network Packet Size using GUI
We can check or change network packet size value using GUI as well. Let’s make this change using GUI so that you will be aware of both methods. I have already discussed T-SQL method in above section.
Connect to SQL Server Instance in SQL Server Management Studio (SSMS). Right click on SQL Server Instance in SSMS and choose properties. Server Properties window will appear to make server level changes. Click on “Advanced” tab from left side pane. You can see the value for network packet size is 8192 which we set in above steps.
Now revert this value to 4096 to its default value. Enter the value and hit the ok button to apply this change. This change will be in immediate effect and does not required any reboot or restart.
You can validate the changes by running sp_configure command or by using GUI method.
I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.
Read More:
Leave a Comment