Service Broker Whitepaper on MSDN: the 150 trick

March 25th, 2009

A new SQL Customer Advisory Team whitepaper was published recently: Service Broker: Performance and Scalability Techniques authored by Michael Thomassy.

The whitepaper documents the experience of a test done in Microsoft labs that measured the message throughput attainable between three initiators pushing data to a target. This scenario resembles a high scale ETL case. The test was able to obtain a rate a nearly 18000 messages per second, which is a rate that can satisfy most high load OLTP environments. To obtain this rate Michael and his team had to overcome the high contention around updates of the dialogs system tables. He presents a very interesting trick: create 149 dialogs that remain unused and only use every 150th. This way the updates done on the system tables occur on different pages and the high PAGELATCH contention on the page containing the dialog metadata during SEND is eliminated. A very clever trick indeed. But this is a typical OLTP insert/update trick and that is the very point of the whitetpaper: that typical OLTP techniques can and should be applied to Service Broker performance tuning.

DatabaseJournal Tutorial

March 24th, 2009

Marcin Policht has concluded his series of articles dedicated to Service Broker in the Database Journal. Although the articles are part of a larger SQL Express coverage, they are not at all specific just to Express. I highly recommend them as a very good introduction to everything Service Broker on SQL Server 2005.

  1. Introduction to Service Broker
  2. Implementing Basic Service Broker Objects
  3. Implementing Service Broker Conversation – I
  4. Implementing Service Broker Conversation – II
  5. Distributed Service Broker Environment – Endpoints
  6. Distributed Service Broker Environment – Routing
  7. Distributed Service Broker Environment – Conducting Dialogs
  8. Configuring Certificate-based Authentication in SQL Server Express’ Distributed Service Broker Environment
  9. Establishing Distributed SQL Server Express’ Service Broker Conversations Using Certificate-based Authentication
  10. Configuring Transport Encryption in SQL Server 2005 Express Service Broker Conversation
  11. Configuring Full Dialog Security in SQL Server 2005 Express Service Broker Conversation
  12. Conducting Service Broker Conversation Using Full Dialog Security in SQL Server 2005 Express Service
  13. Configuring Anonymous Dialog Security in SQL Server 2005 Express Service Broker Conversation
  14. Service Broker Activation in SQL Server 2005 Express Edition
  15. Security Context of Service Broker Internal Activation
  16. Service Broker Transactional Support in SQL Server 2005 Express Edition
  17. Service Broker Poison Message Handling

Things I know now: blogging can get you into a email ponzi scheme

March 20th, 2009

I got tagged by Adam Machanic. Although my blog looks like a DBA blog, I am a pure breed developer, so here is what I know now:

You can’t fix what you can’t measure

Successful projects dedicate quite a large amount of resources to instrumentation, profiling and monitoring. Anywhere between 5 and 10% of the used resources should be an acceptable margin to run code that generates logs, reports performance counters, monitors responsiveness, aggregates and consolidates run time data. I cannot stress the importance of properly instrumenting your code to support this. It has been said before that any optimization or troubleshooting should start from measurements, not from guesswork. Your duty is to put those measurements in place, expose information your users can rely on in order to make informed decisions. Don’t cut corners and eat your dog food: use the instrumentation you added to troubleshoot problems, don’t fire up a debugger and start stepping through the sources. If you cannot figure out the cause of a problem from the tracing and logs, your customers won’t be able to do it either. Nor will you be able to troubleshoot on site at a customer deployment.

My favorite instrumentation tools are the Performance Counters. I sprinkle them generously in my code. For my own use I have developed XSLT stylesheets I use to quickly generate new performance counters code for any application I write starting from a simple XML definition file. The Windows infrastructure allows me to collect them over time in predefined performance logs. Being able to fire up Perfmon and show how your application performs in real time always impresses your customers.

If is not tested, it doesn’t work correctly

I am yet to see a piece of code that was bug free from the first iteration. Testing is always required to validate that the development team correctly understood the requirements, that the assumptions made by the code author where actually correct, that the requirements correctly captured the end user needs. Besides this functional testing, the stress testing never ever fails to produce bugs. Corner cases, exception code paths and unexpected error returns are always guaranteed to reveal bugs and defects in your code.

Testing requires a different mind set than the development work. Seldom is a good developer a good tester too. I have witnessed often how embracing a TDD style is confused with actually testing your product. Unfortunately this is not the case. Besides the obvious case of the fox guarding the hen house that is developers writing tests for their own code, the unit tests produced by TDD are not equivalent to the end-to-end use case testing that is required to validate that your product is actually working and doing what is expected to do.

Your intuition is often wrong

Your mind is incredibly powerful at analyzing things that happen at everyday pace. But your intuition is most of the times wrong when it comes to things that happen really fast or on a really large scale. There is no chance for that record to change while you released the lock on it? It will change under stress. Your application works perfectly on your dual core laptop? Try it on a 64 way machine and you’ll see that lock convoys are not a legend, they do happen.

Writing high scale and high performance programs is a league of its own. Most techniques you learn prior to attempting something really big don’t work. Everything is different: memory allocation, thread management, how you do your I/O. Fortunately there are some excellent articles covering these topics and my favorite ones are the ones from Rick Vicik.

Who’s am I tagging? I’m only gonna choose one person, thus in violation of this game rules, but he is a very special person and if I can get him back into the blogosphere, is well worth it: Slava

Iowa SQLPASS chapter meeting Server-to-Server Service Broker

March 10th, 2009

If you are in the area of Cedar Rapids or Iowa City, IA you can attend the SQLPASS newly formed chapter there and listen tonight to a presentation on Service Broker. Brian Duhn from GoDaddy.com will present “Server-to-Server Service Broker”.

More details at http://380pass.org/ or at http://sqlfool.com/2009/03/380pass-meeting-tonight/.