Running SQL Server 2008 Sproc at a lower priority

Question

We have a large volume application based on ASP.NET 3.5 and SQL 2008, where we hope to maintain a high level of 24x7 availability without the need for a maintenance window.

Over time, we became dependent on some stored processes that perform service operations to clean up data that is no longer needed, compile some metrics, etc. Our problem is that these sprocs consume almost the entire processor on the servers, while they lead to significant consequences for the response to the site.

Is there a way to run these sprocs with lower priority? It does not matter how long they take to complete, as long as we can reduce the impact on the database server processor.

Answer 1

You can use Resource Governor to specify limits on the number of CPUs and memory that incoming application requests can use.

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO