SQL Server 2014 – PAGELATCH_EX Occurs in Update Query with UPDLOCK, READPAST

blockingsql serversql server 2014

We have a queue organized with a table on MS SQL Server 2014:

CREATE TABLE [queue].[Messages](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Status] [tinyint] NOT NULL,
    [Data] [nvarchar](max) NOT NULL,
    [CreationDateUtc] [datetime] NOT NULL
)

CREATE NONCLUSTERED INDEX [IX_Messages_Status] ON [queue].[Messages]
([Status] ASC)

Stored procedure to send message:

ALTER PROCEDURE [queue].[SendMessage]
    @MessageBody NVARCHAR(MAX),
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [queue].[Messages]
    (
        Data,
    )
    VALUES
    (
       @MessageBody,
    )
END

And a stored procedure to receive messages:

ALTER PROCEDURE [queue].[ReceiveMessage]
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE TOP (1) m 
    SET
       m.Status = 2,
       m.StartDateUtc = GETUTCDATE()
    OUTPUT
       INSERTED.*
    FROM [queue].[Messages] m WITH (UPDLOCK, READPAST)
    WHERE m.Status = 1
END

This stored procedure executes every second to check new messages in queue.
Very often we get blocks (PAGELATCH_EX) on this query even if the queue is empty:
enter image description here

First idea was – diff-backups, but they are scheduled at different times:
enter image description here

Other queries don't make blocks so often.

  1. Am I right that this is due to UPDLOCK and READPAST hints?
  2. Are there other reasons?

Best Answer

This is because you use a Status field for messages and leave unreceiveable messages in the queue. Retaining messages (keeping them after process, with a different status) leads to queue bloat, contention, poor plan choices and general slow processing.

Follow Using tables as Queues and do destructive message processing:

DELETE TOP (1) 
FROM [queue].[Messages] m WITH (ROWLOCK, READPAST)
OUTPUT deleted.*;

Also drop all indexes on the queue. The linked article has details how to achieve fancier behavior like FIFO or Pending.

Use queues only for events, not for state.

Related Question