Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Turbocharge Your Database Maintenance With Service Broker: Part 1 Expand / Collapse
Author
Message
Posted Monday, July 23, 2012 6:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 505, Visits: 970
Hi,
Thanks for this article!
I've changed some of our maintenance plans using SB.

Question: How can I limit ONLY the SB queries to use MaxDop=1?
I don't want to make this server wide...

I've tried using Resource governor, but that's not picking up service broker... :-(

Post #1333729
Posted Tuesday, July 24, 2012 4:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 1,825, Visits: 3,484
Wandrag (7/23/2012)
Hi,
Thanks for this article!
I've changed some of our maintenance plans using SB.

Question: How can I limit ONLY the SB queries to use MaxDop=1?
I don't want to make this server wide...

I've tried using Resource governor, but that's not picking up service broker... :-(



Hi,

The only way I can think of is by wrapping the whole SB process in sp_configure statements, as shown below.
"UPDATE STATISTICS" does not take a MAXDOP hint, unfortunately, but "ALTER INDEX...REBUILD" does, so in the latter case you could add the hint in the SB code without having to do it instance-wide.

Of course, doing it instance-wide means that all activity for the duration of the SB process will run using the current MAXDOP setting, but I cannot think of a better alternative.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

<SB process goes here>

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO



__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1334349
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse