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 @ 3:01 AM
Points: 550, Visits: 1,041
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: Thursday, April 17, 2014 2:42 PM
Points: 1,851, Visits: 3,575
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 2
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
Posted Monday, November 11, 2013 3:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:51 PM
Points: 22, Visits: 385
Marios, I've setup the database and ran scripts in the order they appear in supplementary material, but receiving the following error when triggered the job "dba - updStats - ServiceBroker"....

Executed as user: xxxxx. Invalid object name '//UTILITY/UpdStats/InitiatorService'. [SQLSTATE 42S02] (Error 208) Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. [SQLSTATE 25000] (Error 266). The step failed.

Would you be able to assist me with this situation, please...!!
Post #1513282
Posted Tuesday, November 12, 2013 3:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
Why the database needs to be set "trust worthy" in case of SB ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1513400
Posted Tuesday, November 12, 2013 4:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:42 PM
Points: 1,851, Visits: 3,575
srinath.m (11/11/2013)
Marios, I've setup the database and ran scripts in the order they appear in supplementary material, but receiving the following error when triggered the job "dba - updStats - ServiceBroker"....

Executed as user: xxxxx. Invalid object name '//UTILITY/UpdStats/InitiatorService'. [SQLSTATE 42S02] (Error 208) Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. [SQLSTATE 25000] (Error 266). The step failed.

Would you be able to assist me with this situation, please...!!


Hi, it doesn't look like this object has been created in 02_ServiceBrokerObjects.sql:

USE UTILITY;
GO

CREATE SERVICE [//UTILITY/UpdStats/TargetService]
ON QUEUE UpdStats_TargetQueue ([//UTILITY/Contract]);
GO

Can you run the following code and send me the results?

USE [UTILITY];

SELECT
S.name AS [Service]
, Q.name AS [Queue]
, C.name AS [Contract]
FROM
sys.services AS S
INNER JOIN
sys.service_queues AS Q
ON
S.service_queue_id = Q.[object_id]
LEFT OUTER JOIN
sys.service_contract_usages AS U
ON
S.service_id = U.service_id
LEFT OUTER JOIN
sys.service_contracts AS C
ON
U.service_contract_id = C.service_contract_id;



__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
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 #1513416
Posted Tuesday, November 12, 2013 4:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:42 PM
Points: 1,851, Visits: 3,575
Bhuvnesh (11/12/2013)
Why the database needs to be set "trust worthy" in case of SB ?


Good question, I don't have a good answer to it.

I mention this briefly in Part 2.

You will get this error in sys.transmission_queue:
One or more messages could not be delivered to the local service targeted by this dialog.


It seems the db on which Service Broker is running needs to be trustworthy to be able to access objects on other databases through Service Broker.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
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 #1513418
Posted Tuesday, November 12, 2013 9:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:51 PM
Points: 22, Visits: 385
Good morning, Marios!! Here is the result of the query:

Service Queue Contract
//UTILITY/UpdStats/InitiatorService UpdStats_InitiatorQueue NULL
//UTILITY/UpdStats/TargetService UpdStats_TargetQueue //UTILITY/Contract
http://schemas.microsoft.com/SQL/Notifications/EventNotificationService EventNotificationErrorsQueue NULL
http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService QueryNotificationErrorsQueue NULL
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker ServiceBrokerQueue http://schemas.microsoft.com/SQL/ServiceBroker/ServiceEcho
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker ServiceBrokerQueue http://schemas.microsoft.com/SQL/ServiceBroker/ServiceDiagnostic

Any clue, where did I go wrong :)
Post #1513519
Posted Tuesday, November 12, 2013 10:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:42 PM
Points: 1,851, Visits: 3,575
srinath.m (11/12/2013)
Good morning, Marios!! Here is the result of the query:

Service Queue Contract
//UTILITY/UpdStats/InitiatorService UpdStats_InitiatorQueue NULL
//UTILITY/UpdStats/TargetService UpdStats_TargetQueue //UTILITY/Contract
http://schemas.microsoft.com/SQL/Notifications/EventNotificationService EventNotificationErrorsQueue NULL
http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService QueryNotificationErrorsQueue NULL
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker ServiceBrokerQueue http://schemas.microsoft.com/SQL/ServiceBroker/ServiceEcho
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker ServiceBrokerQueue http://schemas.microsoft.com/SQL/ServiceBroker/ServiceDiagnostic

Any clue, where did I go wrong :)


Hmm, that output is correct.

Can you go through the tips I mention in part 2 and see if you can identify the issue?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
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 #1513564
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse