What are the advantages of a long timeout for RECEIVE in activation procedures?
The https://docs.microsoft.com/en-us/sql/t-sql/statements/receive-transact-sql?view=sql-server-ver15 statement has an optional timeout parameter. Most of the activation procedure examples online use a value of 1000 or 5000 milliseconds. In my own testing with Service Broker, I've found that letting the activation procedure run continuously can be bad for server scalability. For example, the activation procedure may end up on the same scheduler as a log writer or it may end up on the same scheduler as another activation procedure. The problem isn't really specific to Service Broker. In general, avoiding long running MAXDOP 1 processes is good for scalability because it gives SQL Server a chance to fix CPU scheduling contention.
I'm inclined to set a low timeout value for RECEIVE in my activation procedure, possibly as low as 1 millisecond. Are there any advantages in setting a longer timeout value?
I haven't measured the overhead myself, just going off the "best practices".
It's a difference between continuously polling and batch processing. If the messages come 100 ms apart, with the timeout = 1ms, you will be closing and reopening the
RECEIVEall the time.
Klaus Aschenbrenner states in his book:
Waiting for a message to appear in a queue is generally more efficient than periodically polling the queue with RECEIVE.
Also, this example video by Jonathan Kehayias has a timeout of 5000, so maybe that's where the advice originated. https://youtu.be/TdO0IZlqitU?t=3768
It probably boils down to the good old "it depends". For example, how much traffic goes between the brokers and how long apart the messages are.
I would be interested in the result if someone does indeed measure it.