16.6. Monitoring resource usage
Workload groups and resource pools can be monitored using three methods: Performance Monitor, events, and Dynamic Management Views.
16.6.1. Performance Monitor
Performance Monitor is the primary mechanism for monitoring Resource Governor usage in SQL Server 2008. Two performance objects are available: SQLServer:Workload Group Stats and SQLServer:Resource Pool Stats.
We saw an example of using the CPU Usage % counter from the SQLServer: Resource Pool Stats earlier in figure 16.5. SQL Server Books Online documents all of the counters for these objects, but let's briefly have a look at some of the counters available for SQLServer:Workload Group Stats:
Queued Requests—If you're using the GROUP_MAX_REQUESTS argument in the workload group definition, this value may be non-zero if throttling has occurred.
Active Requests—This counter represents the number of requests currently executing in the group.
CPU Usage %—This shows the total CPU usage percentage used by all executing requests in the group.
Max Request CPU Time—This is the maximum CPU time used by requests currently executing in the group.
Active Parallel Threads—This indicates the number of executing parallel threads.
Resource Governor also fires events that can be captured and used for alerting.
16.6.2. Events
In chapter 14, we examined the power of establishing alerts for certain events. Resource Governor introduces three new events:
CPU Threshold Exceeded—As we discussed earlier, when a query exceeds its CPU threshold, this event will fire, rather than the query being canceled.
PreConnect:Starting and PreConnect:Completed—When a Resource Governor classifier function or logon trigger starts and finishes, these events will fire.
Finally, we have three DMVs for monitoring purposes.
16.6.3. DMVs
The following DMVs are available for inspecting Resource Governor configurations and statistics:
sys.dm_resource_governor_configuration—This DMV returns the Resource Governor configuration state. Figure 16.6, shown earlier, returns similar information through Management Studio.
sys.dm_resource_governor_workload_groups—This DMV is used to return workload group configuration and statistics.
sys.dm_resource_governor_resource_pools—This DMV is used to return resource pool configuration and statistics.
In closing this section, let's consider how we can use these monitoring tools to help establish the ideal resource boundaries for resource pools.
16.6.4. Establishing resource boundaries
Earlier in our coverage, we spoke about the danger of running complex Resource Governor schemes with numerous configured minimum resource values, particularly for memory. We also covered the fact that Resource Governor is best used in SQL instances where there are well-defined groups of usage patterns, for example, reports versus data entry. If you decide to proceed with Resource Governor, perhaps the best way of evaluating the most appropriate resource usage limits is by using the supplied monitoring tools and the following approach:
Create the user-defined workload groups as planned, but assign all of them to the default resource pool configured with min/max values of 0/100.
Run the database instance for a period of time long enough to cover the typical usage cycle.
Once the test is complete, use the monitoring tools covered previously to determine how resources are used by each of the workload groups; that is, determining the average, minimum, and maximum values of memory and CPU will help you to shape the appropriate minimum and maximum values to apply, as well as estimate the impact of such values.
In a load-testing environment, establish resource pools for each of the workload groups, based on information gathered from step 3.
Load test the system, ensuring enough load is generated for all resource groups to enable appropriate contention in order to observe the performance impact once Resource Governor CPU throttling kicks in.
Adjust and refine the pool and/or workload group parameters until the desired result is achieved.
Apply the results to production, ensuring the dedicated administrator connection is available.
Such an approach enables more realistic starting values to be used, while also producing a deeper understanding of resource usage for each of the workload groups.