Resource Governor Manages Workloads

Administrators can control how computer resources are assigned to SQL Server workloads using a new resource governor in SQL Server 2008. In the illustration, the resource governor controls resources for three separate classes of clients: a reporting application, the Dynamics CRM application (MSCRM), and a backup application.

When an application connects to the server to initiate a session, a classifier function executes, evaluates a set of administrator-defined rules, and based on the outcome assigns the session to a workload group. In the illustration, the classifier function assigns sessions initiated by the reporting application to a workgroup called “grpReport,” those initiated by the backup and recovery application to “grpBCK”, and those initiated by MSCRM to “grpMSCRM.”

Workload groups specify resource constraints for individual SQL Server requests (such as queries) in these sessions—for example, the grpReport workload group could specify the maximum CPU time allowed for queries initiated by the reporting application, which could be used to flag long-running reports. SQL Server includes two built-in workload groups—the internal workload group is reserved for SQL Server’s critical internal functions and cannot be altered; the default group contains all other workloads that were not assigned to a specific group by a classifier function.