Cleveland DBA

SQL Server administration in Cleveland, OH

Aha!

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.

Advertisements

April 25, 2011 - Posted by | Features, Troubleshooting | , ,

1 Comment »

  1. 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.

    Comment by Joel | June 2, 2011 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: