PAGELATCH_EX occures in update query with UPDLOCK, READPAST

Question

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:

First idea was - diff-backups, but they are scheduled at different times:

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?

Answer 1

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.