Service Broker Sample: Multi-Threading
Service Broker is a cool new feature since SQL Server 2005. Yes, SQL 2012 just came out so it’s hardly “new” anymore. But in reality, many DBAs and DB users are still not aware of this feature, or don’t know how to utilize it, or are afraid to do so because they’re not familiar with it.
Using this short series of posts, I’m hoping to ease the minds of such people.
In short, Service Broker is an asynchronous queuing engine working inside SQL Server. Using this engine we can send and receive binary or XML messages between queues. When a message is inserted in a queue, Service Broker executes an “activation procedure” that you specified, which is then supposed to handle messages from that queue.
Since we have a lot to cover in our sample, I won’t go into too much detail introducing Service Broker. But Microsoft has an excellent and detailed chapter about Service Broker available right here: http://msdn.microsoft.com/en-us/library/bb522893.aspx
Sample 1: Multi-Threading
I managed to encounter several times a scenario in which an application needed to execute several queries independent of each other, and then group them together somehow. However, each of these queries takes a while to execute.
One might wonder: If only we could use some kind of multi-threading mechanism to execute them in parallel, and then the total running time will be that of the slowest query only and not the total of them.
Well, it just so happens that our first sample will show how you can implement “multi-threading” in SQL Server using Service Broker queues!
First, I’ll explain the general idea of the design:
Since Service Broker only works with binary or XML messages, we’ll use XML to send a “query request”, and wait for a “query response”.
Specifically, we’ll send a message containing some kind of a dynamic query. But that dynamic query must return its result in XML form using an output parameter. Then, Service Broker will execute the query, retrieve the result from the output parameter, and send the result back as the response.
After that, it’s up to us to gather all the responses together and do whatever it is we wanted to do with it.
Also, for the sake of our sample, we’ll need to simulate long execution times. We’ll do this simply by adding a WAITFOR DELAY command into our dynamic queries which will cause the query to wait for a random number of seconds (between 10 and 30).
Available below is a ZIP file with the two scripts we’ll use:
One is the installation script, responsible for creating all the objects we require – the database, the service broker procedures, endpoints and queues. Everything you need to implement multi-threading in SQL Server.
The other is a sample of how to use this mechanism, using simple queries and a WAITFOR DELAY command to simulate long execution times.
The scripts are commented to help you understand what’s going on. But we’ll go over them bit by bit just in case.
The Installation Script
In order to explain the installation script, I’ll list all the objects that we’re creating and explain the purpose of each object. More detailed information about the implementation can be found in the script itself.
That’s the database we’ll be using to create all the necessary objects.
This is a logging table which will be used to log the start and end of each query execution, and any errors that may occur.
This is the procedure which will actually execute the dynamic queries. It builds the dynamic command, uses sp_executesql to execute it, and returns the result as an output parameter. The name of the output parameter itself can be dynamic.
This is the activation procedure that Service Broker executes when it creates a “reader instance”. Or in other words, when there’s something in the request queue.
This procedure is the most important part because it’s responsible for retrieving messages from the request queue, execute the query (using SB_PQ_ExecuteDynamicQuery), and handle any errors that may occur in-between.
This is the queue where all query requests will be waiting to be processed. Its activation procedure is SB_PQ_HandleQueue.
This is the queue into which query responses will be sent. Its doesn’t have an activation procedure because query response extraction is done manually by the client process.
This is a simple message type of “Well Formed XML”. We’ll be using it to verify our messages.
This is a contract defining that both target and initiator must send messages of type [//SB_PQ/Message].
This is the service endpoint working as the “address” of request queries. We will be sending messages into this service. The queue it’ll be using to receive messages is SB_PQ_Request_Queue.
This is the service endpoint working as the “address” of response queries.
We will be sending messages from this service. The queue it’ll be using to receive messages is SB_PQ_Response_Queue.