Working with Resource Governor in SQL Server 2012

 

 

Resource governor allows system administrators and database administrators to limit the amount of resources the SQL Server database engine can consume. Note that this cannot be used with other SQL services such as reporting services. Incoming client requests can be controlled by specifying type of resource such as memory and processor. Workload is determined by query size and requests. The rule for workload can be changed dynamically without affording down time.

When SQL Server 2012 is installed, internal resource pool and default resource pool is automatically created. You may create additional resource pools as needed. Internal workload groups and default workload groups are also created during install while allowing additional ones to be added. How Resource Governor works is that when SQL server receive a request, the session is classified, then routed to a workload group and it uses the resource pool assigned to the workload group.

 

How to turn on or off the Resource Governor

  1. Open SQL Management Studio > Management > Resource Governor
  2. You can rightclick “Resource Governor” and choose Enable or Disable.

 

Creating a resource pool for Resource Governor

  1. Open SQL Management Studio > Management > Resource Governor
  2. Rightclick “Resource Governor” and choose “New Resource Pool”
  3. Click the Name column and enter the name of the resource pool
  4. Now you should see the newly create resource pool under “Resource Governor”

 

How to create a new workload group

  1. Open SQL Management Studio > Management > Resource Governor
  2. Rightclick “Resource Governor” and choose properties.
  3. In resources pool pane, click first column of newly created resource pool earlier. Enter the name you want to use for workload and click on to exit.

<img src="file:///C|/Documents and Settings/Administrator/My Documents/infotechguyz/SQLServer2012/WorkingwithResourceGovernorinSQLServer2012.jpg" width="685" height="534" />