SQL Server 2008: Monitoring Resource Governor

2/22/2011 9:20:04 AM
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
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.

Figure 1. Resource Governor 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.

Figure 2. Resource Governor trace events


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.

Figure 3. Resource Governor DMV sample output

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.

Figure 4. Resource Governor catalog view sample output

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone