Tuesday, April 7, 2009

Handling workloads on SQL Server 2008 with Resource Governor

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 it works for CPU only and second the dedicated CPUs cannot be shared by other SQL Server instances. For example, if there are two SQL Server instances and instance one has been assigned CPU 1 and 2 and instance two has been assigned CPU 3 and 4 on a four processor machine, even if instance one is idle and instance two is in need of additional resources, it can only use CPU 3 and 4.  So what does SQL 2008 offer to solve this issue?

No comments:

Post a Comment