Hi Friends, I am back with one more blog post on an interesting feature in SQL Server 2008. Today I was discussing about run away situations with my colleagues and I found that only few people read about this excellent feature available in SQL Server 2008 so just thought why not to blog about it.
If you are using SQL Server everyday then you would be aware of the situations when you get into run-away situation during the peak load on the server. For example: there is a heavy transaction volume on the server and someone from the remote end fired an ad-hoc query to churn several tables, running to a few million rows and everything comes to a screeching halt. You would have always wondered if there was a way to manage this to avoid run-away situation. Even if there was any solution that sort you would have wondered can it be done on the fly with a minimal impact.
To explain it in a very simplified manner, every server hardware has a finite number of CPUs and amount of RAM installed on it. When you try to fire a query that is resource intensive and the hardware resources are not available to serve the request ..you get into a run-away situation due to which the service requested is delayed and other requests being handled by the server in parallel are also delayed.
In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.
Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor.
It is feature that is designed to manage the resource limits in real time with minimal impact on workloads that are executing.
Resource Governor addresses 3 primary issues that are prevalent in the database environments.
All of the above scenarios require the ability to differentiate workloads in some way. Resource Governor provides:
There is a clear scope in which Resource Governor works and it is important to understand the scope of it.
The following three concepts are fundamental to understanding and using Resource Governor:
Explanation (Step by Step):
For more information on Resource Governor you may visit SQL Server 2008 Books Online.
I will write more about the Resource Governor very soon so keep a watch.