April 21, 2011 at 2:58 pm
System summary:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Enterprise Edition (64-bit)
Windows 2008 (64-bit)
32GB RAM (SQL Server max memory 28GB)
8 processors
Background:
We're upgrading a ~300GB database from SQL 2005 to 2008. We have some large Cognos reports that have caused performance issues in the past and thus I've been exploring the possible use of Resource Governor. To monitor the resource usage of various application groups, I followed the steps in Scenario 1 here. I put no limits on any of the groups, so the max memory for the pool was 100% and the memory grant % for each group was 100%.
Problem:
We began seeing resource_semaphore waits when larger queries and reports were running. I ran a few test scenarios with one of the bigger Cognos reports and monitored sys.dm_exec_query_memory_grants.
[font="Courier New"]
ResourceGovStatusRequested Mem KBGranted Mem KB
DISABLED 4854288 4854288
ENABLED(max 40%)7766864 7766864
ENABLED(max 100%)19417176 NULL[/font]
As you can see, with Resource Governor disabled, the query only requests 4.8GB of memory. If I enable RG with a 40% memory grant restriction for the workload group, the query actually requests more memory. And if I enable it and don't restrict anything, the query tries to grab all the available memory and basically brings the database to a stand still.
Has anyone seen this behavior before? If this is expected behavior I'm a little wary about implementing RG in a production environment.
Thanks,
Colleen
April 25, 2011 at 11:08 am
Found it! From this very informative article, Understanding SQL Server Memory Grant:
"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 the way to configure it is through using Resource Governor to override it. Well, that answers that. Thanks for looking, all!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply