Introduction

Close your eyes and picture this: you have just completed a major application upgrade that included large changes in your SQL Server database. The upgrade went smoothly and completed in the agreed time frame. You are ready to inform the business that the database is application-ready, but aren’t you forgetting something? The upgrade has likely caused index fragmentation and outdated statistics in the data distributions. Poor application performance may result if this is not addressed. You have to update table statistics and rebuild fragmented indexes. But that will take hours and your outage window is about to end. Your users are fidgeting and are expecting the same application performance as before the upgrade. What are you going to do?

This scenario probably rings true to many of us. Organizations increasingly demand the continuous availability of database systems, imposing restrictions on the frequency and size of outage windows. A powerful platform that can help speed up database maintenance and reduce outage times is Service Broker. Introduced in SQL Server 2005, Service Broker provides support for multi-threaded messaging and queuing applications. Messages are exchanged between initiator and target queues asynchronously, increasing system efficiency and responsiveness. This is achieved by activation, a mechanism that alerts a program to start execution when it detects there is work for it to do. Activation speeds up processing by starting multiple threads of the same program simultaneously; each thread executes one message in the queue, until the queue is empty.

In this article I describe an implementation of Service-Broker internal activation for the manual update of statistics of database tables. Internal activation is a type of activation that works with SQL Server stored procedures. The approach presented here can be easily adapted to any other maintenance procedure (such as index defragmentation) where a series of independent tasks is executed in sequence. Internal activation is used to spawn several parallel sessions, each given the task of updating the statistics of a single table. The number of parallel sessions can be easily scaled up or down, depending on CPU availability. On a multi-CPU server this results in a significant reduction in the length of maintenance runs, in comparison to conventional runs where objects are processed in a single sequence.

Implementation 

In this section we will look at the setup of the main processing database, the Service Broker queues and activation stored procedures.

Setup of the UTILITY database

All processing in this implementation is done in a DBA-owned database, named UTILITY. The UTILITY-database objects and individual roles are shown in the following table:

The process workflow is shown in Figure 1:

Figure 1: Service-Broker implementation for updating statistics of database tables

The stored procedure UpdStats_serviceBroker_beginDialog loops through the records in a pre-populated table, UpdateStatsTables. Each record in UpdateStatsTables represents one database table. In each loop iteration UpdStats_serviceBroker_beginDialog begins a dialog conversation and sends a request message to the target queue, UpdStats_TargetQueue. The queue receives the request messages and activates several parallel-running copies of procedure UpdStats_InternalActivationTarget. Each copy of the procedure reads one request message from the target queue and updates the statistics of one table.

Once that task is done, the procedure sends a reply message to initiator queue, UpdStats_InitiatorQueue, confirming that the processing of the table is done. It then starts over by picking up the next available message from UpdStats_TargetQueue. The initiator queue receives each of the reply messages from UpdStats_InternalActivationTarget and initiates several parallel threads of procedure UpdStats_InternalActivationInitiator. Each thread reads a single reply message from the initiator queue and ends the corresponding dialog conversation. At that point the conversation lifecycle is completed.

Objects

In order to access objects in other databases in the SQL Server instance through Service Broker, the UTILITY database has to be Service-Broker-enabled and its trustworthy bit turned on:

ALTER DATABASE [UTILITY] SET ENABLE_BROKER;
GO
ALTER DATABASE [UTILITY] SET TRUSTWORTHY ON;
GO

Next, several Service-Broker objects need to be created:

Message types: used in performing validation of messages received in a queue:

