Killing me softly with Service Broker…

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

A few days ago, I found a very interesting behavior regarding Service Broker on a customer system. The main thing is that your activation stored procedure is not allowed to block for a very long time, otherwise you will impact the health of your whole SQL Server instance. In this blog posting I want to demonstrate this behavior to you. In the first step I have to create the whole necessary Service Broker infrastructure objects, nothing special with this – I’ve just stolen that code from Chapter 4 of my Service Broker book:

As you can see from this listing, the target queue is enabled for activation, means that the ProcessRequestMessages stored procedure automatically started by a Queue Monitor as soon as a new message arrives on that queue. The stored procedure itself just inserts the received message in the table ProcessesMessages. Now imagine that this table has a lock from some other query that is executing inside your database:

I know that TABLOCKX is a little bit too much, but I just want to demonstrate the problem to you. When you afterwards send a new message, the activation stored procedure gets started, and finally blocks because the necessary IX lock for the INSERT statement can’t be acquired:

You can now check very easily that your activated stored procedure just blocks and is doing nothing inside SQL Server:

The crazy thing about that behavior is, that you can’t just stop SQL Server, you have to do a SHUTDOWN WITH NOWAIT, otherwise SQL Server will not stop anymore…:

Moral of the story: make sure that you release your locks as fast as possible. The other problem with locking in Service Broker is that it yields to high LOCK_HASH spinlocks, which will burn down a lot of CPU cycles without doing anything in SQL Server – it’s a very crazy scenario and in the first step very hard to explain. You can also crosscheck this with sys.dm_os_spinlock_stats. In that scenario the CPU is up to 100% but doesn’t do any work regarding Service Broker – very contraprodutive! Thanks to Thomas Kejser for that tip regarding the LOCK_HASH spinlock.

Thanks for reading

-Klaus

Do you like this post? Why not share it with the SQL Server community?

Comments

  1. Interesting problem. In your customers case what caused the lock to be held in the first place? I would have expected use of the RECIEVE command which should row lock (I haven’t tested but would expect this). Therefore the entire table should never lock.
    Regarding the shutdown I assume this was just a side effect and that you could just kill the blocking spid?
    I’m doing a lot of work with service broker right now, hopefully I won’t see this issue!
    Thanks

  2. Hello Simon,

    The root cause was an uncommitted transactions. The transaction logic in the activated stored procedure was written wrong.

    -Klaus