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 123»»»

Turbocharge Your Database Maintenance With Service Broker: Part 1 Expand / Collapse
Author
Message
Posted Sunday, July 8, 2012 4:22 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:13 AM
Points: 1,860, Visits: 3,597
Comments posted to this topic are about the item Turbocharge Your Database Maintenance With Service Broker: Part 1

__________________________________________________________________________________

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 #1326629
Posted Monday, July 9, 2012 2:44 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1326709
Posted Monday, July 9, 2012 6:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:23 AM
Points: 50, Visits: 88
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.
Post #1326797
Posted Monday, July 9, 2012 1:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:09 AM
Points: 76, Visits: 219
Why do you have MAXDOP set to 1?

Doug
Post #1327095
Posted Monday, July 9, 2012 1:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 9:27 AM
Points: 83, Visits: 297
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.
Post #1327102
Posted Monday, July 9, 2012 2:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:29 PM
Points: 2,007, Visits: 6,077
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.



Shamless self promotion - read my blog http://sirsql.net
Post #1327124
Posted Monday, July 9, 2012 5:43 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #1327215
Posted Tuesday, July 10, 2012 3:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:13 AM
Points: 1,860, Visits: 3,597
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.


__________________________________________________________________________________

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 #1327374
Posted Tuesday, July 10, 2012 9:18 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: Monday, July 21, 2014 8:42 AM
Points: 535, Visits: 750
Interesting idea of using SB for maintenance. I have done a few implementations, but never before considered this particular angle. Now I will !


Post #1327668
Posted Wednesday, July 11, 2012 5:27 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,230, Visits: 14,940
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1328624
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse