Using SQL Server 2008 to Improve Report Resources Management

Today’s guest blog is submitted by CRM MVP Jim Steger of Sonoma Partners.

We’ve all had it happen; someone runs a very resource intensive report during the day which slows down the performance of CRM for all the users. This now can happen more frequently with the addition of the Dynamics CRM 4.0 Report Wizard. What if there were a way to be able to manage the server to limit the amount of system resources that are allowed for generating reports? You’re in luck! Now, a new feature in Microsoft SQL Server 2008, allows you to create Resource Pools to manage the maximum amount of resources (CPU utilization and memory utilization) to be used by given processes that you define.

In this example, we’re going to create a Resource Pool that limits any queries being issued from our Reporting Services server to a maximum of 20% CPU utilization and 40% memory utilization. Also, we’ll only enable this feature during business hours (so during the evening, reports can get full system resources).

1. Enable Resource Governor on SQL Server 2008

In SSMS, expand the Management tab and right-click Resource Governor and select Enable.

2. Create a Resource Classifier function.

This function is what does all the assigning of processes to Resource Pools. Below is an example function:

use master
go
create function dbo.ResourceClassifier()
returns sysname with schemabinding
begin
declare @retval varchar(32);
if suser_sname() = 'DEV\ANAKOTA$' and
datepart(hour, getdate()) >= 6 and
datepart(hour, getdate()) < 19
set @retval = 'reports'
else
set @retval = 'default';
return @retval;
end
go

alter resource governor with (classifier_function = dbo.ResourceClassifier) 
go

alter resource governor reconfigure 
go

This is just a regular SQL Server function and the return values must be one of Workload Groups you created earlier. In this example, we check for the user name of “DEV\ANAKOTA$” (the user that Reporting Services uses to logon to the SQL Server in our example). Next we check the current time, if it’s within business hours (6am – 6:69pm), then we allocate this query to the “reports” Resource Pool, thereby making sure it won’t overload the SQL Server. Any other query is then sent to the “default” Resource Pool.

NOTE: Be sure to change ‘DEV\ANAKOTA$’ in the above script to use the appropriate user name for the reporting services accessing SQL. To determine the user name to compare, I ran SQL Profiler, ran a report from CRM, and captured what user was issuing the query from Reporting Services. There are also other system functions you can use, see http://technet.microsoft.com/en-us/library/bb933865.aspx for more detailed information on writing a classifier function.

3. Create and configure Resource Pool.

Right-click on Resource Governor again and select New Resource Pool…

In the Classifier function name dropdown list, ensure that selection is your newly created function [dbo].[ResourceClassifer].

Then, in the resulting Resource Governor Properties window, create a new resource with the name of “reports”. Then enter the maximum CPU% and Memory% (in my example I set the values to 20 and 40 respectively).

Select the newly created “reports” Resource Pool that was created above. In the Workload Group for this resource pool grid, enter “reports” for the name of the Workload Group. The final result should look similar to the screen below. Click OK when you’re done.

Keep in mind that by limiting the resources available to a report, you are going to slow down the execution of those reports. Be aware of any SLA’s or other business needs before implementing this solution. Users may have specific business needs at certain times of the day or week to generate reports as quickly as possible. If you need faster performance time on reporting without affecting the transactional database, consider moving the reporting database to a separate SQL Server.

Using the new Resource Governor in SQL Server 2008 can be a quick and easy way to ensure that report processing won’t capture the majority of resources on your transactional SQL Server database.

Cheers,

Jim Steger and Sonoma colleague and author, Rob Jasinski

Tags