Running a SQL Server 2008 Sproc at Lower Priority

Question

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

Over time, we have become reliant upon some stored procs which perform housekeeping operations to purge data which is no longer needed, compile some metrics, etc. Our problem is that these sprocs consume almost all the CPU on the servers while they are running resulting in a significant impact on the site's responsiveness.

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

Answer 1

You can use the Resource Governor to specify limits on the amount of CPU 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