Yesterday we took our SQL Server 2012 instance which has been processing messages for several years without any issues (except a new periodic performance issue that started several months ago, details below), and upgraded it from SQL Server 2012 to SQL Server 2017+CU29 (KB5010786). We have tried moving compat from 2012 to 2017 and while it helps with a new issues we're seeing, performance is not great.
But the big thing is: since then, we've had the queue spontaneously disable itself twice. It works for minutes/hours, then poof the queue is disabled. We have logging and nothing's giving us anything. We had briefly turned on Query Store, but after the queue disabled the first time, we went looking for similar issue. We found a thread online that said they were having similar problems and that it was Query Store, so we immediately flipped it to Read-Only (we had turned it on in order to try and fix a performance issue we see on Mondays, where it grabs a bad plan and rebooting it seems to be the only fix, but we don't see that the rest of the week). Also of note, this doesn't update rows, it just inserts new rows into a series of hourly tables.
We're also seeing massive locking on LCK_M_IX on where we didn't before. Looking at that next. It's on a part of the code that does an insert into a table, where the output is generated from a CLR. (INSERT INTO table FROM SELECT clr). Moving from 2012 to 2017 seems to have changed that behavior, but it's still seems like it's slow overall, but I'm terrified about it spontaneously disabling again.
We are running the same load on two separate servers, so I have the ability to compare things.
The "disabled" message in our logging table appears several times all at the same time (I'm guessing once per thread). Nothing in the SQL Error Log. Interestingly, in some of the rows in the logging table, the message_body is NULL, but has a body in others. But we see no errors for several minutes before it occurred in either.
The service queue "ODS_TargetQueue" is currently disabled.
We're also running a Extended Event that logs any severity 11+ errors.
All it's showing is
The service queue "ODS_TargetQueue" is currently disabled.
We are also seeing this sporadically which we normally don't see unless we're having log backup issues:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
We have also seen this a handful of times this morning, which seems to be new:
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.
The queue:
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
The procedure
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