Início / user-52567

Evaldas Buinauskas's questions

Martin Hope
Evaldas Buinauskas
Asked: 2017-03-12 11:09:29 +0800 CST
  • 2

Em nossa propriedade, configuramos o Service Broker para realizar uma ação :

  1. Com base em um agendamento, uma consulta é executada e seus resultados são enviados, via service broker, para o outro serviço em um servidor remoto
  2. A mensagem é recebida e consumida pelo procedimento de ativação no servidor de destino.

O problema é que, por algum motivo, a mensagem é consumida várias vezes no servidor de destino, apesar de ter sido enviada apenas uma vez.

Sou relativamente novo no Service Broker e estou procurando aconselhamento/orientação sobre qual poderia ser o problema exato e qual poderia ser a causa dele.

O código do procedimento que inicia a caixa de diálogo:

CREATE PROCEDURE [ServiceBroker].[SendMessage]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @Message XML
            , @ConversationHandle UNIQUEIDENTIFIER
            , @Counter INT = 1
            , @Error INT
            , @TRANCOUNT INT = @@TRANCOUNT
            , @SavePoint CHAR(32) = REPLACE(NEWID(), '-', '')
            , @FromService sysname = 'SimpleServiceInitiator_' + DB_NAME();

        SET @Message = (
            SELECT 1 AS Value
            FOR XML PATH('Message'), ELEMENTS XSINIL, TYPE
            );

        BEGIN TRANSACTION;
        SAVE TRANSACTION @SavePoint;

        WHILE 1 = 1
        BEGIN
            SET @ConversationHandle = NULL;

            BEGIN DIALOG @ConversationHandle
            FROM SERVICE @FromService
            TO SERVICE 'SimpleServiceTarget'
            ON CONTRACT SimpleContract
            WITH ENCRYPTION = OFF;

            -- Set an two minute timer on the conversation
            BEGIN CONVERSATION TIMER (@ConversationHandle) TIMEOUT = 120;

            -- Attempt to SEND on the associated conversation
            SEND
                ON CONVERSATION @ConversationHandle
                MESSAGE TYPE SimpleMessage (@Message);

            -- Successful send, just exit the loop
            SET @Error = @@ERROR;
            IF @Error = 0 BREAK;

            SET @Counter += 1;
            IF @Counter > 3
            BEGIN
                -- We failed 3 times in a row, something must be broken
                RAISERROR (N'Failed to SEND on a conversation for more than 3 times. Error %i.', 16, 1, @Error);
                BREAK;
            END;
        END;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() = 1
        BEGIN
            -- Rollback any active or uncommittable transactions before inserting information in the ErrorLog.
            -- We can still save the other activities in the transaction.
            ROLLBACK TRANSACTION @SavePoint;
            COMMIT TRANSACTION;
        END;
        ELSE IF XACT_STATE() = -1 AND @TRANCOUNT = 0
        BEGIN
            -- If the tran is doomed, and the @TRANCOUNT was 0, we have to do a full roll back
            ROLLBACK TRANSACTION;
        END;
        EXECUTE Logs.LogError;
        THROW;
    END CATCH;
END;
GO

E a do SP de ativação no alvo:

