Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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


Comments

Posted by Anonymous on 15 October 2011

Pingback from  Resource Governor clamps down on Greedy Reports | SQL Server | Syngu

Leave a Comment

Please register or log in to leave a comment.