Resource Governor in SQL Server 2008
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.
CREATE LOGIN UserAdhoc WITH PASSWORD = ‘UserAdhocPwd’, CHECK_POLICY = OFF
CREATE LOGIN UserAdmin WITH PASSWORD = ‘UserAdminPwd’, CHECK_POLICY = OFF
EXEC sp_grantdbaccess N’UserAdmin’
EXEC sp_addrolemember N’db_backupoperator’, N’UserAdmin’
EXEC sp_grantdbaccess N’UserAdhoc’
Grant EXECUTE ON DBO.showPersonAddress TO UserAdhoc
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:BackupsAdventureWorks1.BAK’
Mirror To Disk = ‘D:BackupsAdventureWorksMirror1.BAK’
Mirror To Disk = ‘D:BackupsAdventureWorksMirror2.BAK’
Mirror To Disk = ‘D:BackupsAdventureWorksMirror3.BAK’
Query to be executed by UserAdhoc
The below mentioned TSQL needs to be executed by the User UserAdhoc
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
SELECT * FROM sys.dm_resource_governor_workload_groups
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.
Pages: 1 2