Resource Governor causing memory problem??

So.  Last week I blogged about the experiment I was running with Resource Governor for one of our newly upgraded development databases.  I had enabled Resource Governor and created a procedure to categorize sessions, but I hadn’t set any limits and everyone was sharing the same default pool.  The plan, if you’ll recall, was to monitor the Resource Governor stats to get a feel for the resources different apps were using.

This week, users have been doing a lot of testing in this environment.  A developer called me on Wednesday morning and said that users were complaining of very slow performance.  So I took a look and saw several sessions waiting on resource_semaphores.  Now, I’ve been supporting this application’s database for years now and I’ve never seen any memory waits, so this got my attention quick.  One of the sessions that was running was one of the reports that I’d enabled Resource Governor to address in the first place.  A look at sys.dm_exec_query_memory_grants told me this query was requesting almost 19GB of memory (or, put another way, all of the available memory).  What the…?

I captured the query (and asked the user to kill his report) and ran the query in our production (SQL 2005) database.  In that environment it was only requesting 7GB of memory.  Still a lot, but do-able in a system with 40GB available.  The query plans were slightly different between the 2 environments, but updating stats didn’t change that at all.  I decided to use Resource Governor as it was intended, and I set a 40% memory grant limit for the Reports workload group.  I re-ran the query and this time it was only requesting 7GB of RAM.

The immediate problem was remediated, but it niggled at the back of my brain; why had the query requested that much memory in the first place?  Thursday morning I got another call.  This time, when I checked the system, a different application was requesting all of the available memory.  I could have set a restriction for that workload group, too, but this was just treating the symptoms.  There had to be a root cause.

I started searching the Great Gazoogle for known memory issues in SQL 2008, but found nothing that explained why queries were suddenly using a lot more memory than they did in SQL 2005.  Think.  Think.  Think.  What if Resource Governor is the problem?  So I ran a series of tests:  with Resource Governor disabled, with it enabled and the memory limit set to 40%, and enabled with the memory limit set to 100% (or unlimited).  I also ran it in our 2005 environment just for comparison purposes, and I monitored sys.dm_exec_query_memory_grants for each test.  Here are the very interesting results.

Again I say:  what the…?  With Resource Governor disabled the query only uses 4.8GB of memory.  Enabling Resource Governor actually appears to cause the query to request more memory.  It almost looks like, rather than being a limit, the memory grant % is telling the query how much to use (putting ideas into its head).  I know that can’t be the case, but that’s how it looks.  I thought maybe I’d misunderstood the settings, that maybe I’d mis-configured Resource Governor, but I don’t think I did.

Memory Grant %
Specify the maximum amount of memory that a single request can take from the pool. Range is 0 to 100.

And technically, that’s what SQL Server did.  I said “this group can use 100% of the memory,” and it gave that query 100% of the memory.  Or tried to, anyway.  But if it really needed all that memory, why doesn’t it ask for it with Resource Governor disabled?

So far I haven’t been able to find any bugs to explain this behavior.  And a post to SQLServerCentral’s forums hasn’t yielded any responses.  I guess for now I’m going to leave it disabled and keep searching for an explanation.  If anyone out there has any insight, please post a comment.  I’d really appreciate it.