Resource Governor – CPU Allocations

Microsoft in the News

On February 4, 2019, a new podcast series that explores how AI is helping solve previously unsolvable societal issues. Microsoft has sponsored the series which will cover topics ranging from AI breakthroughs to environmental issues.

You can find the podcast on iTunes, Google Play and Spotify. The release dates and details are as follows:

  • Feb. 4: AI for Humanitarian Action (podcast, transcript)
    With Justin Spelhaug, Microsoft general manager for Technology for Social Impact
  • Feb. 6: AI for Accessibility
    With Wendy Chisholm, Microsoft principal accessibility architect, and AI for Accessibility grantee InnerVoice
  • Feb. 8: AI for Earth
    With Lucas Joppa, Microsoft chief environmental officer, and AI for Earth grantee SilviaTerra
  • Feb. 18: AI for Healthcare
    With Peter Lee, corporate vice president, Microsoft Healthcare
  • Feb. 20: Human-Centered Design
    With Mira Lane, Microsoft partner director–ethics and society
  • Feb. 22: Fairness in Machine Learning
    With Hanna Wallach, principal researcher at Microsoft Research

Resource Governor – CPU Allocations

Your SQL Server instance has a pool of resources available to it that consists of the CPU, the physical IO, and the memory that are all associated with this instance. A Resource Pool as defined by SQL Server Resource Governor is a virtual pool within the larger pool. This virtual pool of resources is defined by you specifying limitations on how to use the greater pool of resources.

The commands you will use in defining CPU usage in the virtual resource pools are as follows:


When there are workloads that compete for CPU bandwidth, you can use these settings to ensure that those workloads are prioritized appropriately. For example, if you have Client facing ( Web requests) and Non client facing resources (Accounting) sharing a database, but you cannot allow a slowdown for Client facing, you can assign CPU resources accordingly.

Sessions can be classified through the Classification Function and assigned to the appropriate group – in this case: Web or Accounting. Once classified and shunted to the correct group, they will make use of the resource pool assigned to that group. In this case, you can allocate a minimum CPU percentage to one group and a maximum CPU percentage to the other group.

Because you cannot allow client facing resources to be slowed down, you might allocate a minimum 80% of the CPU resource to web requests. Correspondingly, you would allocate a maximum of 20% of the CPU resource to Accounting. This will isolate the Accounting sessions and ensure they do not impinge on Web requests.

The above allocation would only be enforced by the Resource Governor when there is competition for CPU resources. If Production is only using 10% of the CPU resources, Resource Governor would not enforce the maximum CPU percentage allocated to Accounting. Accounting could, if it needed, use up to 90% of the CPU resource in this instance.

In my next blog, I will continue discussing CPU allocation.

Leave a Reply

Your email address will not be published. Required fields are marked *