Post reply

Memory Grants Pending

  • December 15, 2015 at 1:18 am

    #303291

    Hi all,

    I have problem with Memory Grants Pending value on one of the prod. servers. Usually it is around 90 (during pick hours). This instance is not the busiest one but I see a lot of RESOURCE_SEMAPHORE wait types. This is SQL 2008R2 SP2 (10.50.4321) Standard edition, Max memory: 65536 MB. Below is output from sys.dm_os_memory_clerks (top 10).

    Memory Clerk Type\SPA Memory Usage (MB)

    USERSTORE_SCHEMAMGR1540

    USERSTORE_OBJPERM1112

    MEMORYCLERK_SOSNODE1107

    MEMORYCLERK_SQLSTORENG376

    CACHESTORE_SQLCP276

    MEMORYCLERK_SQLGENERAL235

    CACHESTORE_OBJCP116

    OBJECTSTORE_LOCK_MANAGER113

    CACHESTORE_PHDR99

    MEMORYCLERK_SQLOPTIMIZER87

    Can someone tell me if this output is normal/ expected? And how to reduce the “Memory Grants Pending” value?

    Thanks

  • December 15, 2015 at 5:39 am

    #1846043

    Hi,

    You may have already done this but have you checked if there are any wait types related to memory pressures on the instance? I'd highly recommend using Brent Ozar's wait stats triaging script - it's quick and very informative. http://www.brentozar.com/responder/triage-wait-stats-in-sql-server/

  • December 15, 2015 at 5:57 am

    #1846050

    Yes, already done it. The two main are: CXPACKET and RESOURCE_SEMAPHORE on tempdb (both are apx. 60% of total wait time).

  • December 15, 2015 at 7:44 am

    #1846081

    OK, I'm not sure what the advice is for the resource semaphore wait type but for CXPACKET wait types, have you checked if your "Max Degree of Parallelism" is set at default? And what's the "Cost threshold for parallelism" set at? Both of those at default on a server with a large number of cores could lead to CXPACKET waits.

    This article gives advise on what to set your MAXDOP on: https://support.microsoft.com/en-us/kb/2806535

  • December 15, 2015 at 7:47 am

    #1846084

    Oh yes, also is your tempdb data files configured as per: https://support.microsoft.com/en-us/kb/2154845

  • December 15, 2015 at 7:52 am

    #1846087

    Thanks 😉 but CXPACKET is not a problem here - we have already decreased this wait time and we are fine with it. My main concern is output from sys.dm_os_memory_clerks (USERSTORE_SCHEMAMGR , USERSTORE_OBJPERM) and if it has something in common with Memory Grants Pending/ RESOURCE_SEMAPHORE.

  • December 16, 2015 at 3:27 am

    #1846264

    OK, have you tried what's suggested here: https://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/

Viewing 7 posts - 1 through 6 (of 6 total)

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