SQL Server 2008: A Must Do Configuration for Resource Governor

Over the past few months, I have been upgrading numerous SharePoint environments to SQL Server 2008 and I have wrestled with system performance. Unless adjustments are made, SQL Server will attempt to consume more than its share of processor. I've seen this cause SharePoint web applications to freeze and become unusable.

A resource management measure for SQL Server 2008 which I consider to be crucial for SharePoint environments, is outlined below. These steps will limit SQL Server Management Studio and Query Analyzer to 25% processor utilization, and possibly preserve the usability of the SharePoint farm. I strongly recommend these steps for SharePoint environments using SQL Server 2008.


SQL Server 2008 Resource Governor

Steps take from TechNet article:
Integration of SQL Server 2008 and Office SharePoint Server 2007
http://technet.microsoft.com/en-us/library/cc990273.aspx


1. Create a resource pool to limit CPU usage to 25 percent

CREATE RESOURCE POOL poolAdhoc
WITH (MAX_CPU_PERCENT = 25);


2. Create a workload group for ad hoc queries and register it with the new resource pool

CREATE WORKLOAD GROUP groupAdhoc
USING poolAdhoc;


3. Create a function that classifies Management Studio and Query Analyzer as members of the ad hoc group

CREATE FUNCTION adhocQueryClassifier() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN DECLARE @grp_name AS SYSNAME
IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
OR (APP_NAME() LIKE '%QUERY ANALYZER%')
SET @grp_name = 'groupAdhoc'
RETURN @grp_name
END
GO


4. Register the new function with Resource Governor

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION= dbo.adhocQueryClassifier);


5. Restart Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;