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.