Resource Governor in SQL Server II

              Resource governor is technology that introduced in Microsoft SQL Server 2008 Enterprise edition onwards.  This feature can use to manage SQL Server workload and system resource consumption. It provide multi tenancy and resource isolation for single SQL Server instance when serving multi client workloads.

                SQL Server Resource governor allows you to set limits on the amount of memory and CPU resources that incoming requests can use and provide a way to isolate. It limits runaway quires adding fine grained resource tracking for charge back and delivering predictable performance.

               With SQL Server 2012 DBA’s can provide more complete isolation of CPU resources for workloads and put CAP’s on CPU usage for higher level of predictability and control greater proportion of SQL Server memory manager.

Resource governor issues comes with when there is unpredicted workload execution .

1. Long running Transact-SQL queries 
• SET NOCOUNT ON
  DECLARE @i INT
  DECLARE @s VARCHAR(100)
  SET @i = 100000000
  WHILE @i > 0
  BEGIN
  SELECT @s = @@version;
  SET @i = @i - 1;
  END
   
2. Database backups being performed
3. Non ending  query execution
• SET NOCOUNT ON
  DECLARE @i INT
  DECLARE @s VARCHAR(100)
  SET @i = 1
  WHILE @i > 0
  BEGIN
  SELECT @s = @@version;
  SET @i = @i + 1;
  END