SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Service Broker, Triggers, CLR. and SSIS Threading Framework

I have been trying for a few days to implement a Service Broker implementation to, service the threading framework I built for SSIS.  When you build the threading framework you have a set number of threads running and I have been looking for a way to modify the number of running threads to accommodate server load.  There are many ways to do this, such as have the threads check a table and turn on/off, but I don't care for that method.  The issue I have is you need to have the thread, once it is 'asleep' to wake up every so often and poll the table.  That's a waste of resources in my opinion.  I would prefer to have a callback mechanism where the thread is put to sleep and woken up when it is time to come back online.  To do this I choose Service Broker.  Threads will 'wait' on a queue and wake up when they should or never if they are to be kept offline.  No table polling, no contention, no wasted resources.

I was all Gung-Ho.  I had tested my Service Broker implementation with two procedures to simulate a request to throttle the engines.  I had a slight set-back, but @mrDenny was kind enough to jump in a give me the necessary information that every resource I could find failed to explain.  I was ready to implement my Service Broker trigger based implementation.

Whoa did the frustration ensue.  The concept was simple.  I had a table which contained a listing of what you could request of the framework, which at the moment is a certain number of engines on/off, based upon the number of engines in the framework (can't ask for more than are currently built).  Realizing that the engines could only check the queue once they finished their work, I didn't want to have a person request taking X engines offline and wait N minutes for the request to complete.  I wanted the table to update and return immediately then have the Service Broker asynchronously handle waiting. What followed was a miserable fail.

I had the trigger built exactly like my test procedures, yet every time I did an update to the table, the trigger would hang on sending the message, not on the wait.  I spent a few hours racking my brains trying to understand this.   I was checking queues, I was checking error logs (thanks to@AaronBertrand), but couldn't figure out why.  I hit the web and found there were a few people in the same boat as I was and they had no answers to their questions.  Dig a little deeper and I found the only way to make a trigger asynchronous is do make a CLR trigger.  My next task.

Spent a few more hours pouring through the online msdn library looking for clr information (here is a good starting point http://msdn.microsoft.com/en-us/library/ms131093.aspx) and TADAH!. I created a clr trigger and it works like a charm.  I do an update to the table and I get an instant return, allowing the user to continue on.

I now have to integrate this into threading framework, but that won't be too hard.  The framework uses a stored procedure to dole out the work, so I just have to put the SB code in there, vs having to modify my SSIS package.  If you are unfamiliar with the threading framework I designed for SSIS, you can download the deck here (http://josef-richberg.squarespace.com/downloads/) and a recorded presentation here http://appdev.sqlpass.org/MeetingArchive/tabid/2005/Default.aspx.

I am not sure yet if this will be a blog post (it will be very large) or an article (more to come on that).


This blog is syndicated from SSIS - SQL Server Tidbits(http://www.josefrichberg.com/)


Posted by Simon Sabin on 7 February 2010

Why do you need a clr trigger. Surely you can just use a TSQL trigger and pop a message in a service broker queue?

Posted by sqlrunner on 7 February 2010


 I am taking the messages off of one queue and placing them on another.  The second queue might take say 5 min to process the message, since a trigger is within a transaction that secondary queue processing would be within the trigger window and I didn't want that.  The only way to process this outside of the window of the trigger is to make an asynchronous trigger which allows parallel connections, decoupling one from the other.

Posted by Jason Brimhall on 8 February 2010

Nice to see you got that going.  I saw the tweets back and forth between you and Denny.

Posted by sqlrunner on 8 February 2010


 I found something interesting.  It seems as though in SQL Server 2k5 sp1, the trigger hangs on the receive whether or not it is (T-SQL or CLR).  Have not found a way around, so I am modifying my approach.  I will still show a T-SQL version and a CLR version (for both trigger and sp) and continue digging into this issue.

Posted by RBarryYoung on 14 February 2010

Hmm, the Service Broker SEND command should never *ever* hang.  I have used it many, many times to effectively implement asynchronous triggers, and I have never had it stall on the SEND or had to write CLR to get it to work correctly.  I also do not think that the SB transactional features extend through the queues in exactly the way that you are saying here.  You should be able to dispatch to a secondary queue through an intermediate service procedure without any CLR and without any stalls in the original trigger.  I do this all the time.

Posted by sqlrunner on 15 February 2010


 The problem is both send/receive.  I found two issues (CLR or T-SQL).  The trigger will fire the send and then hang on the receive.  When I check the queues, the trigger has sent a message out and is waiting for a message to come back (two different queues).  The issue seems to be the queues it is waiting on are phantom queues.  One of the ids shows all 0s instead of an id number.  If that code is copy/pasted out of a trigger and put in TSQL, it works perfectly.

Leave a Comment

Please register or log in to leave a comment.