Introduction
We all know that one of the most important parts of SharePoint 2013 (and 2003, 2007 and 2010) are SQL Server. Bad SQL Server performance will lead to bad SharePoint performance! That’s just how it is! There are tons of ways of doing this by having enough cores, adding more RAM, using fast disks, using multiple instances and even servers. You should all already be familiar with this.
Search is one of the components in SharePoint that requires A LOT of resources, especially when crawling and doing analytics. For both SQL Server and SharePoint Search there are plenty of documentation on how to optimize both the hardware and configuration of these components. In this post I will explain and show you how to use the SQL Server Resource Governor to optimize the usage of SQL Server, especially for Search.
SQL Server Resource Governor
The Resource Governor was introduced in SQL Server 2008 and is a feature in SQL Server that allows you to govern the system resource consumption using custom logic. You can specify limits for CPU and memory for incoming sessions. Note that the Resource Governor is a SQL Server Enterprise feature (but also present in Developer and Evaluation editions).
The Resource Governor is by default disabled and you have to turn it on. Just turning it on doesn’t do anything for you. You have to configure the Resource Pools, Workload Groups and the Classification.
Resource Pools
Resource Pools represents the physical resources of the server, that is CPU and memory. Each resource has a minimum value and a maximum value. The minimum value is what the resource governor guarantees that the resource pool has access to (that is those resources are not shared with other resource pools) and the maximum value is the maximum value (which can be shared with other pools). By default SQL Server creates two Resource Pools; internal and default. The internal pool is what SQL Server itself uses and the default pool is a …. default pool :-). Resource Pools can be created using T-SQL or using the SQL Server Management Studio.
Workload Groups
Each Resource Pool can have one or more Workload Groups, and the Workload Groups is where the sessions are sent to (by the Classifier, see below). Each Workload Group can be assigned a set of policies and can be used for monitoring. Workload Groups can be moved from one Resource Pool to another. Workload Groups can be created using T-SQL or using the SQL Server Management Studio.
Classification
The Classification of requests/sessions are done by the Classifier Function. The Classifier function (there can be only one) handles the classification of incoming requests and sends them to a Workload Group using your custom logic. The Classifier function can only be created using T-SQL.
Using SQL Server Resource Governor to optimize Search Database usage
So, how can we use the Resource Governor to improve or optimize our SharePoint 2013 performance? One thing (among many) is that we can take a look at how Search crawling affects your farm. While crawling the crawler, part from hammering the web servers being crawled (which you should have dedicated servers for), it also uses lots of SQL Server resources. In cases where you only have one SQL Server (server, cluster, availability group etc) all your databases will be affected by this, and one thing you don’t want to do is to annoy your users during their work with a slow SharePoint farm. What we can do here using the Resource Governor is to make sure that during normal work hours the Search databases are limited to a certain amount of CPU (or RAM).
Configure the SQL Server Resource Governor to limit resource usage of Search databases
The following is one example of how you can configure SQL Server to limit the resource usage of the SharePoint Search databases during work hours and not limit them during night time. All the following code is executed as a sysadmin in the SQL Server Management Studio.
Create the Resource Pools
We need two resource pools in this example – one for sessions using the Search databases under work hours (SharePoint_Search_DB_Pool) and one for sessions using the Search databases during off-work hours (SharePoint_Search_DB_Pool_OffHours). We configure the work hours Resource pool to use at the maximum 10% of the total CPU resources and the Off hours pool to use at the max 80%. In T-SQL it looks like this:
USE master
GO
CREATE RESOURCE POOL SharePoint_Search_DB_Pool
WITH
(
MAX_CPU_PERCENT = 10,
MIN_CPU_PERCENT = 0
)
GO
CREATE RESOURCE POOL SharePoint_Search_DB_Pool_OffHours
WITH
(
MAX_CPU_PERCENT = 80,
MIN_CPU_PERCENT = 0
)
GO
Create the Workload Groups
The next thing we need to do is to create two Workload Groups (SharePoint_Search_DB_Group and SharePoint_Search_DB_Group_OffHours) and associate them with the corresponding Resource Pool:
CREATE WORKLOAD GROUP SharePoint_Search_DB_Group
WITH
(
IMPORTANCE = MEDIUM
)
USING SharePoint_Search_DB_Pool
GO
CREATE WORKLOAD GROUP SharePoint_Search_DB_Group_OffHours
WITH
(
IMPORTANCE = LOW
)
USING SharePoint_Search_DB_Pool_OffHours
GO
After this we need to apply this configuration and enable the Resource Governor, this is done using this T-SQL:
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
Create the Classifier function
The Resource Pools and Workload Group are now created and the Resource Governor should start working. But all the incoming requests are still going to the default Resource Pool and Workload Group. To configure how the Resource Governor chooses Workload Group we need to create the Classifier function. The Classifier function is a T-SQL function (created in the master database) that returns the name of the Workload Group to use.
The following Classifier function checks if the name of the database contains “Search” – then we assume that it is a SharePoint Search database (of course you can modify it to use “smarter” selection). During normal hours it will return the SharePoint_Search_DB_Group and between 00:00 and 03:00 it will return the SharePoint_Search_DB_Group_OffHours group for the Search databases. For any other database it will return the “default” Workload Group.
CREATE FUNCTION fn_SharePointSearchClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @time time
DECLARE @start time
DECLARE @end time
SET @time = CONVERT(time, GETDATE())
SET @start = CONVERT(time, '00:00')
SET @end = CONVERT(time, '03:00')
IF PATINDEX('%search%',ORIGINAL_DB_NAME()) > 0
BEGIN
IF @time > @start AND @time < @end
BEGIN
RETURN N'SharePoint_Search_DB_Group_OffHours'
END
RETURN N'SharePoint_Search_DB_Group'
END
RETURN N'default'
END
GO
This is the core of our logic to select the appropriate Workload Group. You can modify this method to satisfy your needs (you need to set the Classifier to null and reconfigure the Resource Governor, and then set it back and reconfigure again whenever you need to change the method). An important thing to remember is that there can only be one Classifier function per Resource Governor, and this method will be executed for every new session started.
To connect the Classifier function to the Resource Governor there is one more thing that we need to do. First the connection and then tell the Resource Governor to update its configuration:
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_SharePointSearchClassifier)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
Verification
You should now immediately be able to see that the Resource Governor starts to use this Classifier function. Use the following DMVs to check the usage of the Resource Pools and Workload Groups respectively.
SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_workload_groups
As you can see from the image a above the Resource Governor has started to redirect sessions to the SharePoint_Search_DB_Group.
Another useful T-SQL query for inspecting the usage is the following one, which will list all the sessions and which Workload Group they use and from where they originate.
SELECT CAST(g.name as nvarchar(40)) as WorkloadGroup, s.session_id, CAST(s.host_name as nvarchar(20)) as Server, CAST(s.program_name AS nvarchar(40)) AS Program
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
ORDER BY g.name
GO
Summary
You have in this post been introduced to the SQL Server Resource Governor and how you can use it to optimize/configure your SharePoint environment to minimize the impact crawl has on the SQL Server database during normal work hours.
Remember that this is a sample and you should always test and verify that the Resource Pool configurations and the Classifier logic works optimal within your environment.