Resource Governor clamps down on Greedy Reports

One of the things I run into every now and then is this provincial idea that reports can and should be read straight out of the transactional database.  From an architectural standpoint this is of course flawed at and worst disastrous to the performance of both the read/write transactional system and the read-intensive reporting system.

At scale, architectural decisions to place heavy-duty reports - even reports that run nightly, monthly or annually - can be paralyzing to an enterprise.

Using various tools like replication, ETL via SSIS, ETL via SSIS with CDC, database mirroring, or snapshots to provide an off-site, contention-free reporting environment is an elementary architectural decision.  Simply put - don't mix your read/writes with your heavy reads.

But what if you're stuck with a host of hard-hitting reports running against your application database?

Long-term, look into providing the business a viable reporting alternative - a data warehouse with slowly changing dimensions for historical trending, Analysis Services cubes, self-service BI via PowerPivot...

Short-term, take some aggressive steps like using the SQL Server Resource Governor... within reason.  Introduced with SQL 2008, surely this tool can let you wield tyrannical power over connections.  Instead of using this as a punitive hammer on your offending reports, or as a wide-sweeping penalizing stroke, use the Resource Governor as an insulator for your key apps.  Take the approach of protecting the application's read/writes from the reports' massive reads, and the resulting resource stranglehold.

Take this configuration for example.  This is a step-by-step on how to set up Resource Governor.  Note a large number of commented-out example configuration options.

use master

go

--http://msdn.microsoft.com/en-us/library/bb933944(v=SQL.100).aspx

CREATE WORKLOAD GROUP GovGroupReports;

go

CREATE FUNCTION dbo.fnResourceGovernorClassifier() RETURNS sysname 

WITH SCHEMABINDING

AS

BEGIN

      -- Note that any request that does not get classified goes into the 'default' group.

      -- This function is run in the context of the new connection.

      --http://msdn.microsoft.com/en-us/library/bb933865.aspx

     

      DECLARE @grp_name sysname

     

      IF (

                  SUSER_SNAME() IN ('ReportUser')

            AND IS_MEMBER ('db_datareader') = 1

            --AND HOST_NAME() IN ('ReportingServer')

            --AND SUSER_SNAME() IN ('ReportUser')

            --AND IS_SRVROLEMEMBER('bulkadmin') = 1

            --AND IS_MEMBER ('DOMAIN\READONLYGROUP') = 1

            --AND APP_NAME() LIKE 'Report Server%'

      )

      BEGIN

            SET @grp_name = 'GovGroupReports';

      END

      RETURN @grp_name

END;

GO

-- Register the classifier function with Resource Governor

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.fnResourceGovernorClassifier);

--ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= null);

GO

CREATE RESOURCE POOL GovPoolMaxMemory

WITH (--http://msdn.microsoft.com/en-us/library/bb934024.aspx

            MAX_MEMORY_PERCENT = 75

      --,   MIN_CPU_PERCENT = 0

      --,   MAX_CPU_PERCENT = 75

      --,   MIN_MEMORY_PERCENT = 0

);

GO

ALTER WORKLOAD GROUP GovGroupReports

WITH (--http://msdn.microsoft.com/en-us/library/bb895361.aspx

            MAX_DOP = 1

      --,   IMPORTANCE = { LOW | MEDIUM | HIGH }

      --,   REQUEST_MAX_MEMORY_GRANT_PERCENT = 25

      --,   REQUEST_MAX_CPU_TIME_SEC = 600

      --,   REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 600

      --,   GROUP_MAX_REQUESTS = 50

)

USING GovPoolMaxMemory

GO

-- Start Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

--Just in case

--ALTER RESOURCE GOVERNOR DISABLE

What we've accomplished with this Resource Governor implementation is to cap connections we've identified connections using ReportUser login.  We could identify these connections a number of different ways.

Remember that connections we don't catch will always fall through the function into the default bucket, and will be ignored by the Resource Governor.  Only groups we name will be directly affected.

The Resource Pool GovPoolMaxMemory we've created is pretty self explanatory - to simplify, it will cap usage of all connections in the pool to 75% of the SQL Server's available memory.  This is probably still very generous - we may want to make sure that other connections (the transactional application) will have more than 25% of all available memory.  This strategy does prevent reports from hogging ALL of the memory, and remember that is our strategy - to protect, not to punish.  

Similarly, the Resource Group we've created will restrict queries to one degree of parallelism.  That is a topic for another blog post, I recommend reading up on it.  In many cases, this can reduce CXPACKET wait types, further protecting the server from being crushed under the load of the reports.

After the governor is configured, use the query below to verify that new connections are dropping into the new bucket:  

SELECT

            es.session_id

      ,     es.login_time

      ,     es.login_name

      ,     [Group Name]            = wg.name

      ,     [Host Name]             = es.host_name

      ,     Application             = es.program_name

      ,     es.client_interface_name

      ,     es.memory_usage

      ,     es.total_elapsed_time

      ,     Pool_Max_Memory_kb      = rp.max_memory_kb

      ,     Pool_Max_Memory_kb      = rp.used_memory_kb

--SELECT *

FROM

      sys.dm_resource_governor_workload_groups wg

INNER JOIN

      sys.dm_exec_sessions es

ON es.group_id = wg.group_id

LEFT OUTER JOIN

      sys.dm_resource_governor_resource_pools rp

on rp.pool_id = wg.pool_id

WHERE

      wg.group_id > 2 --look only at connections being governed

"Serve the public trust, protect the innocent, uphold the law." -RoboCop