When I meet SQL Server professionals, I am always interested to find out if they have deployed the latest version of SQL Server into production yet, if they are using Enterprise Edition, and, if so, which new features they are using and why. Nothing beats real world implementation scenarios to help get a better understanding of a feature in SQL Server.
The most common Enterprise Edition SQL Server Engine features deployed (and this is not a scientific survey, by any means) appear to be Table Partitioning, Backup Compression (now in Standard Edition) and Resource Governor. The Resource Governor was the feature DBAs working with large-scale SQL Server environments seemed most excited about when SQL 2008 was first announced.
Resource Governor enables DBAs - for the first time - to implement quotas around some of the resources available to SQL Server. Workload groups (e.g., sets of users, specific applications or databases) can be defined, and limits on the amount of CPU or memory those groups can use can be set. I/O throttle is not implemented as yet, but is rumoured to be on the roadmap for Resource Governor. Resources cannot be limited for internal SQL Server processing (e.g., checkpoints) so you cannot adversely affect the tasks SQL Server needs to able to complete. Throttling is not used unless required, so a workload group limited to 20% CPU will use as much CPU as it needs unless other workload groups require CPU resources. An example of its use would be a database with sales transactions and marketing reports running against it. It may be desirable to ensure the sales workload is met at a higher priority, so resource governor could be implemented to ensure those users are provided with more memory and CPU resources.
Some DBAs are required to implement accurate chargeback to departments for which they provide database application services. Traditionally, chargeback has been measured using data, log and backup space, and calculated per the cost of storage required for the databases, but this is rarely a completely fair reflection on the cost of resources the IT department must provision. Having the ability to specify, monitor and record which users are employing certain resources allows DBAs to make more fair and accurate chargeback calculations. If users require better performance from an application, they can be correctly charged for the extra resources provided. This is particularly useful in consolidated environments where resources tend to be shared among many applications and sets of users.
Resource Governor is also useful to ensure that potential "runaway" transactions can be controlled; if a user runs an inefficient query across a large dataset, it will not adversely affect other users. It can also be used to limit the number of connections from a workload group, and some of the throttle parameters can be changed dynamically while Resource Governor is implemented. Different resourcing thresholds can be implemented based on different timeframes. So, perhaps user workload can take priority during the work day, and batch processing can take priority after hours.
There are some limitations at the moment. Resource Governor is only implemented for the SQL Server Engine, and there is no way to tell if a specific query was throttled retrospectively. It tends to be of limited value with fast running OLTP transactions, as the throttling does not have time to take effect.
Given the correct workload scenarios, Resource Governor can be a valuable tool for the DBA to provide the optimal service to priority users and more accurately meet business SLAs and calculate costs.
Microsoft Whitepaper for Resource Governor: http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/ResourceGov.docx
Email: iain.kick@quest.com, follow me on Twitter: www.twitter.com/iainkick or visit my blog: www.iainkick.com.