Thursday, August 6, 2009

The DBA vs. Service Broker Assistant Part II

So being the DBA portion of Devenius ( see About ) I figured I would try to test my developer partner's statistics from Part 1 using Server Broker Assistant in implementing the event notification logging example. I did deviate slightly from the plan by creating the tables and stored procedure prior to beginning the time study. My test went as follows.

1) Enable service broker for the test database.
2) Create the queue for notification events.
3) Create the service to manage the queue.
4) Alter the queue to use the previously created stored procedure.


I suppose I could have just as easily skipped step 4 and hooked in the stored procedure upon queue creation. However, I figured I would try to get close to the same process I am attempting to validate.

Test Case A - scripted

Using Management Studio's Query window, I wrote and executed the T-SQL manually. I too had the syntax available to me and wasn't required to perform any lookups on MSDN or BOL. Here is a copy of the sytax:

1. ALTER DATABASE TestDB_1 SET ENABLE_BROKER;

2. CREATE QUEUE NotifyQueue;

3. CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);

4. ALTER QUEUE [dbo].[NotifyQueue]
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = [dbo].[LogEventsProc] ,
MAX_QUEUE_READERS = 2,
EXECUTE AS SELF
);

Test Case B - Service Broker Assistant

In this scenario, I used the Service Broker Assistant to perform the same actions in Test Case A.


And the results:

Test Case A = 3 min. 54 sec.
Test Case B = 1 min. 46 sec.

Service Broker Assistant showed quite an improvement in time to execute over the traditional method of manual T-SQL.

No comments:

Post a Comment