Service Broker Strange Performance spikes RRS feed

  • Question

  • I have a simple stored procedure that send a message on a conversation:


    Code Snippet

    CREATE PROCEDURE [Vxml].[sp_SendMessageToWarehouse]
                -- Add the parameters for the stored procedure here
                @Message XML,
                @EntityId uniqueidentifier,
                @ConversationHandle uniqueidentifier OUTPUT
                BEGIN TRY
                    ;SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE VxmlEngineXMLMessage(@Message);
                END TRY
                BEGIN CATCH
                    INSERT INTO [Vxml].SBError (ErrorMsg) VALUES (N'Error <' + ERROR_MESSAGE() + N'> for entity <' + cast(@EntityId as NVARCHAR(max)) + N'> on conversation <)' + cast(@ConversationHandle as NVARCHAR(max)) + N'> with body ' + cast(@Message as NVARCHAR(max)))
                END CATCH;

    This completes in less than 100ms (avg 30ms) except once in about 10000 executes (only one simultaneous execute), then this takes about 3 seconds to complete.

    When performing the same test and calling the stored procedure simultaneously from 5 to 20 threads, we see up to 3 such spikes (in 10000 executes) taking 2 to 4 seconds.

    Conversations are created before the test and closed after. No ODBC errors occur and the SBError table is empty.

    This is run on an SQLEXPRESS the conversation target is a SQL Server 2005 (standard edition)

    Can anyone explain these strange performance spikes ?
    Thursday, February 7, 2008 8:17 AM


  • Most likely you are hitting database or log growth events. A quick inspection of the performance counters could confirm this. Pre-grow your database and log.
    Thursday, February 7, 2008 10:39 AM