Monday, March 05, 2007

SQLServer 2000: Memory Grants Pending

I tell you -

Finding out anything about memory grants pending is virtually impossible. Yea, there are a few references to it out there in the google world, but most just refer to the fact that it's not much more than the title implies. SQLServer has memory grants for processes queued up.

At surface value, you would expect it would mean 'get more memory', which is what most of the links imply.

As it turns out - you need to dig a little further. We noticed that some applications we run were slowing down our whole server due to memory grants pending. So add memory, of course....

Nope - throw on a trace and look for deadlocks - sure enough, the real culprit was some out of control application code that was causing deadlocks in a cascading manner, which in turn caused the memory grants queue to jump way up and slow SQLServer to a crawl. Fix the deadlocks, fix the memory grants queue.

Anyway, haven't yet looked at 2005 for these issues and possible explanations, but maybe there will be more info....
Post a Comment