SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Turbocharge Your Database Maintenance With Service Broker: Part 1


Turbocharge Your Database Maintenance With Service Broker: Part 1

Author
Message
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11866 Visits: 3764
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



__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
srinath.m
srinath.m
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 562
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...!!
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12532 Visits: 4077
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;-)
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11866 Visits: 3764
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;



__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11866 Visits: 3764
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. w00t

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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
srinath.m
srinath.m
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 562
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 Smile
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11866 Visits: 3764
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 Smile


Hmm, that output is correct.

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

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
D-SQL
D-SQL
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 806
Hi I know this post is older bu had some questions. I'm not an expert at this, i have been trying to get this set up to test, I am able to get the list into the table but then nothing after that. I run the test query and get Run Duration NULL. I have took it down and re-did a few times. Just wondering if you had any ideas, I'm not getting any errors either.

I do have service broker set up on the same database gathering schema changes but i didn't see any conflict.

Thanks in advance
D-SQL
D-SQL
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 806
This is great, I am new to service broker and wanted to get some questions answered. I set this up and was trying to configure it to run on all the databases. I downloaded all the material and saw the job. I'm assuming in the 2nd step is where I would at the syntax for all databases. Is that correct or does anything else need modified. Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search