articles >> performance tuning >> Resource Governor in SQL Server 2008 ...

Resource Governor in SQL Server 2008

By : Ashish Kumar Mehta Aug 08, 2008

Page 2 / 2


Create SQL Server Logins to Differentiate Workloads for Resource Governor
Let us now create two new users who will run a workload against SQL Server. The first user is named UserAdhoc, which will be used to execute a stored procedure against the AdventureWorks Database. The second user is named UserAdmin, which will be running mirrored database backups of AdventureWorks database.

Use Master
Go
CREATE LOGIN UserAdhoc WITH PASSWORD = 'UserAdhocPwd', CHECK_POLICY = OFF
CREATE LOGIN UserAdmin WITH PASSWORD = 'UserAdminPwd', CHECK_POLICY = OFF
Go

Use AdventureWorks
Go
EXEC sp_grantdbaccess N'UserAdmin'
Go
EXEC sp_addrolemember N'db_backupoperator', N'UserAdmin'
Go

Use AdventureWorks
Go
EXEC sp_grantdbaccess N'UserAdhoc'
Go
Grant EXECUTE ON DBO.showPersonAddress TO UserAdhoc
Go

Monitoring Resource Governor Utilization
Now you need to establish two different connections to SQL Server 2008. One connection will be using the credentials of UserAdhoc and the second connection will be with the credentials of UserAdmin. Then, you need to open up Performance Counters by going to Start | All Programs | Administrative Tools | Performance. 

You will be capturing how CPU utilization happens when workloads are run against SQL Server 2008 on which Resource Governor is configured. The configuration for the Performance Counters are mentioned below

1. In the Performance object list, click SQLServer:Resource Pool Stats
2. In the list of counters, click CPU usage %
3. In the list of instances, click default, internal, PoolAdhoc & PoolAdmin
4. Allow the Performance Monitor to capture the data and follow the below steps which will capture the workload.

Query to be executed by UserAdmin
The below mentioned TSQL needs to be executed by the User UserAdmin

Backup Database AdventureWorks
To Disk = 'D:\Backups\AdventureWorks1.BAK'
Mirror To Disk = 'D:\Backups\AdventureWorksMirror1.BAK'
Mirror To Disk = 'D:\Backups\AdventureWorksMirror2.BAK'
Mirror To Disk = 'D:\Backups\AdventureWorksMirror3.BAK'
WITH FORMAT;

Query to be executed by UserAdhoc
The below mentioned TSQL needs to be executed by the User UserAdhoc

Use AdventureWorks
Go
exec showPersonAddress '01'
exec showPersonAddress '02'
exec showPersonAddress '03'
exec showPersonAddress '04'
exec showPersonAddress '05'
exec showPersonAddress 'WA'

Performance Counter Results
The black waves in the first performance counter chart represent the CPU usage when UserAdhoc was running the stored procedures. 



The black waves in the below performance counter chart represent the CPU usage when UserAdmin was running the mirrored SQL server database backups. The important thing you should notice here is that towards the end the spikes are high and then again it’s falling down to almost 10%. The spike in CPU usage happens when SQL Server identifies that, at that point in time the UserAdhoc which was running the queries does not require much of CPU and at that instance SQL Server cleverly allow UserAdmin who is taking backups to utilize more CPU to perform its operations. And when UserAdhoc comes back to SQL Server requesting more CPU it provides UserAdhoc the required CPU and memory as it is configured in Resource Pool. 

 

The below mentioned DMV will help you to verify the in memory configuration of the Resource Pools and the Workload Groups

SELECT * FROM sys.dm_resource_governor_resource_pools
Go
SELECT * FROM sys.dm_resource_governor_workload_groups
Go

Conclusion
The Resource Governor Feature of SQL Server 2008 helps DBA’s to monitor and control the CPU and memory utilization with respect to different workload groups.