SQL 2014 clear locks in memory without reboot?

Question

Hello, I'm noticing a bunch of RESOURCE_SEMAPHORE waits and when checking portions of Memory used most of it shows coming from Locks.

Is it possible to clear out these Locks in Memory without rebooting and is it safe or not recommended?

Thanks in advance.

Answer 1

Hi techreasearch7777777-7743,
Common causes for this wait type include:

• Missing indexes causing large sort or hash operations
• Out-of-date statistics causing incorrectly large cardinality estimates
• Large numbers of concurrent queries running that all require memory to run, or where many of the queries have incorrectly large memory grant requirements

You can use the sys.dm_exec_query_memory_grants DMV to see the size of granted and pending memory grants to identify queries that have or need incorrectly large memory grants and then tune these queries.

Please refer to How to fix RESOURCE_SEMAPHORE waits and Troubleshooting SQL Server RESOURCE_SEMAPHORE which might be helpful.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

That's what I was thinking also that RESOURCE_SEMAPHORE waits could come back even after reboot, thanks for the replies.