SQL Server 2008 Resource Governor

  • Article

Here are some notes on “SQL Server 2008 Resource Governor” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Nasty queries

  • Case: “Query from hell” takes all resources
  • Case: Controlling users that connect using Excel, Access
  • SQL Server 2005: Single resource pool, can’t differentiate workloads, best effort resource sharing
  • Approach: Query governor cost limit. Issues…
  • Approach: Add a “TOP 100000” to all queries. Issues…
  • Approach: Look at the showplan_xml before actually executing. Issues…

Resource Governor

  • SQL Server 2008: Putting all resources together
  • Workloads mapped to Resource Pool
  • Use only if you are an experienced administrator!
  • You can control – Min/Max Memory, Min/Max CPU – (missing: IO)
  • Works well for long-running, reporting type queries
  • Not so much for short-running, OLTP type queries
  • Books Online: Managing SQL Server Workloads with Resource Governor
  • See https://msdn.microsoft.com/en-us/library/bb933866.aspx

Workload Groups

  • Determined at connection time using classifier function
  • Defined on a variety of things – App name, Login, User, DB
  • Can have an importance label – High, medium, low
  • Case: If you don’t give me an app name, goes in the “tough luck” group
  • Internal group – internal cleanup like DBCC, can always go to effective 100%
  • Default group - can go to Shared Max % (what's left after all Min% allocated)

Resource Pools

  • Can control - Min Memory %, Max Memory %, Min CPU%, Max CPU%, MaxDOP
  • 20 resource pools max, don’t create too many up front
  • Percents can end quite different from what you set. How come?
  • Maximum applies only when contention occurs, reductions not applied immediately
  • Start by adding up all the Min% - Consider that set aside - What's left is the Total Shared%
  • Careful – Min memory % - should not total more than 100%
  • A specific pool will go from Min% to Max% on demand, limited by Min% + Total Shared%

Effective Max% 

  • Example: Pool A, Min=30%, Max=90%
  • Example: Pool B Min=25%, Max 50%
  • Example: Pool C Min=5%, Max=80%
  • Calculating: Total Shared % = 100 - SUM (all Min%) 
  • Example: Total Shared% = 100 - (30%+25%+5%) = 100 - 60% = 40%
  • Calculating: Effective Max% for a pool = MIN( Pool Max%, Pool Min% + Total Shared% )
  • Example: Pool A, Eff Max% = MIN (90%, 30%+40%) = 70%
  • Example: Pool B, Eff Max% = MIN (50%, 25%+40%) = 50%
  • Example: Pool C, Eff Max% = MIN (80%, 5%+40%) = 45%
  • Default pool: Min=0%, Max=100%. Example: Eff Max = 40%
  • Internal pool: Min=0%, Max=100%. Example: Eff Max = 100%

Classifier functions

Demo

  • Create database, a few users (one with CHECK_POLICY=OFF)
  • Look at configuration with sys.dm_resource_governor%
  • CREATE RESOURCE POOL – Though luck pool, good guy pool
  • ALTER RESOURCE GOVERNOR RECONFIGURE
  • CREATE WORKLOAD GROUP groupname USING poolname
  • How to assign someone into a pool – using a function
  • CREATE FUNTION … RETURNS SYSNAME WITH SCHEMABINDING
  • ALTER RESOURCE GOVERNOR WITH (classifiying function)
  • Going into performance monitor, adding counters from SQL Server: Resource Pool Stats
  • Testing with EXECUTE AS USER=’user’ – Is not effective
  • Testing with another connection under another user
  • To make effective use ResourceGovernor, need separate connection pools by identity