SQL server slow with wait_type RESOURCE_SEMAPHORE

SQL Server slow without any reason ? lot timeout complain? if wait_type = RESOURCE_SEMAPHORE , can try this

We have issue when users keep complain their query timeout, query can’t complete

We run some query in SSMS, result return very fast; The server with low CPU, no blocking, no long run query and active connection keep accumulate.

We can try this method and check

--watch for waiter_count

-- if waiter_count> 0, mean query need wait for memory. Normally we should expect this value =0

--resource_semaphore_id = 0 , large query

--resource_semaphore_id=1, small query

 

SELECT waiter_count,grantee_count,* FROM sys.dm_exec_query_resource_semaphores

--if waiter_count>0 all the time, check this

--what query have granted_memory_kb
select granted_memory_kb ,grant_TIME,SESSION_ID,resource_semaphore_id from
sys.dm_exec_query_memory_grants
order by 1 desc

Look for session_id with largest granted_memory,

Check what query is running with this session,

Dbcc inputbuffeR(session_id)

Remember capture hostname, loginame and how long the query already runs.

Sp_who2(session_id)

Good Article :
SQL wait_type RESOURCE_SEMAPHORE troubleshooting

Troubleshoot on timeout query

We have one request to trace on timeout issue on applicaition.
I setup profiler to trace which query has timeout issue


Error and Warnings
- Attention


Stored Procedures
- RPC: Starting
- SP: Starting
- SP: StmtStarting

TSQL
- SQL: BatchStarting
- SQL: StmtStarting


Setting sample table and data
Create table member3
(member_no int,
lastName varchar(20),
firstName varchar(20));
insert into member3 values (1,'tan','tanlast'),(100,'lee','leelast');

Setting timeout environment
begin tran
update member3
set lastname='me'
where member_no=1

Open a second Microsoft SQL Server Management Studio
In connect to server box, Click on Option and change Execution time-out from 0 (unlimited) to 2 seconds
Press connect



In Query Window, type this,

select * from member3
where member_no=100

After run for 2 second, SQL will give the below message

Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

From SQL Profiler, look for EventClass=Attention which highlight with red color
You can do this to find “Attention”

CTRL+F, In “Find what” column, keying “Attention”



After get the EventClass with “Attention”, look for the SPID. In this example, SPID for “Attention” = 58
Look backward for SPID=58, you will know the above query is face timeout



In a real production environment, lot of the query is running and we difficult to find the backward query. We can do in this way.

Save the trace log in trace file
Open the trace file that you save just now

Filter the SPID from File-> Properties -> Events Selection -> Column Filter -> SPID -> Equals -> Keying in 58 and press ok, ok




The SQL profiler will only show trace with SPID 58, which easy us to search which query cause timeout.