Skip to main content
1 of 4
Backs
  • 133
  • 6

PAGELATCH_EX occures in update query with UPDLOCK, READPAST

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)

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. So, very often we get blocks (PAGELATCH_EX) on this query even queue is empty: enter image description here

First idea was - diff-backups, but they are scheduled in tifferent time: enter image description here

Other queries don't make blocks so often.

  1. Am i right and this is because of UPDLOCK and READPAST hints?
  2. Are there other reasons?
Backs
  • 133
  • 6