Resource Governor Tips

Handling workloads has been quite difficult until SQL Server 2005. For example consider a scenario where one SQL instance is serving two applications i.e. an OLTP application and a reporting/data warehousing application. Since reporting applications are normally resource intensive, it may consume all the SQL Server available resources and may hamper the OLTP application which ideally should have more preference over the reporting application. To handle this scenario, in earlier version of SQL Server there was one option to create multiple instances for these application (segregating by running one application on each instance) and setting CPU affinity mask for these instances appropriately. But the problems with this approach are, first

Those of us using the Resource Governor, introduced in SQL Server 2008, often have to make arbitrary choices about how to limit specific resource pools or workload groups in terms of CPU, memory, parallelism and concurrency. In many cases we "set it and forget it," assuming that the choices we have made are correct; unless somebody complains, we have little motivation to revisit our configuration to determine if it should be adjusted in any way. Just because nobody is actively complaining does not necessarily mean that changes are not needed.

Handling workloads has been quite difficult until SQL Server 2005. For example consider a scenario where one SQL instance is serving two applications i.e. an OLTP application and a reporting/data warehousing application. Since reporting applications are normally resource intensive, it may consume all the SQL Server available resources and may hamper the OLTP application which ideally should have more preference over the reporting application. To handle this scenario, in earlier version of SQL Server there was one option to create multiple instances for these application (segregating by running one application on each instance) and setting CPU affinity mask for these instances appropriately. But the problems with this approach are, first

Those of us using the Resource Governor, introduced in SQL Server 2008, often have to make arbitrary choices about how to limit specific resource pools or workload groups in terms of CPU, memory, parallelism and concurrency. In many cases we "set it and forget it," assuming that the choices we have made are correct; unless somebody complains, we have little motivation to revisit our configuration to determine if it should be adjusted in any way. Just because nobody is actively complaining does not necessarily mean that changes are not needed.

Those of us using the Resource Governor, introduced in SQL Server 2008, often have to make arbitrary choices about how to limit specific resource pools or workload groups in terms of CPU, memory, parallelism and concurrency. In many cases we "set it and forget it," assuming that the choices we have made are correct; unless somebody complains, we have little motivation to revisit our configuration to determine if it should be adjusted in any way. Just because nobody is actively complaining does not necessarily mean that changes are not needed.

Handling workloads has been quite difficult until SQL Server 2005. For example consider a scenario where one SQL instance is serving two applications i.e. an OLTP application and a reporting/data warehousing application. Since reporting applications are normally resource intensive, it may consume all the SQL Server available resources and may hamper the OLTP application which ideally should have more preference over the reporting application. To handle this scenario, in earlier version of SQL Server there was one option to create multiple instances for these application (segregating by running one application on each instance) and setting CPU affinity mask for these instances appropriately. But the problems with this approach are, first