Resource governor related

GokhanVarol 2013-03-13 21:26:03

Introduction:
Lately we have been running into some memory problems, the server has 256GB of memory and quite a few queries are taking about 45GB of memory (seems like the max they can take in that system), and there is quite a bit of them. We implemented resource governor, all sysadmins and service account drop back to default pool and the rest drop into the users pool. I highligted the only non default settings in our resource governor setup.
It's kind of related, I also needed to drop "index create memory (KB)" setting on the server to 20GB since index creations and so are also taking 45GB (so does DBCC check table or checkdb)

We do not have cpu bottleneck but we have highly partitioned tables (3300+ partitions each on 100+ table, database is a mix of oltp and data warehouse about 4 tb in size ), the most # of rows in a table is little over 1.3 billion and largest table has 900 million rows and over 0.8TB row compressed data. Statistics are not our friends really, since our data vary between partitions (data comes from different counties in US, they have different rules and regulations etc). To cut it short the queries users are writing do the job but they just learned sql within the last year. Till we get all the code optimized we need to still let this queries operate somehow. Within few months a new server with 1TB ram will replace this but unless we can control the memory usage in the queries that won't be enough either since our users and their assignments are increasing.

Questions:
1-) I did not understand one thing well, having multiple pools, does it mean it's not an efficient use of memory, meaning the hot buffer cache from default pool can it be used by a session on running under a different pool or the same page may need to be brought up to the memory twice, if it is twice is that problematic somehow?

2-) If above is a concern (to me it sounds not efficient use of memory if that is the case) is there any way just to limit query grant maximum for users (maybe even the whole server without exceptions) without creating a second pool?

3-) Defaulting service accounts and sysadmins to default pool, is that a good idea, I set it up that way thinking it will be more efficient use of memory.

Thank you

Resource Governor Setup:

CREATE FUNCTION dbo.fnResourceGovernorClassifierFunction ( )
RETURNS SYSNAME
    WITH SCHEMABINDING
AS 
BEGIN
    IF SUSER_SNAME() IN /* SYSADMINS */
    RETURN 'default'
    RETURN 'Users_WG'
END
GO
 
CREATE RESOURCE POOL [Users_Pool]
WITH
    ( MIN_CPU_PERCENT = 0
     , MAX_CPU_PERCENT = 100
     , CAP_CPU_PERCENT = 100
     , MIN_MEMORY_PERCENT = 0
     , MAX_MEMORY_PERCENT = 25
     , AFFINITY SCHEDULER = AUTO);
 
CREATE WORKLOAD GROUP [Users_WG]
 WITH
    (  IMPORTANCE = MEDIUM
        , REQUEST_MAX_MEMORY_GRANT_PERCENT = 25
        , REQUEST_MAX_CPU_TIME_SEC = 0
        , REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0
        , MAX_DOP = 4
        , GROUP_MAX_REQUESTS = 0 )
USING [Users_Pool];
 
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[fnResourceGovernorClassifierFunction]);
Jonathan Kehayias 2013-03-13 21:44:19
Resource Governor only controls execution memory grants not buffer pool usage so the data pages would only be in cache once, regardless of the pool the user executed query was running in. If you have queries consuming 45GB of execution memory, I'd be looking at why they have such large grant requirements in the execution plan. Running with a lower grant will likely spill to tempdb which isn't ideal performance wise.