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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27472 Visits: 3782
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
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
derek.colley
derek.colley
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4028 Visits: 603
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??!Crazy

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.

Chris Metzger
Chris Metzger
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 Visits: 89
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.
Douglas Osborne-229812
Douglas Osborne-229812
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 392
Why do you have MAXDOP set to 1?

Doug
SteveHood79
SteveHood79
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 309
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.
Nicholas Cain
Nicholas Cain
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14550 Visits: 6200
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
timothyawiseman
timothyawiseman
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4126 Visits: 920
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/
Marios Philippopoulos
Marios Philippopoulos
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27472 Visits: 3782
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
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Misha_SQL
Misha_SQL
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2650 Visits: 1037
Interesting idea of using SB for maintenance. I have done a few implementations, but never before considered this particular angle. Now I will !



SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123728 Visits: 18627
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

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