Turbocharge Your Database Maintenance With Service Broker: Part 1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Marios... » Turbocharge Your Database Maintenance With...

12 posts, Page 1 of 2

1 2 »»

Turbocharge Your Database Maintenance With Service Broker: Part 1

Rate Topic

Display Mode

Topic Options

Author

Message

Marios Philippopoulos

Posted Sunday, July 08, 2012 4:22 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

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

derek.colley

Posted Monday, July 09, 2012 2:44 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:40 AM
Points: 496, Visits: 583

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.

Post #1326709

Chris Metzger

Posted Monday, July 09, 2012 6:13 AM

Valued 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

Douglas Osborne-229812

Posted Monday, July 09, 2012 1:23 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:58 AM
Points: 47, Visits: 168

Why do you have MAXDOP set to 1?

Doug

Post #1327095

SteveHood79

Posted Monday, July 09, 2012 1:36 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 12:01 PM
Points: 32, Visits: 195

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

Nicholas Cain

Posted Monday, July 09, 2012 2:04 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:27 PM
Points: 2,007, Visits: 6,040

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

timothyawiseman

Posted Monday, July 09, 2012 5:43 PM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900

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

Marios Philippopoulos

Posted Tuesday, July 10, 2012 3:51 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

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

mishaluba

Posted Tuesday, July 10, 2012 9:18 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525, Visits: 617

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

SQLRNNR

Posted Wednesday, July 11, 2012 5:27 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

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 »

12 posts, Page 1 of 2

1 2 »»

Permissions