CREATE PROCEDURE [ServiceBroker].[ProcessSimpleMessagesFromQueue]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @ConversationHandle UNIQUEIDENTIFIER
            , @MessageType sysname
            , @MessageBody XML
            , @ServiceName sysname
            , @IsRecognised BIT = 0
            , @ErrorCode INT
            , @ErrorDescription NVARCHAR(4000);

        WHILE 1 = 1
        BEGIN
            SET @ConversationHandle = NULL;
            BEGIN TRANSACTION;
            WAITFOR
            (
                RECEIVE TOP (1) @ConversationHandle = conversation_handle
                    , @MessageBody = message_body
                    , @MessageType = message_type_name
                    , @ServiceName = service_name
                -- SELECT *
                FROM dbo.SimpleQueue
            ), TIMEOUT 5000;

            IF @@ROWCOUNT = 0
            BEGIN
                ROLLBACK TRANSACTION;
                BREAK;
            END;
            ELSE IF @MessageType = N'EndOfStream'
            BEGIN
                END CONVERSATION @ConversationHandle;
            END;
            ELSE IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            BEGIN
                END CONVERSATION @ConversationHandle;
            END;
            ELSE IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
            BEGIN
                EXECUTE ServiceBroker.ExtractError @MessageBody = @MessageBody
                    , @ErrorCode = @ErrorCode OUTPUT
                    , @ErrorDescription = @ErrorDescription OUTPUT;

                END CONVERSATION @ConversationHandle;

                INSERT INTO Logs.ConversationError (ServiceName, ConversationHandle, MessageType, MessageBody, ErrorCode, ErrorDescription)
                VALUES (@ServiceName, @ConversationHandle, @MessageType, @MessageBody, @ErrorCode, @ErrorDescription);
            END;
            ELSE IF @MessageType = N'SimpleMessage'
            BEGIN
                SET @IsRecognised = 1;

                /* DO -TSQL MAGIC*/

                END CONVERSATION @ConversationHandle;
            END;
            COMMIT TRANSACTION;
        END;
    END TRY
    BEGIN CATCH
        SET @ErrorCode = ERROR_NUMBER();
        SET @ErrorDescription = ERROR_MESSAGE();

        IF @IsRecognised = 1
        BEGIN
            -- 6a) Commit otherwise end the conversation to get the message off the queue **
            IF @@TRANCOUNT > 0
            BEGIN
                COMMIT TRANSACTION;
            END;
            ELSE
            BEGIN
                SET @ErrorCode = 127;
                SET @ErrorDescription = N'Unable to process message';

                END CONVERSATION @ConversationHandle
                    WITH ERROR = @ErrorCode
                    DESCRIPTION = @ErrorDescription;
            END;
        END;
        ELSE IF XACT_STATE() IN (-1, 1) AND @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;

        INSERT INTO Logs.ConversationError (ServiceName, ConversationHandle, MessageType, MessageBody, ErrorCode, ErrorDescription)
        VALUES (@ServiceName, @ConversationHandle, @MessageType, @MessageBody, @ErrorCode, @ErrorDescription);
    END CATCH;
END;

Este é o SP de ativação no iniciador que deve encerrar a conversa:

CREATE PROCEDURE [ServiceBroker].[ProcessSimpleMessagesFromQueue_Initiator]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @ConversationHandle UNIQUEIDENTIFIER
            , @MessageType sysname
            , @MessageBody XML
            , @ServiceName sysname
            , @ErrorCode INT
            , @ErrorDescription NVARCHAR(4000);

        WHILE 1 = 1
        BEGIN
            BEGIN TRANSACTION;
            WAITFOR
            (
                RECEIVE TOP (1) @ConversationHandle = conversation_handle
                    , @MessageBody = message_body
                    , @MessageType = message_type_name
                    , @ServiceName = service_name
                FROM dbo.SimpleQueue
            ), TIMEOUT 5000;

            IF @@ROWCOUNT = 0
            BEGIN
                ROLLBACK TRANSACTION;
                BREAK;
            END;
            ELSE IF @MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            BEGIN
                END CONVERSATION @ConversationHandle;
            END;
            ELSE IF @MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
            BEGIN
                EXECUTE ServiceBroker.ExtractError @MessageBody = @MessageBody
                    , @ErrorCode = @ErrorCode OUTPUT
                    , @ErrorDescription = @ErrorDescription OUTPUT;

                END CONVERSATION @ConversationHandle;

                INSERT INTO Logs.ConversationError (ServiceName, ConversationHandle, MessageType, MessageBody, ErrorCode, ErrorDescription)
                VALUES (@ServiceName, @ConversationHandle, @MessageType, @MessageBody, @ErrorCode, @ErrorDescription);
            END;
            COMMIT TRANSACTION;
        END;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        SET @ErrorCode = ERROR_NUMBER();
        SET @ErrorDescription = ERROR_MESSAGE();

        END CONVERSATION @ConversationHandle
            WITH ERROR = @ErrorCode
            DESCRIPTION = @ErrorDescription;

        EXECUTE Logs.LogError;
        THROW;
    END CATCH;
