RE: Serious intermittent performance issues

One Orange Chip

Points: 26778

December 20, 2013 at 8:36 am

GilaMonster (12/20/2013)

william.rees.howells (12/20/2013)
Well, if I use my load testing tool and load it up with about 20-30 concurrent queries running this SP then I can reliably recreate a load of RESOURCE_SEMAPHORE waits. These wait types indicate that this SP is chewing up the memory.
Not necesssarily. Resource Semaphore is about query workspace memory, in this case probably the space the optimizer needs to compile. The waits indicate that queries want workspace memory and aren't getting it, that can be caused by having lots and lots of concurrent queries wanting memory grants

One correction here, RESOURCE_SEMPHORE is execution workspace memory only, associated with hash, sort, bulk copy, or index creation operations. Compile memory is a completely separate resource and would trigger RESOURCE_SEMAPHORE_QUERY_COMPILE waits if compile memory resulted in pressure on one of the three internal gateways for compile memory. For a 32-bit instance the small gateway threshold is fixed at 250,000 bytes, and can support four times the number of CPUs for concurrent compilations before a new compile would have to wait. The medium and big gateways have dynamic thresholds that adjust based on current compile memory usage by the workload.

If the memory is being chewed up then am I right in saying that SQL Server will start ditching cached plans from the plan cache as it starts running out of memory. If this is correct then would this in turn cause all of these Compilations?
Maybe, maybe not. Workspace memory and plan cache aren't the same thing.

Gail is correct that workspace memory and plan cache aren't the same thing, but excessive workspace memory grants occurring concurrently would reserve pages from the buffer pool and could result in internal cache store pressure that clears plans during external clock sweeps of the cache entries. You would see this in the performance counters for the instance by a drop in Free Pages, associated with an increase in Reserved Pages/Granted Workspace Memory and then shortly after, a drop in Database Pages (data cache) and Stolen Pages (plan cache/other stores). You can track the plan cache activity with the SP:CacheHit, SP:CacheInsert, SP:CacheMiss, SP:CacheRemove, and SP:Recompile events, and the SP:CacheRemove event would let you know why it was being removed for further troubleshooting.

I've seen this behavior in other workloads where triggers were present, so one question I'd have is are there triggers associated with either of the object_id's? If you have a plan invalidation for an object, it would compile the trigger and if the procedure is long running, the OBJECT compile lock becomes the bottleneck.