Microsoft SQL Server Default Settings for Installation and Configured Values leave quite a bit of performance on the table.
The default the packet size for OLE DB connections is 0 ! The default packet size on SQL Server 97-2017 is 4096
This series will help you dramatically increase your Servers Performance. Increases in the range of 70-320% is easily achievable with this guide.
The benefits will be felt most by companies with large databases or companies that import / export spreadsheets on a regular basis.
If images and datasheets are stored in the database you will also benefit from tuning this setting
We Recommend Performing a FULL IMAGE backup and a DATABASE backup BEFORE making any changes to your production server.
This is an IT series article. If you do not know how to perform the steps below You should NOT be attempting this
There is an alternative method available at the bottom of the page
- Gigabit Switch (on all ports)
- Server with Gigabit Ports
- ALL workstations on network need gigabit lan adapters
- Cat6 Cable (you may get away with 5e to some extent)
- Microsoft SQL Server
- Start with your switch. If you have a smart switch and are positive all workstations have gigabit lan adapters disable any energy saving feature. If your switch allows prioritize 1000 or manually set it to 1000.
- Set your server Lan Adapter to 1000 in Lan Adapter Properties > Advanced Configuration (NOT AUTO)
- Set your workstation Lan Adapter to 1000 in Lan Adapter Properties > Advanced Configuration (NOT AUTO)
SQL Server Properties
Open SSMS (SQL Server Management Studio)
In SSMS Object Explorer
1) Right Click the Server
2) Click Properties
3) Click Advanced in the Server Properties Window
4) Locate Network Pack Size and Edit the Property
Change the setting using Increments of 4096 (ie 4096, 8192, 12288, 16384, etc)
Note : The limitation for encrypted connections is 16,383 bytes.
Do not exceed this value if your connection string includes Encryption=Yes;
Restart the SQL Server
1) Right Click the Server in Object Explorer
2) Click Restart
Warning: We are NOT Finished Yet. Packet Loss Testing is a MUST.
Microsoft Developer Network: How to: Configure Packet Size (SQL Server Management Studio)
Microsoft Docs: Configure the network packet size Server Configuration Option
Update: Microsoft has recommended not changing the packet size on SQL Server directly on the server
We have an alternative method to this solution in Increase SQL Network Performance Part 2
Decide which is best for you.