16.7. Best practice considerations: Resource Governor
Prior to SQL Server 2008, the only mechanisms available for controlling resource usage were coarse-grain settings such as CPU Affinity and Query Governor Cost Limit. The introduction of Resource Governor in the Enterprise edition of SQL Server 2008 now enables much finer control over resource usage, but like any feature, system performance may suffer if it's used incorrectly.
Resource Governor is most effective when resource usage is well defined and consistent across a number of distinct groups, for example, data entry and reports. In cases where certain data entry transactions dominate resource usage, Resource Governor may not be effective, unless there is a method of identifying and classifying the source of such transactions.
Classifier functions should be small, simple, and execute quickly to ensure logins do not time out waiting for the function to execute. The function should be tested, ideally under load, before production implementation.
Workload groups and resource pools should be as simple as possible, and you should take care when assigning minimum values, particularly for memory. Complex pools and groups run the risk of wasted memory and less-than-expected resource availability.
When setting minimum resource values, consider the effective maximums that will apply to other pools as a result.
Minimum memory values are reserved up front, regardless of the usage frequency of the pool. Therefore, take particular care when setting memory minimums.
Before assigning resource pool boundaries, assign groups to the default pool with 0/100 min/max values and observe the resource usage over time. Understanding how groups use resources will lead to more realistic resource settings.
Consider the security of classification functions; for example, APP_NAME() can be spoofed by someone with access to a connection string to circumvent logon triggers and workload group classification.
The names of Resource Governor groups and pools should not contain sensitive information, given their inclusion in error messages presented to the user.
The dedicated administrator connection (DAC) should be enabled, which assists in troubleshooting the classifier function.
When load testing the impact of a max CPU pool setting, ensure there is sufficient CPU contention such that the CPU maximum value is actually applied. If there is no concurrent activity to cause contention, the maximum limit will not be applied.
Additional links and information on the best practices covered in this chapter can be found online at http://www.sqlcrunch.com/resourcegovernor.
In the next and final chapter, we'll bring together a number of topics that we've covered throughout the book by introducing a performance-tuning methodology called waits and queues.