Tuesday, November 11, 2008

Performance Tuning SQL Server's Configuration Settings

The default network packet size for clients communicating with SQL Server is 4096 bytes. In most cases, this is the overall best setting. But in some special cases, this SQL Server configuration setting should be changed. If the client regularly transfers large amounts of text or image data to SQL Server, or performs large BCP or DTS operations, then increasing the default size may boost performance because the number of network packets is reduced. On the other hand, if your application only sends and receives small amounts of data, then a smaller packet size can boost responsiveness.

Keep in mind that client software can overrule this setting in SQL Server. Only try changing this setting if you are very familiar with network traffic analysis and have the ability to test the performance, before and after the change. [6.5, 7.0, 2000, 2005]

No comments: