We are trying to get Service Broker working in our environment in order to solve a business case. I don't know if the message title is a good one, but my question is below. But it may not be a good question, so after that is what we are doing and why I think it's the right question.
How many messages should be sent on a conversation before ending the conversation?
We want to use Service Broker in order to asynchronously update a result table. The result table is flattened and fast. We have triggers on the base tables that send a message with their table and primary key. We have three queues:
- Low Latency - objective is 15 second to process. It handles items that change relating to a specific item.
- Bulk Queue - objective is 5 minutes to process. It handles when something changes that affects many hundred (or thousands) of items. It breaks out the list of items that were affected and feeds them to the Deferred Low Latency Queue.
- Deferred Low Latency - objective is 30 minutes to process. This processes items, but only from the bulk queue.
Basically, if a client's information updates, that affects many products, so that gets sent to the bulk queue for slower processing. However, if a product gets updated, that gets sent to the low latency queue.
We reuse conversations similar to Remus Rusanu's blog http://rusanu.com/2007/04/25/reusing-conversations/, with the exception that we do it based on the modulus of the primary key. This has the side benefit of aiding in de-duplication of primary key's.
So, we are re-using conversations and are within our guidelines. With two threads, I was able to burn through 125 messages/second (artificial drop of several thousand messages), which is more than capable of keeping up with production (est. 15 messages/sec).
However, the problem we are experiencing is that after a period of time, ~4 hours or 120K messages, we started seeing blocks and high contention on sysdesend and the queue table. The locks are LCK_M_U and are KEY locks. Sometimes the hobt resolves to sysdesend and othertimes to the specific queue table (queue_).
We have a process in place that will end conversations after either 24 hours or 30 minutes of inactivity, we could just increase the time before cycling over of conversations.
We are using SQL 2016 Enterprise (13.0.4001.0)
- Trigger Fires (send to either low latency or bulk)
- Look up or create conversation handle.
- Send message
- Queue activated procedure
- Update results table
Cleanup process runs every 10 minutes to see if there are any idle conversations. ltd it finds them more than three times in a row, it marks it as inactive and ends the conversations.
Please let me know if there are any additional details that may be beneficial. I don't have much experience with Service Broker, so I don't know if our messages/sec is low, high or indifferent.
So we tried again today and encountered the same problem. We changed the conversation lifetime to 2 hours and that had no effect. So we then implemented the 150 trick, which had the same issue.
Tons of waits on SEND CONVERSATION, waiting on sysdesend. Does anyone have any further ideas?
We ran the test for longer today, and for one of the sample periods of 17 minutes, we processed 41K messages on 4 conversation handles. We were able to keep up, except towards the end when the locks on the sysdesend and the queue table became too much, and we started drifting behind before stopping it. We seem to have no problem processing messages, without things entering the queue: we can pull them off and process them at least 5x that speed. Our speed appears to be limited based on adding messages.
On a later test, we removed one of the triggers which accounted for 80% of the messages. Even with this much reduced load, we started seeing the same waits.
Thank you, Remus for your advice (and thank you for posting such excellent blog articles on the subject, they were instrumental in getting to this point).
We ran it again today and did better (as in we went longer before seeing the waits and even longer before it crippled us). So, the details.
Increased the number of maintained conversations per thread from 1:1 to 2:1. Basically, we had 8 conversation handles for 4 threads.
consolidated the bulk queue (because one incoming message could mean hundreds of outgoing messages) to consolidate into fewer, larger messages.
Notes about this attempt:
disabling the target queue activation procedure. no change in blocking (we waited 5 minutes) and the messages did get sent to sys.transmission_queues.
monitoring sys.conversation_endpoints. This number went from 0 13K very quickly, and then more slowly rose throughout the day ending up around 25K after ~5 hours. Blocking didn't start occurring until it reached 16K+/-
I went into the DAC and ran the DBREINDEX commands for the queues, although from a query, ghost records never got above 200 or so before the cleanup came along and dropped the count to 0.
sysdesend and sysdercv had identical counts of 24,932 when I ended the test.
we processed ~310K messages in 5 hours.
We went so far before things fell apart that I really thought we would make it this time. Tomorrow we will try forcing the messages to go through the wire.