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:
First idea was - diff-backups, but they are scheduled in tifferent time:
Other queries don't make blocks so often.
- Am i right and this is because of
UPDLOCK
andREADPAST
hints? - Are there other reasons?