I found the answer to my Resource Governor quandary in this outstanding article, Understanding SQL Server Memory Grant, on MSDN.  The juicy bit:

The server calculates the memory limit for one query. By default, this is 25% (20% on 32bit SQL 2005) of total query memory (which is set by memory broker as about 90% of server memory). This per-query limit helps to prevent one query from dominating the whole server. This percentage is configurable on SQL 2008.

And how is it configurable, you ask?  By using Resource Governor to override it.

Really though, read the article, it’s extremely informative.  I’d like to give the author a great big hug.

Also recommended:

Leave a comment

Your email address will not be published.

One thought on “Aha!

  • Joel

    This was the article I have been searching for. I was performing the same analysis but couldn’t figure out why it made a difference. The default 25% was the key.