SQL Server Resource Governor – When To Use and When Not To

SQL Server Resource Governor

SQL Resource Governor was implemented in the release of SQL 2008 and has been developed further with the onset of SQL 2012, and 2014.

It is often, mistakenly thought of as a “quick fix” for resource contention.

With this in mind, it’s important to consider what exactly the SQL resource governor feature does, and does not do.

Key points

  • Resource Governor requires the Enterprise edition of SQL Server (or developer/evaluation editions)
  • SQL 2008 and 2012 allows governance of CPU and Memory only
  • SQL 2014 introduced the additional governance of IO
  • Instance wide feature – you cannot apply resource governor per database

Each version of SQL introduces new functionality to the product, so it’s imperative to check which version of SQL you are planning on using.

SQL Server resource governor 2012 – New features

SQL Server 2012 enhanced the memory managed side of resource governor – for more info check out this whitepaper.

SQL Server resource governor 2014 – New features

SQL Server 2014 introduced IO handling capability. To find out more read The Essential Guide to SQL Server 2014: The Resource Governor.

Sql Server resource governor best practices

  • Ensure the classifier function is performing optimally. As this must be passed through for each and every connection to the database, if poorly coded this can quickly become a major bottleneck.
  • Ensure adequate resources are left aside for the default workload group.
  • Take extreme care when setting memory constraints to avoid starving other resource pools.

When should you use the resource governor?

The best use scenarios are those that require you to throttle a workload.

This makes better sense than trying to “reserve” resources for workloads as that, although theoretically possible, can come at the expense of performance.

How to create a classifier function

Resource governor uses a classifier function in order to allocate connections to the appropriate workloads.

To create a classifier function see this MS article https://msdn.microsoft.com/en-nz/library/cc645892.aspx

Do You Use, Or Plan To Use, Resource Governor?

We would love to hear what you are doing, so please reach out to us by way of a comment, and let us know what you are intending to do, or have already done, with resource governor.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

1 Comment

Leave a Reply