Resource governor

Question

Resource governor

have2much

Mr or Mrs. 500

Points: 560
More actions
January 8, 2016 at 9:12 am

#306766

Hi,
I am new to sql server and trying to use resource governor to limit maxdop for DBCC CHEKDB and if possible for other maintenance job like backup and optimizing index in SQL 2012. Job is executing step as sqlcmd.
All these jobs are categorized as "Maintenance". My aim is to create classifier function to evaluate the job request and if it matches this category then reduce the MAXDOP to 1.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 1

have2much (1/8/2016)
Hi,
I am new to sql server and trying to use resource governor to limit maxdop for DBCC CHEKDB and if possible for other maintenance job like backup and optimizing index in SQL 2012. Job is executing step as sqlcmd.
All these jobs are categorized as "Maintenance". My aim is to create classifier function to evaluate the job request and if it matches this category then reduce the MAXDOP to 1.

Out of curiosity, why do you want to do that?

😎

have2much Mr or Mrs. 500 Points: 560 More actions · Answer 2

I want to do this because dbcc command waits for very long time with waittype CXPACKET, when I tried running it with MAXDOP 1 all the wait time is gone and the command finished in not time.

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 3

have2much (1/11/2016)
I want to do this because dbcc command waits for very long time with waittype CXPACKET, when I tried running it with MAXDOP 1 all the wait time is gone and the command finished in not time.

What is the cost threshold for parallelism setting on the server? If it is set to the default 5 then I suggest you start by changing the setting to a higher value, normally I would start at 50 and monitor the results.

😎

have2much Mr or Mrs. 500 Points: 560 More actions · Answer 4

have2much

Mr or Mrs. 500

Points: 560

January 12, 2016 at 1:36 pm

#1851414

Yes, it is already set to 50.

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 5

have2much (1/12/2016)
Yes, it is already set to 50.

Assume you are running Enterprise edition, otherwise DBCC CHECKDB does not do parallel execution and obviously the Resource Governor is also an Enterprise only feature. Suggest you look at this document from the sqlskills.com site, it's a good starting point.

😎