System Troubleshooting - Pro SQL Server Internals - page 531

For the next step, you can use the sys.dm_exec_sessions and sys.dm_exec_connections views to get
information about the blocking session, as shown in Listing 27-11. You can troubleshoot why the lock is held and/or
terminate the session with the KILL command if needed.

Listing 27-11. Getting information about a blocking session
select
ec.session_id
,s.login_time
,s.host_name
,s.program_name
,s.login_name
,s.original_login_name
,ec.connect_time
,qt.text as [SQL]
from
sys.dm_exec_connections ec with (nolock)
join sys.dm_exec_sessions s with (nolock) on
ec.session_id = s.session_id
cross apply
sys.dm_exec_sql_text(ec.most_recent_sql_handle) qt
where
ec.session_id = 51 -- session id of the blocking session
option (recompile)

■ Worker thread starvation may prevent any connections to the server. in that case, you need to use Dedicated
Admin Connection (DAC) for troubleshooting. We will discuss DaC later in this chapter.

Note

It is worth mentioning that even though increasing the Maximum Worker Thread setting does not necessarily
solve the problem, it is always worth upgrading to a 64-bit version of Windows and SQL Server. A 64-bit version of
SQL Server has more worker threads available by default, and it can utilize more memory for query grants and other
components. It reduces memory grant waits, makes SQL Server more efficient and, therefore, allows tasks to complete
execution and frees up workers faster.
Workers, however, consume memory, which reduces the amount of memory available to other SQL Server
components. This is not usually an issue unless SQL Server is running on a server with very little physical memory
available. You should consider adding more memory to the server if this is the case. After all, it is a cheap solution
nowadays.

ASYNC_NETWORK_IO Waits
The ASYNC_NETWORK_IO wait type occurs when SQL Server generates data faster than the client application consumes
it. While this could be a sign of non-sufficient network throughput, in a large number of cases ASYNC_NETWORK_IO waits
are accumulated due to incorrect or inefficient client code.
One such example is reading an excessive amount of data from the server. The client application reads
unnecessary data or, perhaps, performs client-side filtering, which adds extra load and exceeds network throughput.
Another pattern includes reading and simultaneous processing of the data, as shown in Listing 27-12. The client
application consumes and processes rows one-by-one, keeping SqlDataReader open. Therefore, the worker waits for
the client to consume all rows generating the ASYNC_NETWORK_IO wait type.