SQL SERVER – Identifying Query Generating Malicious Wait Type

Just another day in my life when I got involved in the SQL Server Performance Comprehensive Database Performance Health Check, where I ended up with a server with a high amount of malicious Wait Type. In this blog post, we will learn about how to fix the performance issue with the malicious Wait Type.

While I was out at the festival celebration, I received an urgent email from the client that they are seeing overall performance slowdown in their system and they need my immediate help. As they were already my previous client, they had a link to make the payment as well. Without checking with me about my availability they made the payment and wrote an email for the help.  In normal cases, I first like to discuss my availability before I accept the payment. However, as I have worked with the client, I knew their server very well and I also got friends with some of the DBAs there.

Malicious Wait Type

We started investigating their system for the slow performance and while running my SQL Server Wait Statistics Script, we figured out that the biggest wait type is THREADPOOL Wait Type and it is about 85% of the overall wait type. As I have worked many times before on the server which had similar problems, I was able to help them to resolve the issue very quickly.

The biggest issue which we were facing was to identify queries that were doing the malicious wait type. There is no DMV in SQL Server which will directly generate the wait type. We finally used the following trick to identify the queries which were generating the wait type.

I know that many SQL Server Experts would not like to use this method and they have their own methods to do the task. I am totally fine with any method you use to identify the bad wait type and query associated with it.

Identify the Session Creating Wait

Here is the first query which we ran to identify the session which was generating the bad wait and all the associated the wait with the query.

SELECT * FROM 
sys.dm_exec_session_wait_stats
WHERE session_id in 
(
SELECT session_id
FROM sys.dm_exec_session_wait_stats
WHERE wait_type LIKE '%THREADPOOL%') 

Identifying the Query From Session

Once we got the desired session_id which was generating the malicious wait type, we ran the following query to get the query from the session_id.

SELECT es.session_id, ib.event_info, status,
cpu_time, memory_usage, logical_reads, writes, row_count
total_elapsed_time, login_time, last_request_start_time, last_request_end_time
host_name, program_name, login_name, open_transaction_count
FROM sys.dm_exec_sessions AS es
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
WHERE es.session_id in (
SELECT session_id
FROM sys.dm_exec_session_wait_stats
WHERE wait_type LIKE '%THREADPOOL%') -- replace threadpool with other wait

I guess you can replace the wait type to any other wait type and get necessary query. Every time, I learn something new when I see new SQL Server and new problems.

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