Database Reference
In-Depth Information
Blocking is not the only reason why this situation could occur. It is also possible to reach the limit of worker
threads in systems with heavy concurrent workload from a large number of users.
As usual, you need to find the root-cause of the problem. While it is possible to increase the
Maximum Worker
Thread
number in the SQL Server configuration, this may or may not help. For example, in the blocking scenario
described above, there is a good chance that newly created workers will be blocked in the same way as existing ones.
It is better to investigate the root-cause of the blocking problem and address it instead.
You can check a blocking condition and locate the blocking session by analyzing the results of the
sys.dm_os_waiting_tasks
or
sys.dm_exec_requests
views. Listing 27-10 demonstrates the first approach. Keep in
mind that the
sys.dm_exec_requests
view does not show tasks that do not have workers assigned waiting with the
THREADPOOL
wait type.
Listing 27-10.
Using
sys.dm_os_waiting_tasks
select
wt.session_id
,wt.wait_type
,wt.wait_duration_ms
,wt.blocking_session_id
,wt.resource_description
from
sys.dm_os_waiting_tasks wt with (nolock)
order by
wt.wait_duration_ms desc
option (recompile)
As you can see in Figure
27-9
, the ID of the blocking session is 51.
Figure 27-9.
Sys.dm_os_waiting_tasks
result
