Return to 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 and is this because of UPDLOCK and READPAST hints?
2. Are there other reasons?

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?

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. 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.

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

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 and is this because of UPDLOCK and READPAST hints?
2. Are there other reasons?

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.

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

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. 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.

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