william.rees.howells (12/20/2013)
Ok so that would indicate that those stored procedures with schema lock waits are trying to modify the schema in some way? This sounds strange because the SPs that are waiting for those locks do not make any schema modifications at all. They are just simple select statements. Does this sound right to you?
Doesn't sound right, but then the wait resources weren't tables, so could be something else going on. I don't know what those wait resources are.
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 optimiser 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
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.
You never answered my question about why AWE wasn't enabled and if /3GB was on. If it isn't on, then SQL is limited to 2GB of memory, which may well be a large portion of the problem.
Have a read through the troubleshooting book, especially chapter 1. That has some counters that you may want to start monitoring so that you have an idea what's normal so that when the problem happens again you can see immediately what's not normal. I'd add some SQL memory-related counters, especially around data cache, plan cache and memory grants to Jonathan's list in this case.
I'd also suggest next time it happens, get a lot more info on memory, CPU, waits before failing over. I think we're going to need a lot more info.
And push for that 64-bit move. Hopefully to a server with a lot more memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability