July 8, 2012 at 4:22 pm
Comments posted to this topic are about the item Turbocharge Your Database Maintenance With Service Broker: Part 1
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 9, 2012 at 2:44 am
Thank you for an excellent article, my knowledge of SB is hazy and it's nice to see a practical application of this feature. The only disappointment (NOT from you!) is that SB might not be suitable for cases where I/O is a problem - sadly for me, I/O is indeed a problem so I'm hesitant about doing anything that's going to tie it up (using parallel threads) for any length of time.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
July 9, 2012 at 6:13 am
Or Developers need to include this type of index maintenance in the updater. Our installer actually does this for us (Index Rebuild) after every major update - and it even offers the option to skip the step in the installer UI. If Developers would be smarter about how they execute application and database updates this would then be a non-issue for the sysadmin and\or DBA.
July 9, 2012 at 1:23 pm
Why do you have MAXDOP set to 1?
Doug
July 9, 2012 at 1:36 pm
If you have parallelism, you also have the added cost of parallelism. In this case, you're taking two completely separate processes and running them at the same time, which eliminates that cost while having two threads doing the work at the same time. If you want to use more threads, just have more processes run simultaneously.
The flaw in this logic is that if you have a large table that takes 90% of your time and typically is done using 4 threads then you could actually take longer to run the entire process. That doesn't stop you from saying that this big table can be handled separately and the rest of your tables go through service broker. However, if you don't put MaxDOP down and you have 5 iterations going at once, you could easily have 20 threads starving each other and every other process running for CPU time.
July 9, 2012 at 2:04 pm
The article is good but it needs to address the flaws of attempting to do this, for example how threading index rebuilds or stats rebuilds could seriously impact the i/o performance of your server and lead to significantly slower times than running the same thing in a serial fashion, or the inherent security concerns around setting a databases trustworthy setting to on.
July 9, 2012 at 5:43 pm
Excellent article. I have not really played with service broker very much, so it was interesting to see it put into action.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
July 10, 2012 at 3:51 am
Thank you all for your comments so far.
Derek and Timothy:
Thank you for the kind words.
Chris:
Yes, developers definitely have a role to play in making sure stats and fragmentation are addressed during major releases/upgrades.
After all, they know best which tables/indexes are affected by these large changes and could incorporate code to update stats or defrag the impacted objects. Even in that case though, using multi-threading with Service Broker would help speed up this process. However, this does not cover major data changes caused not by an application upgrade but because of some business-related process that the developer is not aware of. An example is a new business acquisition, for which an application change was not needed. This is where the DBA is called to make sure database maintenance is done on a set schedule.
Doug:
MAXDOP=1 is set by us on OLTP servers in our environment. This is to prevent CXPACKET (parallelism) waits and to allow for as many requests as possible to make use of the available CPUs. Developers are free to insert hints in their code to circumvent this restriction, if they feel their code would behave better with parallel execution. Another option would be to raise the "max threshold of parallelism", so queries do not parallelize as easily as with the default setting of 5. We have chosen to go with the MAXDOP option for now, and have not noticed any ill effects as a result.
Steve:
Thank you for the explanation to Doug's question.
Nicholas:
Yes, I/O can be a serious problem. I'm assuming that db maintenance is done during periods of otherwise "low" activity, so business processes are not impacted. So, as long as there is an overall reduction in the maintenance window as a result of multi-threading, I don't find I/O too concerning. With regards to setting the UTILITY db as TRUSTWORTHY=ON, I agree that this is something I need to be more concerned about. This is a DBA-owned database, and I need to pay more attention as to who is allowed to access it and what code is allowed to run there. One option that comes to mind would be to turn on the TRUSTWORTHY bit right at the start of the service-broker run and turn it off right at the end. That would reduce the risk of some other process attempting to highjack the database for a malicious purpose.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 10, 2012 at 9:18 am
Interesting idea of using SB for maintenance. I have done a few implementations, but never before considered this particular angle. Now I will !
July 11, 2012 at 5:27 pm
I like the article and the concepts you have laid out for us.
Thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 24, 2012 at 4:09 am
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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2013 at 3:29 pm
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...!!
November 12, 2013 at 3:55 am
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;-)
November 12, 2013 at 4:23 am
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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 12, 2013 at 4:35 am
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[/url].
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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply