SQL Server performance Tuning resources waiting network I/O

Original: SQL Server performance Tuning resource waiting network I/O

I. Overview

The main wait for network I/O is async_network_io, when SQL Server returns a data result set to the client, the result set is populated into the output cache (ouput cache), and the network layer begins to package the data in the output cache. Received by the client. If the client receives a packet that is slow and SQL Server has no place to store the new data results, the task enters the Async_network_io wait state.

　1. View Async_network_io from the instance level

Average time: 46366950.0/43014737.0=1.077ms, maximum wait time: ~40 seconds.

　2. Reproduce the Async_network_io wait

To demonstrate the async_network_io phenomenon, we need to output a large result set. When SQL Server memory is fully used, a large amount of data is populated into the cache, where SQL Server has no place to store the new data results and goes into a wait state.

-- query 100,000 data output to the client at a time SELECT TOP 100000 *  from  with (NOLOCK)

The following sessions are heard:

　　

Query 64 results using DBCC INPUTBUFFER are as follows:

　3. Analysis and Resolution

This waiting question highlights the following points:

(1) The client does not take the data in time, adjust the configuration of SQL Server generally is not a big help.

(2) The network layer may be the cause of the problem. Resolution: 1 is to reduce the amount of data output to the client. 2 is to increase the network Packe size of SQL Server, to some extent, optimize the performance of net transfer, but will increase the cost of memory (less than the recommended setting is less than 8kb).

The network Packe size is a relationship between the client and SQL Server to communicate with each packet. Network Packe size sets the packet stored in the connection category of the memory function component. The default is the 4KB setting, the input output cache will be placed in the buffer pool, if changed to 8KB or greater, the input output cache will be placed in the multi-page about memory can see SQL Server memory. Setting the network Packe size can be controlled by sp_configure. Client applications can override this value as configured in. NET as follows.

Data source= (local); Initial Catalog=adventureworks; " Integrated Security=sspi; Packet size=512

Demo to set net work Packe size to 6050 bytes

Use AdventureWorks2012;  GO  EXEC sp_configure ' show advanced options ', 1;  GO  RECONFIGURE ;  GO  EXEC sp_configure ' network packet size ', 6500 ;  GO  RECONFIGURE;      

Can also be configured through the interface

　　

(3) application-side performance issues can also cause async_network_io in SQL Server to wait.

When the network layer of SQL Server packages the result set to the client, it waits until the client acknowledges receipt before the next package is sent.

(4) Distributed lock

If you see async_network_io for a long time and then cause a blockage inside SQL Server, and the wait lasts for long, you should suspect a distributed deadlock.

Summary: When encountering Async_network_io waits, you need to check the application's own health, and also to check if it is necessary for the application to request such a large result set to SQL Server return, generally speaking, SQL Server itself is not a problem.

Two. Other network I/O waits

There are several other net_waitfor_packet,proxy_network_io,external_script_network_iof here.
2.1 Net_waitfor_packet: The explanation in MSDN is that the connection is waiting for network packets to occur during network read.

The actual level waits as shown:
　　　　
　　2.2 Back two proxy_network_io,external_script_network_iof. There is no data in the production environment. No explanation was found on MSDN. Can only be explained by literal meaning.

SQL Server performance Tuning resources waiting network I/O