Disadvantages of SQL Server Service Broker [closed]

Question

I have been doing r&d for the scope of SQL Server Service Broker to replace current messaging solution MSMQ. I want to know disadvantages of SQL Server Service Broker in contrast to MSMQ for following criteria.

1. Development
2. Troubleshooting
3. Performance (let's say we need to process 100,000 messages daily, having avg size around 25 KB)
4. Scalability

Fuse

Answer 1

I've used Service Broker in my current project, having previously used MSMQ (brokered by MassTransit) with great success. I was initially dubious about using Service Broker, but I have to admit it has performed very well.

If you're using the Publish / Subscribe model, then I would use Message Queueing every time (though I would use RabbitMQ over MSMQ if the project allowed) but when you just want to chew through a stack of data and persist it to Sql Server, then Service Broker is a great solution: the fact it's so 'close to the metal' is a big advantage.

Development

Service Broker requires a lot of boilerplate, which is a pain, but unless you're planning on having lots of different queues it's manageable. Sql Server projects in Visual Studio take a lot of the pain of deploying away.

Troubleshooting

Service Broker is a black box - messages go in, and they usually come out, but if they don't then troubleshooting can be problematic, and all you can do is query the system views - and sometimes you simply can't find out what has gone wrong. This is annoying, but MSMQ has the same kind of issues..

Performance

Service Broker performance is excellent for our use case (see comment section below for discussion). We are processing a lot more than 100,000 messages per day, more than 30,000 per hour at our SLA load, and our message sizes are large. I would estimate we process close to 100,000 messages per hour during heavy load testing.

For best performance I would advise you to use a Dialog Pool like this one ¹ as creating a Service Broker dialog can be an expensive operation.

You will also want to use the Error Handling procedures detailed by Remus Rusanu. (If you do use Service broker, you might as well read everything Remus has written on the subject before you start, as you'll end out reading it eventually!)

Scalability

You can certainly use more than one server to scale up if required, though we haven't had to do so, and from the load size you mention I don't think you would need to either.

I don't think I have really managed to answer your question, as I haven't highlighted enough disadvantages of Service Broker queues. I would say the impenetrable nature of its internal workings is the thing that most annoys me - when it works, it works very well, but when it stops working it can be very hard to figure out why. Also, if you have a lot of messages in a queue, using ALTER QUEUE takes a very long time to complete.

Not knowing how you are using MSMQ also makes it different to fairly compare the two technologies.

¹ Recreated in a gist as the original url is now "disabled" and the page isn't in the internet archive. Eventually found a copy here

Hued

I do not consider 27 messages per second as good performance. – Horner

In this case, 27 messages represents writing 27,000 (large) rows to a SQL table per second, as each message has 1000 data items in it. It may be possible to get higher insert performance, but we have reached our target with room to spare, so it seems unnecessary – Hued

Isn't 100 000 msgs/hour same as 100000/60/60=28msgs/sec? – Horner

I'm not disagreeing with your maths, I just don't see your point. – Hued

My point was that 100k/hour isn't excellent performance. My client uses SSB a lot (between 5-100 messages per second) and get into trouble frequently when queues grow. The entire service broker tend to go slow then. – Horner

For instance, one application uses SSB to enqueue replies to certain types of requests. The generated data is about 600kb per message. Once 40k messages are enqueued it can take several seconds per message receive. – Horner

@jqauffin, what recovery model is your database running? You will get better performance if you switch to simple. You will lose point in time recovery but you don't have that with MSMQ either. Also, how many reader instances do you have? If you only have a single reader your queue can easily get flooded. – Bradstreet

@Horner I've been able to enqueue/process more than 3000 msgs per second per single queue on my laptop. Of course processing logic was simple. That was comparable to what I was able to achieve with one node of Apache Kafka. Critical issue here is transactional durability, which takes away performance. Each operation needs to be written to the transaction log and flushed out to disk. If you need more performance, use multiple queues/multiple broker instances and better environment (especially faster disks). – Superabound

@MatthewWhited: We do use simple mode for SSB databases. We have one reader per queue and use waitfor – Horner

@Superabound That's impressive. Did you enqueue the messages first and then time the reads, or how did you measure? – Horner

You issue is probably around having only one reader per queue. Depending on what you have that reader doing that will be a bottle neck. – Bradstreet

@Horner "My point was that 100k/hour isn't excellent performance." in our case, the bottleneck was Sql Server Disk IO on our test server. I would have loved to see what the prod server could have done, but alas that was not permitted as the prod (and staging) environments were owned by the client. And simple recovery mode would have been unacceptable to the client. – Hued

That is one reader holding 100 messages. You should have RECEIVE TOP(1) and have multiple readers CREATE QUEUE [yourqueue] WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [yourproc], MAX_QUEUE_READERS=100 /*reader count*/) – Bradstreet

And yes, these comments were based on old comments ... but it's good to keep the future masses correctly informed :) – Bradstreet