服务经纪队列“目前是禁用的”从SQL Server 2012升级到2017年后，几次

Question

昨天，我们采用了SQL Server 2012实例，该实例已经处理了几年没有任何问题（除了几个月前开始的新定期性能问题，下面的详细信息），并将其从SQL Server 2012升级到SQL Server 2017+CU29（ KB5010786）。我们尝试从2012年到2017年移动兼容，尽管它有助于解决我们看到的新问题，但性能并不是很棒。

但最大的事情是：从那时起，我们已经两次自发地禁用了队列。它可以使用几分钟/小时，然后 poof 队列被禁用。我们有伐木，没有什么给我们任何东西。我们已经短暂地打开了查询商店，但是在第一次禁用队列之后，我们去寻找类似的问题。我们在网上找到了一个线程，说他们有类似的问题，并且是查询商店，所以我们立即将其翻转为只读（我们已经打开了它，以便尝试解决我们星期一看到的性能问题，抓住一个糟糕的计划并重新启动它似乎是唯一的解决方法，但我们看不到剩下的一周）。还要注意的是，这不会更新行，它只是将新行插入一系列小时表中。

我们还看到LCK_M_IX上的大规模锁定在我们从未去过的地方。查看下一个。它在代码的一部分上，将插入到表中，其中输出是从CLR生成的。 （从选择CLR插入表中）。从2012年到2017年，似乎已经改变了这种行为，但总体上似乎仍然很慢，但是我对此感到恐惧，再次自发地禁用。

我们在两个单独的服务器上运行相同的负载，因此我有能力比较事物。

我们的记录表中​​的“禁用”消息同时出现了几次（我猜每线程一次）。 SQL错误日志中没有任何内容。有趣的是，在伐木表中的某些行中，message_body为null，但在其他行中有一个身体。但是我们在两分钟内都没有发现任何错误。

The service queue "ODS_TargetQueue" is currently disabled.

我们还运行一个扩展事件，该事件记录了任何严重性11+错误。
它所显示的是，

The service queue "ODS_TargetQueue" is currently disabled.

除非我们有登录问题，否则我们也偶尔会看到它通常看不到的：

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

我们也已经看到了今天早上几次的时间，这似乎是新的：

Process ID 57 attempted to unlock a resource it does not own: METADATA: database_id = 7 CONVERSATION_ENDPOINT_RECV($hash = 0x9904a343:0x9c8327f9:0x4b), lockPartitionId = 0. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.

队列：

CREATE QUEUE [svcBroker].[ODS_TargetQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [svcBroker].[ODS_TargetQueue_Receive] , MAX_QUEUE_READERS = 30 , EXECUTE AS OWNER  ), POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY] 
GO

该过程

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [svcBroker].[ODS_TargetQueue_Receive]
AS

