Lots of RESOURCE_SEMAPHORE waits with almost no memory granted

Question

Lots of RESOURCE_SEMAPHORE waits with almost no memory granted

v.bartosh

SSC-Addicted

Points: 407
More actions
September 30, 2016 at 7:06 am

#330605

We are running SQL Server 2014 (12.0.2000) in our software testing environment, and started encountering this problem recently: total/target memory becomes low, even though there is no external memory pressure
I've ran this query:
select
sum (max_target_memory_kb)/1024 max_target_memory_mb
,sum (target_memory_kb)/1024 target_memory_mb
,sum (total_memory_kb)/1024 total_memory_mb
,sum (available_memory_kb)/1024 available_memory_mb
,sum (granted_memory_kb)/1024 granted_memory_mb
,sum (grantee_count) grantee_count
,sum (waiter_count) waiter_count
from sys.dm_exec_query_resource_semaphores
and here is what it shows:
max_target_memory_mb target_memory_mb total_memory_mb available_memory_mb granted_memory_mb grantee_count waiter_count
59724 7862 7862 7845 17 4 21
Maximum server memory limit is set at 40000Mb, and the task manager shows, that sqlservr.exe owns that amount. It is a dedicated SQL Server, so no other memory consumung software runs there. So, I see no reason for low total/target memory amounts.
What is even more weird, having 8Gb memory available as shown above, there are lots of tasks waiting for RESOURCE_SEMAPHORE for ages (like hours... days...), even with negligible requested anmounts, like 1-10Mb. And only restarting the server helps... until the next time.
Any thoughts?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

There are a few reasons for this. But sorry, I'm not going to waste my time helping you decipher it while you are on RTM of SQL 2014 and thus TWO AND A HALF YEARS behind on patching. You are missing a TREMENDOUS number of bug fixes, including at least one that is directly related to your wait type.

http://sqlserverbuilds.blogspot.com/

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

v.bartosh SSC-Addicted Points: 407 More actions · Answer 2

So, is this a bug in SQL Server itself?

OK, we'll update and see if the problem persists.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

v.bartosh (9/30/2016)
So, is this a bug in SQL Server itself?
OK, we'll update and see if the problem persists.

I am not saying what you are seeing is related to a bug. I am saying that you are completely out of date in your patching. So what you are seeing could very well be something that has been patched since your RTM build.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service