jeudi 26 mars 2015

How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types


We are trying to figure out root cause of slow running sql server queries hitting/fetching data from one of the database , size 300 GB, hosted on server with below configuration:



Windows server 2003 R2, SP2, Enterprise Edition, 16 GB RAM , 12 CPU'S 32 Bit


SQL server 2005, SP4, Enterprise Edition, 32 Bit.



We have already informed business on the upgrade to 64 bit which would take over a month.


But for the current issue, we are trying to gather the data if we can resolve the memory pressure or finally come to a conclusion to increase RAM.


Action Completed: Re-indexing and update stats are proper for this DB.


As shown below, we have been noticing the semaphore waittype for past 5 days, ran during the load hours:


waittype


Few info after below queries: size of buffer= 137272



SELECT SUM(virtual_memory_committed_kb)
FROM sys.dm_os_memory_clerks
WHERE type='MEMORYCLERK_SQLBUFFERPOOL'


and semaphore memory= 644024 per below query



SELECT SUM(total_memory_kb)
FROM sys.dm_exec_query_resource_semaphores


Below is some more info gathered from dm_exec_query_resource_semaphores and sys.dm_exec_query_memory_grants dmv's


dmvserror


So from above info gathered and per SP_Blitz data Resource semaphore seems to be the problem.


Is memory 'target_memory_kb' assigned for resource semaphore id's too low, as compared to 16 GB RAM available.


Note* per analysis on 8 hours run 'target_memory_kb' is always under 1 GB, compared to 16 GB available?


what could be the issue here and how to resolve, please suggest


Thanks





Aucun commentaire:

Enregistrer un commentaire