SQL Server 2008 (SSQA.net)

SQL Server 2008 Resource Governor FAQ

Bottlenecks aka locking & blocking is a quite common scenario within a RDBMS platform, that too SQL Server is not an exception at all. With an access to shared resources causes bottlenecks and demands on shared resources cause poor response time and must be identified and tuned. Few root causes for such blocking are:

• Insufficient resources, requiring additional or upgraded components.
• Resources of the same type among which workloads are not distributed evenly; for example, one disk is being monopolized.
• Malfunctioning resources.
• Incorrectly configured resources.
• Some other component may prevent the load from reaching this component thereby increasing the time to complete the load.

• Client requests may take longer due to network congestion

All these bottlenecks are is again divided into CPU, memory usage,  Disk I/O and blocking locks, to get more understanding on System Resource within SQL Server 2008 has been documented very well and that too using Resource Governor can reduce such a contention to some extent when your database is performing poorly.

Resource Governor concepts are fundamental to understanding and using :

• 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.
  

Bob Dorr from PSS SQL Server engineers blog has given much insight about Resource Governor questions in addition to bit of information on SQL Server 2005 too.