BEGIN
    set nocount on

    -- Variable table for received messages.
    DECLARE @receive_table TABLE(
            queuing_order BIGINT,
            conversation_handle UNIQUEIDENTIFIER,
            message_type_name SYSNAME,
            message_body xml);
  
    -- Cursor for received message table.

    DECLARE message_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
            FOR SELECT
            conversation_handle,
            message_type_name,
            message_body
            FROM @receive_table ORDER BY queuing_order;
 
     DECLARE @conversation_handle UNIQUEIDENTIFIER;
     DECLARE @message_type SYSNAME;
     DECLARE @message_body xml;
 
     -- Error variables.
     DECLARE @error_number INT;
     DECLARE @error_message VARCHAR(4000);
     DECLARE @error_severity INT;
     DECLARE @error_state INT;
     DECLARE @error_procedure SYSNAME;
     DECLARE @error_line INT;
     DECLARE @error_dialog VARCHAR(50);

     BEGIN TRY
       WHILE (1 = 1)
       BEGIN
         BEGIN TRANSACTION;

         -- Receive all available messages into the table.
         -- Wait 5 seconds for messages.

         WAITFOR (
            RECEIVE TOP (1000)
               [queuing_order],
               [conversation_handle],
               [message_type_name],
               convert(xml, [message_body])
            FROM svcBroker.ODS_TargetQueue
            INTO @receive_table
         ), TIMEOUT 2000;

         IF @@ROWCOUNT = 0
         BEGIN

              COMMIT;
              BREAK;
         END
         ELSE
         BEGIN
              OPEN message_cursor;
              WHILE (1=1)
              BEGIN
                  FETCH NEXT FROM message_cursor
                            INTO @conversation_handle,
                                 @message_type,
                                 @message_body;
       
                  IF (@@FETCH_STATUS != 0) BREAK;
 
                  -- Process a message.
                  -- If an exception occurs, catch and attempt to recover.

                  BEGIN TRY
 
                      IF @message_type = 'svcBroker_ods_claim_request'
                      BEGIN
                          exec ParseRequestMessages @message_body
                      END
                      ELSE IF @message_type in ('svcBroker_EndOfStream', 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
                      BEGIN
                          -- initiator is signaling end of message stream: end the dialog
                          END CONVERSATION @conversation_handle;
                      END
                      ELSE IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
                      BEGIN
                           -- If the message_type indicates that the message is an error,
                           -- raise the error and end the conversation.
                           WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb)
                           SELECT
                           @error_number = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Code)[1]', 'INT'),
                           @error_message = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Description)[1]', 'VARCHAR(4000)');
                           SET @error_dialog = CAST(@conversation_handle AS VARCHAR(50));
                           RAISERROR('Error in dialog %s: %s (%i)', 16, 1, @error_dialog, @error_message, @error_number);
                           END CONVERSATION @conversation_handle;
                      END
                  END TRY
                  BEGIN CATCH
                     SET @error_number = ERROR_NUMBER();
                     SET @error_message = ERROR_MESSAGE();
                     SET @error_severity = ERROR_SEVERITY();
                     SET @error_state = ERROR_STATE();

                     SET @error_procedure = ERROR_PROCEDURE();

                     SET @error_line = ERROR_LINE();

                     IF XACT_STATE() = -1
                     BEGIN
                          -- The transaction is doomed. Only rollback possible.
                          -- This could disable the queue if done 5 times consecutively!
                          ROLLBACK TRANSACTION;
             
                          -- Record the error.
                          BEGIN TRANSACTION;
                            INSERT  INTO svcBroker.target_processing_errors (
                                    error_conversation,[error_number],[error_message],[error_severity],
                                    [error_state],[error_procedure],[error_line],[doomed_transaction],
                                    [message_body])
                            VALUES  (NULL, @error_number, @error_message,@error_severity,
                                    @error_state, @error_procedure, @error_line, 1, @message_body);
                          COMMIT;
 
                          -- For this level of error, it is best to exit the proc
                          -- and give the queue monitor control.
                          -- Breaking to the outer catch will accomplish this.
                          RAISERROR ('Message processing error', 16, 1);
                     END
                     ELSE IF XACT_STATE() = 1
                     BEGIN
                          -- Record error and continue processing messages.
                          -- Failing message could also be put aside for later processing here.
                          -- Otherwise it will be discarded.
                            INSERT  INTO svcBroker.target_processing_errors (
                                    error_conversation,[error_number],[error_message],[error_severity],
                                    [error_state],[error_procedure],[error_line],[doomed_transaction],
                                    [message_body])
                            VALUES  (NULL, @error_number, @error_message,@error_severity,
                                    @error_state, @error_procedure, @error_line, 0, @message_body);
                     END
                  END CATCH
              END
              CLOSE message_cursor;
              DELETE @receive_table;
         END
         COMMIT;
       END
     END TRY
     BEGIN CATCH
         -- Process the error and exit the proc to give the queue monitor control
         SET @error_number = ERROR_NUMBER();
         SET @error_message = ERROR_MESSAGE();
         SET @error_severity = ERROR_SEVERITY();
         SET @error_state = ERROR_STATE();
         SET @error_procedure = ERROR_PROCEDURE();
         SET @error_line = ERROR_LINE();
 
         IF XACT_STATE() = -1
         BEGIN
              -- The transaction is doomed. Only rollback possible.
              -- This could disable the queue if done 5 times consecutively!

              ROLLBACK TRANSACTION;

              -- Record the error.
              BEGIN TRANSACTION;
              INSERT INTO svcBroker.target_processing_errors (
                    error_conversation,[error_number],[error_message],[error_severity],
                    [error_state],[error_procedure],[error_line],[doomed_transaction],
                    [message_body])
              VALUES(NULL, @error_number, @error_message,@error_severity, @error_state, @error_procedure, @error_line, 1, @message_body);
              COMMIT;
         END
         ELSE IF XACT_STATE() = 1
         BEGIN
              -- Record error and commit transaction.
              -- Here you could also save anything else you want before exiting.
              INSERT INTO svcBroker.target_processing_errors (
                    error_conversation,[error_number],[error_message],[error_severity],
                    [error_state],[error_procedure],[error_line],[doomed_transaction],
                    [message_body])
              VALUES(NULL, @error_number, @error_message, @error_severity, @error_state, @error_procedure, @error_line, 0, @message_body);
              COMMIT;
         END
    END CATCH
END;


GO