主页 / user-52567

Evaldas Buinauskas's questions

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

在我们的遗产中，我们设置了 Service Broker 来执行一项操作：

  1. 根据计划执行查询，并通过服务代理将其结果发送到远程服务器上的其他服务
  2. 消息被目标服务器上的激活过程接收和消费。

问题是由于某种原因，消息在目标服务器上被多次使用，尽管它只发送了一次。

我对 Service Broker 还很陌生，正在寻求关于什么可能是确切问题以及可能是什么原因的建议/指导。

开始对话框的过程代码：

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

以及目标上的激活SP：

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;

这是应该结束对话的启动器上的激活 SP：

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

我的表结构如下：

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

部分测试数据：

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);

我从中创建了 Pivoted 视图：

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;

我会这样查询：

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

现在问题来了：SQL Server 是否可以只从 dbo.TestData 中选取行，其中 TimeSeries 值仅为 2011 而不是扫描所有内容？

我查看了执行计划，它似乎在扫描整个表并且没有过滤掉这些结果。

执行计划

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

我正在学习执行计划并尝试不同的查询并比较它们的性能并偶然发现：

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

它们都返回相同的结果集 - 但是第一个执行得更快并且资源消耗更少（至少 SSMS 告诉我）以下是执行计划： 执行计划

SQL Query Plan Explorer 的比较： 在此处输入图像描述 谁能给我一些关于幕后实际发生的事情以及为什么结果不同的见解？如果您还有其他需要 - 请告诉我。

谢谢，埃瓦尔达斯。

sql-server performance