SQL SERVER – Whitepaper – Optimizing SQL Server Network Performance

I have blogged before regarding how to Identify Application vs Network Performance Issues using SQL Server Dynamic Management Views (DMV). You do have some further diagnostics as well as optimizations you can perform to drill down into the network issue and address it.

Once you have used sys.dm_os_wait_stats to identify that the top wait_type is ASYNC_NETWORK_IO you know you have a problem either with the client side of the application or the network. To drill down further, you should check SQL Server sys.configurations for the network packet size. The network packet size is not actually affecting the network layer, but changes the size of the Tabular Data Stream (TDS) packets which are then sent to TCP/IP for transmission. TDS packet size can be set from 512 bytes up to 32KB and your assumption may be that the larger the packet size, the faster your data will move. The primary consideration in allocating the TDS packet size is memory utilization so you’ll want to consider this when setting it. Some people like to match the TDS packet size to the MTU, while others simply test different TDS packet settings until they reach a level that balances memory utilization with performance requirements.

While it is good to optimize your TDS packet size, you still may have network pipe issues. To get more detail on how to query and optimize TDS packet sizing as well as addressing SQL Server network performance, check out DBA Tactics for Optimizing SQL Server Network Performance.

Let me know what you think of this blog post and if you want me to create a SQL in Sixty Seconds post for this video. If you have a similar script that you may find will be helpful to users, please do share it with me and I will post it on the blog with due credit to you.

You can always reach out to me via Twitter here.

Reference: Pinal Dave (https://blog.sqlauthority.com)