Search code examples
sql-serverrebus

Rebus SqlServerTransport, "MissingIndex" from SQL Server seems incorrect, or something isn't working as intended

SQL Server is reporting that the Messages table used by Rebus and it's SQLServerTransport are needing a different index created for it. I feel the current index setup is correct, but something about the usage of a CTE, deleting, and outputting is causing SQL Server to do something.

Is SQL Server wrong on the index or is the CTE doing something unexpected here?

SQL Server is requesting and index along the lines of

CREATE INDEX IX_<NewNameHere> 
ON [MessageQueues].[dbo].[Messages] ([id]) 
INCLUDE ([recipient], [priority])

where are currently the clustered index is like

PRIMARY KEY CLUSTERED 
(
    [recipient] ASC,
    [priority] ASC,
    [id] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

and here is the only query being ran against this DB. 

exec sp_executesql N'SET NOCOUNT ON

;WITH TopCTE AS 
(
    SELECT TOP 1
        [id], [headers], [body]
    FROM
        [Messages] M WITH (ROWLOCK, READPAST)
    WHERE    
        M.[recipient] = @recipient
        AND M.[visible] < getdate()
        AND M.[expiration] > getdate()
    ORDER BY        
        [priority] ASC, [id] ASC
)
DELETE FROM TopCTE
OUTPUT deleted.[id] as [id],
       deleted.[headers] as [headers],
       deleted.[body] as [body]
', N'@recipient nvarchar(200)', @recipient=N'Location'

Update: I was wrong on the query that was causing the wanted index. Rebus does a periodic message PerformExpiredMessagesCleanupCycle which runs this query

``` DELETE FROM [{_tableName}] WHERE [id] IN ( SELECT TOP 1 [id] FROM [{_tableName}] WITH (ROWLOCK, READPAST) WHERE [recipient] = @recipient AND [expiration] < getdate() )

```

is where the wanted index is coming from.

Solution

  • Rebus, for each queue, does a periodic clean up to delete all expired messages. It did this command.CommandText =$@" DELETE FROM [{_tableName}] WHERE [id] IN ( AND [expiration] < getdate() ) ";

    If the query was like this command.CommandText =$@" ;with TopCTE as ( SELECT TOP 1 [id] FROM [{_tableName}] WITH (ROWLOCK, READPAST) WHERE [recipient] = @recipient AND [expiration] < getdate() ) DELETE FROM TopCTE "; then there wouldn't be a need to do the secondary lookup which is causing the ID column to be scanned.