What are queues and why should we use them

I was going to write about querying XML but then realised I had to start with the queues. Bear with me. It will all start to make sense when you read all articles. It makes sense to talk about queues since I recently mentioned that SQLWATCH is moving to queues.

Why queues

We all queued in the shop at least once, but have we ever thought about the concept of queues and why they exist? Mainly because more customers want to buy and pay at the same time than there are cashiers or tills. Cashiers are not good at serving multiple clients at the same time. They must go through each basket, count all items and take payment at the end. Only then move on to the next client. If customers did not queue and all “attacked” the cashier, it would just be chaos that would serve no one. The cashier would probably panic and run away.

We can think of the above scenario as concurrent access (like single or multiple applications making many requests to a database that may eventually kill it). What if we told all the customers to wait until there is no one else at the till and serve them one by one? Such a scenario would naturally create a queue:

In the above scenario, the cashier (the database) is never overwhelmed and can sit there and carry out their job. Customers also know their place and do not rush and demand service (like in concurrent access scenario). Sure, if the queue takes too long, like really, really long, those at the very end may still give up, drop their basket and walk away, so the cashier still needs to be relatively fast enough, but what if it isn’t? Well, we can have multiple cashiers who will serve customers much faster:

Those types of queues are called First in First Out (FIFO) because the first customer that gets to the checkout is the first customer who will leave the checkout. There are also other queues, for example, First In Last Out (FILO), but that’s for another blog post.

In other words, queues are highly efficient buffers that help reduce overload

Queues in SQL Server

Service Broker queues

The Service Broker handles queues in SQL Server. Many developers build their queues in tables, but they are rarely as efficient and reliable as the Service Broker (this statement will probably start a fight). Service Broker can handle thousands or hundreds of thousands of messages per second. Hang on, what is a message?

Tables as queues

Although I prefer Service Broker queues to custom-made table queues, these are perfectly fine to do, and I have done a few in my career. You can find one in previous versions of SQLWATCH, in the table: dbo.sqlwatch_meta_action_queue. We will do a deep dive into tables queues in the future.

Messages and Payloads

The primary goal for the queue is to deliver a piece of information. This information is a message and its payload. A message in a queue system carries the information (the customer), and the information itself is the payload (the shopping basket). These are being delivered to the queue processor (the cashier).

So now it all makes sense. If there were no queues in the shop, like in the first picture, customers would dump the content of the basked on a single cashier, all the same time.

More on Messages and Payloads in future posts. Subscribe for updates.

If we are implementing queues using tables, the table itself will be the queue, the row will be our message and the content of the row will be the payload.

The benefit of queues

Performance

The benefit of queues is that they can dampen the spike in the workload. Queues can usually hold a lot of data, smoothing out the processing demand by drip-feeding the messages to the processor. The processor “cashier” usually gets the messages from the queue when it’s ready “next, please!” instead of being bombarded with requests. Queues can even allow the processor to go offline whilst accumulating messages that will be processed when the processor comes back online. When the till at the shop closes, the queue will be just growing. It cannot grow indefinitely, but that is a separate subject.

Security

Using queues can increase the security of the system. For example, we may only have access to write a specific message into the queue and not to the database directly.

Resilience

Imagine you have an application that directly interfaces with the database. The database is overloaded and refuses connection whilst your application is trying to write sales data. The writing process fails, the data never lands in the database, but the sale has been made as far as the customer is concerned. Of course, in an ideal world, you would write some extra logic to check database transactions and only return success status if the write has been successful. All this extra work needs lots and lots of testing.

With queues, once you have written into the queue, the process is completed as far as your application is concerned. There are bi-directional messaging standards that will notify the application about the message status, but that’s for another post. By design, once the data is in the queue, it is guaranteed to make it into the destination endpoint. I am not talking about SQL Server here, just Queues in general. Pretty much like in the shop, you will get served once you are in the queue, even if the shop is closing. The shop will not allow new customers to enter and only deal with existing ones.

In SQL Server, sure the message can fail, for example, we can have some data that would violate the primary key, and the INSERT will fail. The message will then go back into the queue for manual investigation. If the error continues, the queue will be disabled and will not allow any new data to maintain consistency. This is important to know that queues care about data integrity and consistency.

SQLWATCH suffered from this problem

This was the most common problem in SQLWATCH. With direct access, that is an Agent Job running a stored procedure that was doing the data collection, we often observed a situation where reading data into Power BI or Grafana caused enough load to cause the data collection to hang there waiting for resources (or even locks) causing gaps in the data. I have introduced queues in version 5, so the data is never skipped. Suppose the database is being overloaded temporarily because we are reading data into the dashboard. In that case, the queue will continue accepting data, and we never have gaps, just a slight delay that will eventually catch up. Furthermore, queues (and SQL Server Service Broker in general) have very little overhead, much less than invoking Agent Jobs, so we use much less CPU for data collection.

Conclusion

Queues can solve a lot of problems. Still, suppose the receiving endpoint “the cashier” cannot handle the number of messages “customers”. In that case, the management must employ more cashiers (more queue processors) or train them to be faster (add more CPU). You can try implementing queues in your existing system to try to smoothen workload spikes and prevent overload. A properly designed system utilising queues can often handle way bigger workloads than those with direct access, increased security, and reduced risk.

Stay tuned for future posts. Subscribe for updates:

[emailoctopus form_id=1]