Cleveland DBA

SQL Server administration in Cleveland, OH


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.


April 25, 2011 Posted by | Features, Troubleshooting | , , | 1 Comment

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.

April 22, 2011 Posted by | Features, Troubleshooting | , , , | 1 Comment

Resource Governor – Getting started

One of the databases I’m responsible for is a fairly large OLTP system.  For the majority of the time, it runs great.  Unfortunately, there’s a select group of users who insist on using it as an OLAP system, running very large reports or queries during business hours and consuming a lot of system resources in the process.  There have been a few occasions where these reports have brought this very robust server to a crawl.  (We’ve gone over several options for offloading this reporting, but nothing was “real-time” enough for their use, so here we are.)

This year we’ll be upgrading this database to SQL Server 2008 and I’m very excited to make use of the Resource Governor feature.  I figure if I can’t get them off the system I can at least prevent them from killing the system, right?  But how do I know what resource limit(s) to set?  Pick some arbitrary number?  Probably not a good idea.  I want to control them, not strangle them.  So what I did was something recommended here, in Scenario 1.  I tweaked the code a little for my own use.

-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs.
-- Create a classification function.
-- Note that any request that does not get classified goes into
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname
 DECLARE @grp_name sysname
 SET @grp_name = 'GroupAdhoc'
 IF (SUSER_NAME() = 'sa')
 SET @grp_name = 'GroupAdmin'
 SET @grp_name = 'GroupReports'
 RETURN @grp_name
-- Register the classifier function with Resource Governor
-- Start Resource Governor

Notice I’m not setting any resource limits here.  The purpose is to categorize sessions into the appropriate group and monitor the normal resource usage.  I can see this usage by querying sys.dm_resource_governor_workload_groups.

USE master;
SELECT * FROM [sys].[dm_resource_governor_workload_groups]

In particular, I’m going to be keeping an eye on the max_request_cpu_time_ms and max_request_grant_memory_kb columns.  Once I get an idea of what they’re currently using, I’ll be better able to decide what limits to set.  At that point I can set resource restrictions within Resource Governor or I can choose to have an alert triggered if a limit is exceeded.  I know – very exciting stuff!

(But I’m even more excited about the prospect of AlwaysOn mirroring in SQL Server Denali for offloading these reports.  I mean, what’s more “real-time” than “real-time”??)


April 13, 2011 Posted by | Features | | 1 Comment