USE [UTILITY];
GO
CREATE MESSAGE TYPE
       [//UTILITY/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//UTILITY/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

Contract: defines which message types will be allowed in the dialog conversation:

USE [UTILITY];
GO
CREATE CONTRACT [//UTILITY/Contract]
      ([//UTILITY/RequestMessage]
       SENT BY INITIATOR,
       [//UTILITY/ReplyMessage]
       SENT BY TARGET
      );
GO

Target Queue and Service: the physical object for storing messages and the associated logical object acting as a reference for routing messages to the target queue, respectively:

USE UTILITY;
GO
CREATE QUEUE UpdStats_TargetQueue;
CREATE SERVICE [//UTILITY/UpdStats/TargetService]
 ON QUEUE UpdStats_TargetQueue ([//UTILITY/Contract]);
GO

Initiator Queue and Service: the physical object for storing messages and the associated logical object acting as a reference for routing messages to the initiator queue, respectively:

USE UTILITY; 
GO
CREATE QUEUE UpdStats_InitiatorQueue;
CREATE SERVICE [//UTILITY/UpdStats/InitiatorService]  
 ON QUEUE UpdStats_InitiatorQueue; 
GO

Configuring Queues for Internal Activation

The target and initiator queues now need to be configured for internal activation. The procedures referenced in the code below must already exist in the UTILITY database; these procedures will be discussed in the next section:

USE UTILITY;
GO
ALTER QUEUE UpdStats_TargetQueue
    WITH ACTIVATION
    ( STATUS = ON,
      PROCEDURE_NAME = UpdStats_internalActivationTarget,
      MAX_QUEUE_READERS = 2,
      EXECUTE AS SELF
    );
GO
ALTER QUEUE UpdStats_InitiatorQueue
    WITH ACTIVATION
    ( STATUS = ON,
      PROCEDURE_NAME = UpdStats_internalActivationInitiator,
      MAX_QUEUE_READERS = 2,
      EXECUTE AS SELF
    );
GO

Note that MAX_QUEUE_READERS is set to 2 in this sample; this means that a maximum of 2 instances of the activation stored procedure can run at any given time. This value can be increased, depending on the number of CPU cores available.

Procedure UpdStats_serviceBroker_beginDialog

This procedure selects records of database-table pairs from table UpdateStatsTables and stores them in a cursor; UpdateStatsTables is prepopulated with this data. It then loops through the cursor and in each loop iteration performs the following tasks:

It begins a dialog conversation from the initiator service - [//UTILITY/UpdStats/InitiatorService] - to the target service -  [//UTILITY/UpdStats/TargetService] – on contract [//UTILITY/Contract]:

BEGIN DIALOG @InitDlgHandle
 FROM SERVICE [//UTILITY/UpdStats/InitiatorService]
 TO SERVICE N'//UTILITY/UpdStats/TargetService'
 ON CONTRACT [//UTILITY/Contract]
WITH ENCRYPTION = OFF;

This then constructs the request message:

SELECT @RequestMsg = N'<Request><Database>' + @dbName + N'</Database><Table>'
 + @tblName + N'</Table></Request>';

The process sends the request message to the target queue as part of the conversation:

SEND ON CONVERSATION @InitDlgHandle
 MESSAGE TYPE [//UTILITY/RequestMessage] (@RequestMsg);

Procedure “UpdStats_internalActivationTarget”

This is an internal-activation procedure. It gets activated when the target queue, UpdStats_TargetQueue, receives a request message from procedure UpdStats_serviceBroker_beginDialog. It loops through the following tasks until there are no more messages left in the target queue:

This procedure receives the conversation handle, message body and message-type name from the target queue; this data originates from procedure UpdStats_serviceBroker_beginDialog:

WAITFOR
( RECEIVE TOP(1)
 @RecvReqDlgHandle = conversation_handle,
 @RecvReqMsg = message_body,
 @RecvReqMsgName = message_type_name
  FROM UpdStats_TargetQueue
), TIMEOUT 5000;

If the message-type name is what is expected, “//UTILITY/RequestMessage”, it does the following:

(1) Parses the database and table name from the message (message is in XML format):

SELECT @dbName = @RecvReqMsg.value('(/Request/Database)[1]', 'SYSNAME')
,      @tblName = @RecvReqMsg.value('(/Request/Table)[1]', 'SYSNAME');

(2) Inserts the dialog handle, message, message-type name, database name and table name in table ServiceBroker_UpdStatsLog with a timestamp.

(3) Updates the statistics of the table in question:

SET @sql = N' USE [' + @dbName + '];
IF  EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''' + @tblName + ''') AND type = N''U'' )
 BEGIN
  UPDATE STATISTICS ' + @tblName + ' WITH FULLSCAN;
 END';
EXEC (@sql);

(4) Repeats step (2); the time difference between the two timestamps, step (2) and this step, represents the time it took to update the statistics of the table.

(5) Constructs the reply message, confirming that the statistics of the table in question has been updated:

SELECT @ReplyMsg = N'<ReplyMsg>The statistics of table ' + @tblName + N' in database '
 + @dbName + N' has been updated WITH FULLSCAN.</ReplyMsg>';

(6) Sends the reply message to the initiator queue as part of the conversation:

SEND ON CONVERSATION @RecvReqDlgHandle
 MESSAGE TYPE [//UTILITY/ReplyMessage](@ReplyMsg);

Procedure UpdStats_internalActivationInitiator

This is also an internal-activation procedure. It gets activated when the initiator queue, UpdStats_InitiatorQueue, receives a reply message from procedure UpdStats_internalActivationTarget. The purpose of this procedure is to end each of the conversations started by procedure UpdStats_serviceBroker_beginDialog.

UpdStats_internalActivationInitiator loops through the following tasks until there are no more messages left in the initiator queue:

This procedure receives the conversation handle, message body and message-type name from the initiator queue; this data originates from procedure UpdStats_internalActivationTarget:

WAITFOR (
RECEIVE TOP(1)
@RecvReplyDlgHandle = conversation_handle,
  @RecvReplyMsg = message_body
FROM UpdStats_InitiatorQueue
 ), TIMEOUT 5000;

It ends the conversation; this conversation has now come full circle:

END CONVERSATION @RecvReplyDlgHandle;

It then populates table ServiceBroker_EndConversation with the reply message.

Results

To launch the update-stats process you just need to execute this:

EXEC [UTILITY].[dbo].[UpdStats_serviceBroker_beginDialog];

You will notice that your session completes almost instantaneously. If you are running the statement as a SQL-agent job, the job itself ends in a matter of seconds. This does not mean that the actual process of updating the statistics of database tables is done. Behind the scenes a lot is happening. However, a somewhat different methodology than that of monitoring conventional SQL activity is required. In part 2 of this series I will go through techniques of checking on Service-Broker processes and fixing failures. For now I will add a couple of queries of listing execution requests and measuring the duration of the entire run.

The following query shows which service-broker requests are currently running:

SELECT
 [des].session_id
, [des].login_name
, DB_NAME(der.database_id) AS database_name
, der.command
, der.last_wait_type
, der.wait_resource
--, dest.[text] AS [executing batch]
FROM
 sys.dm_exec_sessions [des]
LEFT JOIN
 sys.dm_exec_requests [der]
ON
 [des].session_id = der.session_id
--OUTER APPLY
-- sys.dm_exec_sql_text(der.sql_handle) dest
WHERE
 [des].session_id <> @@SPID
 AND [der].[status] <> 'sleeping'
 AND DB_NAME(der.database_id) <> 'master'
ORDER BY
 [des].session_id;

On my machine, with MAX_QUEUE_READERS set to 2, I got the following:

Two sessions are running in parallel, executing command "UPDATE STATISTIC" on two different tables of database SPdemo. To find out which tables these are, uncomment the lines of code in the above query to get the [executing batch] column returned. The third session, with session_id=30, was executing procedure UpdStats_internalActivationInitiator at the time of capture. There is no explicit DELETE statement in the procedure code, so the DELETE showing in the command column is initiated by an internal Service-Broker process.

There is nothing special about the query above, but notice that all three session ids are lower than 50, normally reserved for system activity. This is a characteristic of service-broker sessions.

To get the duration of the overall run in seconds, run this:

SELECT
 DATEDIFF(SECOND, MIN([db_recordtimestamp]), MAX([db_recordtimestamp])) AS [Run Duration]
FROM 
 [UTILITY].[dbo].[ServiceBroker_UpdStatsLog];

If you recall, we store the timestamps of both start and end of the processing of each table in table [UTILITY].[dbo].[ServiceBroker_UpdStatsLog]; so the query gives us how long it took from the moment the first table was about to be processed to the time when processing of the last table was completed. As there are a few other steps before and after that time window (see Figure 1), the actual duration of the run will just be a few seconds longer. 

The time savings of running update-statistics with Service Broker, relative to conventional single-thread runs, can vary widely. Pay close attention to the last_wait_type column when monitoring session activity; if the waits are mostly IO-based, then you likely have a problem with your disk subsystem and Service Broker will not help you. I tried this on my desktop PC with all database files on the C: drive and managed to get Service Broker to run SLOWER than a conventional single-thread run! In contrast, I have seen a 60% speed-up of the processing of a 400-GB database on a high-end production server (MAX_QUEUE_READERS = 8; MAXDOP = 1): from 21 hours, pre-Service-Broker, down to 7. The constraint came from the largest table that alone took about 4 hrs. The top few largest tables will inevitably eat up a large chunk of the maintenance-run duration, no matter how many parallel threads you have running. The savings comes mainly from processing the rest.

Conclusion

In this article I have described an implementation of Service-Broker activation for updating the statistics of database tables. This methodology can be easily adapted to any form of database maintenance, made up of a series of similar and independent tasks. It comes with an advantage: multiple threads of the same code are activated simultaneously, each thread updating the statistics of one table. As soon as a thread completes its work, it checks its queue for any messages pending and picks up the next one for processing. This continues until no more messages exist in the queue. Compared to conventional single-thread runs, Service-Broker-initiated runs can produce significant time savings. That is important in situations where outage windows tend to be too small for the amount of maintenance work at hand.

By no means is this the first time Service Broker is mentioned in the context of database maintenance. There are at least a couple of other links on this topic that I know of (see References section). My hope though is that I have provided enough detail here to enable the reader to quickly set up his/her own routines with Service Broker. To that end, the full code and setup instructions are attached to this article as supplementary material.

Inspiration for this work came to me from attending a SQLSaturday presentation by John Huang in Toronto last September, titled "Multi-threading TSQL": http://www.sqlsaturday.com/93/schedule.aspx. John's blog on multi-threading can be found here: http://www.sqlnotes.info/tag/multi-threading/.

In part 2 of this series I will go through techniques of monitoring the Service-Broker process and troubleshooting failures. In the meantime, get your own maintenance processes turbocharged with Service Broker and make your CPUs work for a change!

References

Using Service Broker to Enable Parallel-Task Execution:
http://www.enterim.com

Async Lifestyle: Manage Your Tasks With Service Broker
http://technet.microsoft.com/en-us/magazine/2005.05.servicebroker.aspx