Knowing
how to monitor your Resource Governor configuration allows you to
validate the choices you made during the setup process. Resource
Governor monitors provide you with the essential data needed to alter
and adapt your configuration based on informed decisions instead of just
making random configuration changes and hoping for the best. You can
monitor your Resource Governor configuration using performance counters,
trace events, and views.
1. Performance Counters
There are two new performance counters you can use to collect information about resource groups and workload groups: SQLServer:Workload Group Stats and SQLServer:Resource Pool Stats. Both of these performance counters expose several properties in PerfMon and the sys.dm_os_performance_counters DMV. You can run the query in Listing 16-18 to see a list of properties returned for both of these performance counters for the default workload group and resource pool.
Example 1. Performance Counters for the Default Workload Group and Resource Pool
SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name IN
('MSSQL$SQL2K8:Workload Group Stats','MSSQL$SQL2K8:Resource Pool Stats')
AND instance_name = 'default'
|
The results of Listing 1 are displayed in Figure 1. As you can see, there is a lot of valuable information you can gather using these performance counters.
Using these counters in
PerfMon will provide a more interactive experience showing changes in
real time. If you alter your Resource Governor configuration, the
PerfMon counters will reflect the changes immediately. Using PerfMon is
extremely useful when adding and comparing counters from different
workload groups and resource pools.
2. Trace Events
There are three valuable trace events that you can use to obtain information about Resource Governor: CPU Threshold Exceeded, PreConnect:Starting, and PreConnect:Completed. The CPU Threshold Exceeded event will fire whenever a query exceeds the REQUEST_MAX_CPU_TIME_SEC value you set for a workload group. From the CPU Threshold Exceeded event, you can obtain the workload group where the violation occurred, the spid of the process that caused the violation, and when the violation occurred. The CPU Threshold Exceeded
event occurs every five seconds, so there is a slight chance that you
could exceed the query threshold and not trigger the event. You can use
the PreConnect:Starting and PreConnect:Completed events to determine how efficient your classifier function is operating. The PreConnect:Starting event fires when the classifier function begins execution, and the PreConnect:Completed event fires after it has finished. As you can see in Figure 2, the CPU Threshold Exceeded event is located under Errors and Warnings, and the PreConnect:Starting and PreConnect:Completed events are located in the Sessions section in SQL Server Profiler.
3. Views
There are six views, three DMVs,
and three catalog views, all of which provide insight into your
Resource Governor configuration. The DMVs provide statistical
information, while the catalog views provide information about your
current configuration. The Resource Governor DMVs are shown in Listing 2.
Example 2. Resource Governor Dynamic Management Views
SELECT * FROM sys.dm_resource_governor_workload_groups
SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_configuration
|
If you execute the code in Listing 2, you should receive results similar to the output shown in Figure 3. You can run the ALTER RESOURCE GOVERNOR RESET STATISTICS statement in order to reset the DMV statistics.
The Resource Governor catalog views are shown in Listing 3.
The catalog views provide the same basic information you could find by
selecting Resource Governor Properties from SQL Server Management
Studio.
Example 3. Resource Governor Catalog Views
SELECT * FROM sys.resource_governor_workload_groups
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_configuration
|
Figure 4 shows the output returned from running the code in Listing 3.
This is just an alternative way to get information about your current
configuration without having to dig through SQL Server Management
Studio.
