Meme Week: Queue in the Database

Architecture
11 Comments

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.

Previous Post
Meme Week: SQL Server 2012 as of 2022
Next Post
Meme Week: SET DEADLOCK_PRIORITY HIGH

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.

    Reply
  • 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.

    Reply
  • @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.

    Reply
    • Remind me, what’s the cost on it?

      Reply
      • 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)

        Reply
    • msmq is a nightmare to configure and not many devs understand it

      Reply
  • I’m curious: Do you write the blog post and then look for a meme to fit or is it the other way around? 🙂

    Reply
  • Julian Fletcher
    February 23, 2022 8:43 am

    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).

    Reply

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.