END;
GO
sql-server sql-server-2016
Martin Hope
Evaldas Buinauskas
Asked: 2015-06-14 06:16:32 +0800 CST
  • 1

Tenho a estrutura da tabela da seguinte forma:

CREATE TABLE [dbo].[Test] (
   [ID] [int] IDENTITY(1, 1) NOT NULL
   , [ProductID] [int] NOT NULL
   , [CountryID] [int] NOT NULL
   , CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TestData] (
   [ID] [int] IDENTITY(1, 1) NOT NULL
   , [TestID] [int] NOT NULL
   , [TimeSeries] [int] NOT NULL
   , [Values] [float] NULL
   , CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestData]
   WITH CHECK ADD CONSTRAINT [FK_Test_TestData] FOREIGN KEY ([TestID]) REFERENCES [dbo].[Test]([ID])
GO

ALTER TABLE [dbo].[TestData] CHECK CONSTRAINT [FK_Test_TestData]
GO

Alguns dados de teste:

INSERT INTO dbo.Test (ProductID, CountryID)
VALUES (1, 1), (1, 2), (1, 3), (1, 4)
    , (2, 1);

INSERT INTO dbo.TestData (TestID, TimeSeries, [Values])
VALUES (1, 2013, 0.5), (1, 2014, 1.9)
    , (2, 2013, 0.78), (2, 2014, 9.7)
    , (3, 2012, 0.77), (3, 2013, 5.6), (3, 2014, 1.54)
    , (4, 2011, 0.81), (4, 2014, 6.7);

E eu criei uma visão dinâmica a partir dele:

CREATE VIEW dbo.vw_PivotedTest
AS
SELECT ProductID, CountryID, [2011], [2012], [2013], [2014]
FROM (
    SELECT T.ProductID, T.CountryID, TD.TimeSeries, TD.[Values]
    FROM dbo.Test AS T
    LEFT JOIN dbo.TestData AS TD
        ON TD.TestID = T.ID
    ) AS S
PIVOT (
    AVG([Values])
    FOR TimeSeries IN ([2011], [2012], [2013], [2014])
    ) AS P;

E eu perguntaria assim:

SELECT ProductID, CountryID, [2011]
FROM dbo.vw_PivotedTest;

Agora vem a pergunta: é possível para o SQL Server escolher apenas linhas de dbo.TestData, onde o valor TimeSeries é apenas 2011, em vez de digitalizar tudo?

Dei uma olhada no plano de execução e parece estar verificando toda a tabela e não filtra esses resultados.

Plano de execução

sql-server sql-server-2014
Martin Hope
Evaldas Buinauskas
Asked: 2015-03-20 02:56:31 +0800 CST
  • 10

Estou aprendendo sobre planos de execução e estou experimentando diferentes consultas e comparando seu desempenho e me deparei com isso:

SELECT StatisticID
FROM (
    SELECT StatisticID, ROW_NUMBER() OVER (ORDER BY StatisticID) AS rn
    FROM FTCatalog.Statistic
    ) AS T
WHERE T.rn <= 1000
ORDER BY rn

SELECT TOP 1000 StatisticID
FROM FTCatalog.Statistic
ORDER BY StatisticID

Ambos retornam o mesmo conjunto de resultados - no entanto, o primeiro é executado mais rapidamente e consome menos recursos (pelo menos o SSMS me diz isso) Aqui estão os planos de execução: Planos de execução

Comparação do SQL Query Plan Explorer: insira a descrição da imagem aqui Alguém poderia me dar algumas dicas sobre o que realmente está acontecendo nos bastidores e por que os resultados diferem? Se precisar de mais alguma coisa, é só me avisar.

Obrigado, Evaldas.

sql-server performance