Async_network_io and Preemptive_os_waitforsingleobject wait events

Background environment:

SQL Server 2005 or higherSelect * from a table, the data volume of the table is approximately 300,000 rows, the execution of the statement by observing sys.dm_exec_requests in the wait_type column found is Async_network_io wait, A preemptive_os_waitforsingleobject wait was found on the local MSSQL2012 test, and only async_network_io wait was found when the local 2008r2 was tested. You can use the following statement to query the waiting time for the related wait:

123456789

select   session_id,   db_name (database_id)   as   "db_name" ,   status,   wait_type,   wait_time,   text from  sys.dm_exec_requests cross apply Sys.dm_ Exec_sql_text (sql_handle) where   Session_id>50

about network protocols:When the shared memory protocol is on, the shared memory protocol is preferred using the native name login, so this protocol applies only to local connections. You can get network protocol usage for all non-system sessions by using the following SQL:

12345678910

select session_id, most_recent_session_id, net_transport, auth_scheme, client_net_address, client_tcp_port, local_net_address, local_tcp_portfromsys.dm_exec_connections

From the query results can be roughly inferred that the local ssms as a client if using the TCP/IP protocol is also to go to the network card, and the execution results show the protocol used by the login and login authentication method and the port number used. A connection using the shared memory protocol does not acquire data through socket communication, but rather reads from shared memory directly through the system bus.

About the Wait event:

Async_network_io

This wait type is where the SQL Server have sent some data to a client through TDS and are waiting for the client T O acknowledge that's have consumed the data, and can also show up with transaction replication if the Log Reader Agent job Is running slowly for some reason.

This wait type indicates that SQL Server is transmitting the requested data to the client through TDs, or that the log read agent for transactional replication is slow to operate for some reason.

(Books Online description: "occurs on network writes when the task is blocked behind the network. Verify the client is processing data from the server. ")

(Explanation of Books Online: The client is receiving data from the server when the task appears because it is blocked from the network)

Other information:

This wait type was never indicative of a problem with SQL Server, and the vast majority of the time it's nothing to do wit h the network either (it's very common to see advice stating so this is a network issue). A simple test for network issues are to test the ping time between the SQL Server and the Client/application/web Server, an D If the ping time is close to the average wait time and then the wait is because of the network (which may just be the Norma L Network latency, not necessarily a problem).

This wait type represents a problem that is not SQL Server, and in most cases is not related to network problems (many times it is considered a network problem), a simple test method is to ping the server from the client, if the latency is close to sys.dm_exec_requests in wait The average value of the _time proves that it is indeed network-related (often just a normal network delay, not a network failure).

There is usually nothing so you can does with your SQL Server code that would affect this wait type. There is a few causes of this on the client side, including:

• The client code is doing and known as RBAR (Row-by-agonizing-row), where only one Row at a time was pulled from the Res Ults and processed, instead of caching all the results and then immediately replying to SQL Server and proceeding to Proce SS the cached rows.
• The client code is running on a server, the have performance issues, and so the client code is running slowly.
• The client code is running on a VM in a host that's configured incorrectly or overloaded such that the VMS doesn ' t get to Run properly (i.e. slowly or coscheduling issues).

There is generally no need to make any changes to the SQL code for this wait event, which is basically caused by the client, for example:

。 The client code processes the dataset using the Rbar method, each time taking only one data from the result Chira, rather than getting it all done.

。 The server on which the client is located has some performance issues that cause the client to operate slowly.

。 The client is running on a virtual machine that is misconfigured or overloaded, and the server itself is a problem.

On the SQL Server side, the only possibility I know of a for causing the are using MARS (multiple Active Result sets) with L Arge result sets.

Can demonstrate this wait type easily by running a query with a large result set through SSMS on the SQL Server itself , with no network involved.

On the database server side, the only possible cause I know of is the use of a large result set of Mars. (actually because the result set is too large)

You can easily verify that the wait event occurs by running a query that gets a large result set by using the native name login on the database server.

Some other things you can try:

• Look for incorrect NIC settings (e.g. TCP chimney offload enabled) with the help of your Network/system administrator. Whether Some settings should is enabled or not depends on the underlying OS version. See this post for some more details.
• Consider increasing the TDS packet size (carefully) –see this post for more details.

a few other attempts: . If there are other network settings errors, contact your network administrator to modify some of the network parameters in the registry, some parameters in some OS version should be enabled reference here (see above hyperlink). . consider increasing the size of the TDS package (be cautious), refer to here (see above hyperlink). Preemptive_os_waitforsingleobject

Description:

This wait type was when a thread was calling the Windows WaitForSingleObject function for synchronization with An external client process, which is communicating using this object.

(Books Online description: N/A--indicates that books are not described online)

This wait event indicates that a thread is synchronizing data from an object to an external client process, so this wait occurs. In general, this type of wait appears in SQL Server 2012 and later versions, previously replaced with async_network_io .

Other information:

This wait type was commonly seen in conjunction (simultaneous appearance) with async_network_io, depending on the NETWORK Transpo RT used to communicate with the client, so to troubleshoot, follow the same steps as for async_network_io.

Note that if a thread calls out to Windows, the thread changes from non-preemptive (SQL Server controls the thread) to P Reemptive (Windows controls the thread) mode. The thread's state would be a listed as RUNNING, as SQL Server doesn ' t know what Windows are doing with the Threa D.

This wait event typically appears with the async_network_io wait event, depending on the type of network transport used by the connection, so The resolution step refers to the resolution of the async_network_io.

Note that when a connection thread is controlled from SQL Server (non-preemptive) to Windows controlled (preemptive), the state of the thread becomes running, at which point SQL Server does not know what Windows is doing with this thread.

For the difference between preemptive and non-preemptive, refer to the website blog for an introduction to how SQL OS differs from the Windows OS in how threads are handled.

Async_network_io and Preemptive_os_waitforsingleobject wait events