1140
Part VIII: Performance Tuning and Optimization
Views and Limitations
Following are three catalog views for the Resource governor:■ (^) sys.resource_governor_configuration
■ (^) sys.resource_governor_resource_pools
■ (^) sys.resource_governor_workload_groups
These views enable you to view how the Resource Governor, your resource pools, and your
Workload groups are confi gured.
Three Dynamic Management Views support the Resource Governor, which enable you to
monitor it:
■ (^) sys.dm_resource_governor_workload_groups
■ (^) sys.dm_resource_governor_configuration
■ (^) sys.dm_resource_governor_resource_pools
The Resource Governor also has some limitations:
■ (^) It is limited only to the Database Engine. But some services are not a part of the
Database Engine such as Analysis, Integration, and Reporting services. For these,
you still must rely on other solutions to manage the resource problem (such as
installing separate SQL Server instances).
■ (^) You can manage only a single instance through the Resource Governor. An organi-
zation may have more than a single instance but must manage each separately.
■ (^) The Resource Governor currently has only two resources: CPU and memory.
Summary
Resource Governor is an extremely useful tool to prevent run-away queries. In addition,
it can prevent many DBA nightmares. Judicious use of the Resource Governor allows you
to dynamically control resource CPU and memory allocations for queries, which allows you
to control your environments proactively rather than reactively. This granular control of
resource allocation becomes critical in consolidated environments, such as shared instances
or private cloud.c50.indd 1140c50.indd 1140 7/31/2012 10:22:31 AM7/31/2012 10:22:31 AM
http://www.it-ebooks.info