Resource Governor in SQL Server 2008


Resource Governor in SQL Server 2008

SQL Server 2008 Resource Governor allows you to control the resource according to the requirements. This was motive behind this feature is providing predictable response to the user in any situation. In earlier versions, we had a single pool of resources like Memory, CPU, threads etc. You can not priorities the workload versus Resource pool in 2005 and earlier version. Generally, who accesses the system first and starts a process, it can consume the resources without any restrictions. Consider, some kind of BI runaway query is first hit system where the OLTP and OLAP Databases are in the same server. Now the OLTP process has to wait till the OLAP process releases the resource. This was a major concern in earlier versions. So what were the solution then, go for multiple instances and configure the Resource per instance or go for different machine altogether. Both methods were having their own problem. By specifying the resource, if the system is not using that resource still will not released. If you go for another machine, you may have license issue and it’s not a cost effective method.

In SQL Server 2008, these problems are addressed by providing a tool called Resource Governor. You can differentiate the workload by Application Name, Login, Group, by database name etc. Once you have defined the workload, you can configure the resource which can consumed by workload. Probably, you want to give more resource for your OLTP application than the OLAP. You have that kind of flexibility and control here.
The following three concepts are fundamental to understanding and using Resource Governor:
Resource pools: Two resource pools (internal and default) are created when SQL Server 2008 is installed. Resource Governor also supports user-defined resource pools.
• Workload groups: Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed. Resource Governor also supports user-defined workload groups.
• Classification: There are internal rules that classify incoming requests and route them to a workload group. Resource Governor also supports a classifier user-defined function for implementing classification rules.

Advertisements

One thought on “Resource Governor in SQL Server 2008

  1. Pingback: Did you know? SQL Server 2008 includes Backup Compression

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s