When you have a hammer, everything looks like a nail. Unfortunately, a lot of people don’t even know how to use a hammer correctly.
You really shouldn’t be doing queues in the database because:
- They’re a blocking nightmare, constantly locking rows in highly concurrent tables
- They’re a logging nightmare, constantly pouring data into the transaction log
- They’re an HA/DR nightmare, constantly causing slow writes to your sync replicas
- They’re a backup nightmare, constantly changing pages that bloat your differential backups
- They’re a licensing nightmare, causing a lot of work on an expensive relational database for tables that have no relational joins to anything else whatsoever
- There are simple, cheap queueing alternatives like Amazon SQS and a few in Azure
If you really gotta do queues in SQL Server, read how to use locking hints.
11 Comments. Leave new
I usually just used an array in my application or later on, queue types themselves in .Net for example the Queue Class.
When I needed a quick, application death tolerant, distributed work tracker, a quick db table was easy to add. The app was small, not many users, it was fast, easy, and most of all, “free”. Don’t have to convince the bosses to get a new application or cloud service.
Then I needed something a bit more robust and got a library that tracked things in the db. Better and still “free”.
Now, we grew 3 gazillion percent, who is my app falling down………
Overall I do agree that DBs stink for queues. But sometimes the red tape to bring in a proper queue is more hassle than it’s worth.
@Brent – how you would rank service broker in the mix here as a queueing option? I do agree that it’s generally better served with a specialized solution (SQS, RabbitMQ, etc.) but curious what your opinion is.
Remind me, what’s the cost on it?
Well after you set up Message Types, Contracts, Queues and Services, Routing, Security (authentication and encryption), implement the activation procedure and handle 1 business case and 17 technical nuances such as poison massage handling, add some logging so that you can “see” staff happen, there are basically no costs at all.
Wait oh…
And let us not forget monitoring – add some job to check the pulse of the system and alert you if it does not behave as expected (you could let SQL server email you via e-mail queue…oh what irony)
@Haris Katura, is it simplier and more robust with other messaging services? I am just new to the subject, there is no irony in my question.
http://rusanu.com/2010/03/09/dealing-with-large-queues/
Then there is this gem which cooled any enthusiasm I had for Service Broker considerably, although I suspect SB handles it better than 99% of all other queue implementations in SQL Server
msmq is a nightmare to configure and not many devs understand it
I’m curious: Do you write the blog post and then look for a meme to fit or is it the other way around? 🙂
I looked at meme examples first, heh.
Can’t some of the problems outlined in that article be avoided by using the “DELETE … OUTPUT” construct (he asked naively, probably opening a whole other can of worms).