5

On one of our production server, where SQL Server 2008R2 (Service Pack 2) is installed with 24 GB memory and 24 CPU cores grouped into two NUMA nodes, we are facing high counts for resource_semaphore waits WHEN we execute DBCC CheckDB for one of our database which is 125 GB in size.
Instance is up for last 6 months and working fine, but this start suddenly while no size change in database and no configuration changes performed.
While
Hard drives are performing up to the mark.
BUT
Strange thing i found is "ideal_memory_kb" values i.e. 5299767576 (5TB) for DBCC Check DB Session

select top 5 ideal_memory_kb,* from sys.dm_exec_query_memory_grants

Is it TempDB issue ?
Is there anything wrong on memory side?

3

There is nothing wrong! This is how DBCC CHECKDB works. You can read Jonathan's post explaining this behavior and a workaround if you're on the Enterprise Edition -- DBCC CHECKDB Execution Memory Grants – Not Quite What You Expect

The only strange thing is that usually this happens with servers with larger amount of RAM!

Here is also a really good article on Understanding SQL server memory grant

Memory grant uses "RESOURCE_SEMAHORE" wait type. If you see significant waits on this wait type, you may have an issue with big queries.

Perhaps, if you see a big IO Queue on your SAN, you have a lot of IO bound queries on top of your memory pressure. Check the PLE and Memory Grants Pending counters on this server. If you PLE is very low and you have lots of pending memory grants, your server might benefit from additional memory. Also look if you need to add any indexes (review missing indexes DMV) -- Are you using SQL's Missing Index DMVs?

  • Sorry DenisT, but RESOURCE_SEPHORE really creating problem for other queries request. In our case RAM is only 24 GB and workload must spill over to TempDB. Is there any possibility that problem is on wrong estimation of memory for DBCC CHECKDB process i.e. 5TB in my case and we have only 300 GB space on drive and it is unable to spill over workload to TempDB ? – aasim.abdullah May 21 '14 at 7:35
  • @aasim.abdullah, spill to TempDB happens when the estimated memory grant is smaller than the actual memory grant. It doesn't apply in your case. You should look into investigating if your server experiencing any memory pressure. – DenisT May 21 '14 at 13:46
0

Strange but issue was on SAN throughput side. Avg. Disk Queue Length values are found too high and informed to System Administrator, Corrected SAN problem, and as a result RESOURCE_SEMAPHORE high wait values issue resolved with same configurations and database size.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.