Home > Configuration, Performance, Resource Governor, SQL Agent, SQL Server > Targeting CHECKDB SQL Agent jobs for resource governing

Targeting CHECKDB SQL Agent jobs for resource governing

Some time ago I was looking into the performance of DBCC CHECKDB on one of our servers and came to the conclusion that the best Degree of Parallelism (DOP) for CHECKDB was not the same as our server level setting and since CHECKDB does not accept a DOP hint the only option to limit DOP when the server level setting is not appropriate is to use the resource governor.

In our environment CHECKDB is scheduled using SQL Agent and I could have chosen to simply target all SQL Agent jobs and have them all assigned to a single pool but this was not appropriate for all my SQL Agent jobs so I needed a solution targeting individual jobs. I  thought this would be a relatively straight forward task but there were some challenges so i wanted to share them and my solution.

Challenges

The resource governor classifier function assigns connections to workload groups and it needed to be coded to do the following.

  1. Identify a running job
  2. Lookup the job name
    You may ask why we want to know the job name, basically we have a strict naming convention for jobs which means we can determine the purpose of a job from its name which makes the job name perfect for determining the workload group.

Solution

Identify a running job

Finding a running job proved to be relatively straight forward because you can identify a SQL Agent job from the program name in sys.dm_exec_sessions and it takes the form shown below.

SQLAgent – TSQL JobStep (Job <Transposed_GUID> : Step 1)

The <Transposed_GUID> part can be translated into the GUID for the job id held in msdb.dbo.sysjobs. For a clear explanation on translating the transposed guid see Jonathan Kehayias post.

Finding the job name

When the resource governor classifier function runs it has to know the job name and you want it to run as fast as possible because you don’t want it to be a bottleneck during the server logon process.

The challenge here is that the classifier function is schema bound and resides in master so you can not reference the msdb database to get the job name  The way around this problem is to create a lookup table for job ids & names in the master database which is refreshed once a day using a job. The other benefit of this approach is that you should not get any contention on the table and as such not cause bottlenecks in the logon process.

Conclusion

You can download the script to implement the complete solution for targeting SQL Agent jobs from here. The script is well commented and self explanatory, you can also easily amend the classifier function to assign to other jobs to different groups or even assign differently if its not a SQL Agent job.

One of the nice fringe benefits you also get with using the resource governor is that a number of usage statistics are captured in sys.dm_resource_governor_resource_pools and sys.dm_resource_governor_workload_groups. Examples of these statistics are below.

Untitled

Finally, I am not going to cover the performance results of the DOP testing i did her but if you want more information regarding DOP & CHECKDB see Paul Randal’s posts DBCC CHECKDB scalability and performance benchmarking on SSDs.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: