Queue DELETE time

Hello!

In running some performance tests on a Queue using a message size of ~5KB, we found that we can process (SEND and RECEIVE) on the order of 600 - 800 messages / second. However, we have found that INSERTs of new messages to the Queue appear to take great precedence over DELETEs of received messages from the queue. In particular, we found that during heavy use the total size of the Queue (as determined using the sp_spaceused procedure) equals about the number of total messages processed, not the number of messages on the queue.

When we stop sending messages, the overall size of the Queue table appears to decrease slowly, so there is a background process that is obviously doing some work there to clean up the received messages from the Queue. What I would like to know is if we can affect that background process in any way so that the messages are cleared out more quickly. The performance has been determined to suffer appreciably once the Queue size grows to greater than about 3GB in size. We also notice timeouts on the RECEIVE statements when the Queue size is that large.

Thanks for any help --

Robert

There is no background deletion from queues.
If retention is OFF, the RECEIVE is a DELETE WITH OUTPUT and messages are deleted immedeately.
If retention is ON, RECEIVE is an UPDATE WITH OUTPUT and the messages are deleted when the END CONVERSATION is run.

HTH,
~ Remus

|||

Remus --

Thanks for the reply. What we have noticed is that if we SEND a lot of messages in a short period of time, the overall size of the Queue will grow regardless of the actual number of messages on the Queue. Maybe what it is is actually a background shrinking of the TABLE; I don't know. I did find that if the throughput was ~300 messages / sec or less, the shrinking kept up with the INSERTs and the size of the Queue did not increase.

Is there no way, then, to affect the shrinking of the Queue?

Thanks --

Robert

|||

Doesn't this means that your sending faster than the attached procedure can process? I have a blog on how to process faster at http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx , but as a general rule, you will always be able to send faster than the service can process. You must tune your system to be able to process as many messages as you need as average over some period of time. You do not need to be able to keep up with spikes of messages, they can queue up and be processed later, but you need to be able to keep up with the incomming rate over time.

|||

Remus Rusanu wrote:

Doesn't this means that your sending faster than the attached procedure can process?

I don't know if I would call this SENDing faster than being able to process the messages. As a general rule, the number of items on the Queue (as told by the ROWS column from sp_spaceused) doesn't appear to grow that large.

Here is an example: we would SEND 5KB messages at a rate of ~600 - 800 / sec. We were able to process the messages at about the same rate, give or take a little. Over the course of half an hour, the ROWS column said that there were maybe 10000 items on the Queue, but the RESERVED column would show a TABLE size of ~5GB. Obviously, the size of 10000 5KB messages is not 5GB, but the total number of items processed (~1.1 million) * 5KB is ~5GB.

What that indicates to me is that either the process that DELETEs the rows from the TABLE or the process that shrinks the TABLE after items are DELETEd operates more slowly or less frequently than the process that INSERTs the rows into the TABLE. If I throttle the SEND process down to ~300 messages / sec, it appears that the two processes -- growing to add the new messages and shrinking after delivering the received messages -- are close to equilibrium.

In talking through this, I guess we've more or less answered the question, which was whether I could make the TABLE shrink occur any more quickly -- and the answer is no, we need to tune the system for the greatest throughput. Thanks very much for your time!

Robert

|||

Use this query to get the number of rows in the queue:

select p.rows

from sys.objects as o

join sys.partitions as p on p.object_id = o.object_id

join sys.objects as q on o.parent_object_id = q.object_id

where q.name = '<queuename>'

and p.index_id = 1

What you see is probably the ghost writer cleanning up pages in the database, has no relation with SSB per say, is the normal process you would see with any table that has a large number of inserts (message enqueues) followed by a large number of deleted (message dequeues). sys.dm_db_index_physical_stats (http://msdn2.microsoft.com/en-us/library/ms188917.aspx) shows the actual number of ghosted records and you may correlate this with your observations.

HTH,

~ Remus