Set Size to -1 to optimize SQL Server query plan execution

[ramonsmits]

Describe the suggested improvement

Various queries are not ideally constructed resulting the SQL query cache misses.

Delayed queue table:

(@Headers nvarchar(1362),@Body varbinary(177),@DueAfterDays int,@DueAfterHours int,@DueAfterMinutes int,@DueAfterSeconds int,@DueAfterMilliseconds int) DECLARE @NOCOUNT VARCHAR(3) = 'OFF'; IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON' SET NOCOUNT ON;  DECLARE @DueAfter DATETIME = GETUTCDATE(); SET @DueAfter = DATEADD(ms, @DueAfterMilliseconds, @DueAfter); SET @DueAfter = DATEADD(s, @DueAfterSeconds, @DueAfter); SET @DueAfter = DATEADD(n, @DueAfterMinutes, @DueAfter); SET @DueAfter = DATEADD(hh, @DueAfterHours, @DueAfter); SET @DueAfter = DATEADD(d, @DueAfterDays, @DueAfter);  INSERT INTO [NsbSamplesSqlOutbox].[receiver].[Samples.SqlOutbox.Receiver.Delayed] (     Headers,     Body,     Due) VALUES (     @Headers,     @Body,     @DueAfter);  IF(@NOCOUNT = 'ON') SET NOCOUNT ON; IF(@NOCOUNT = 'OFF') SET NOCOUNT OFF;

Queue table:

(@Id uniqueidentifier,@TimeToBeReceivedMs int,@Headers nvarchar(734),@Body varbinary(max) ) DECLARE @NOCOUNT VARCHAR(3) = 'OFF'; IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON' SET NOCOUNT ON;  INSERT INTO [NsbSamplesSqlOutbox].[receiver].[Samples.SqlOutbox.Receiver] (     Id,     Recoverable,     Expires,     Headers,     Body) VALUES (     @Id,     1,     CASE WHEN @TimeToBeReceivedMs IS NOT NULL         THEN DATEADD(ms, @TimeToBeReceivedMs, GETUTCDATE()) END,     @Headers,     @Body);  IF (@NOCOUNT = 'ON') SET NOCOUNT ON; IF (@NOCOUNT = 'OFF') SET NOCOUNT OFF;
(@Id uniqueidentifier,@TimeToBeReceivedMs int,@Headers nvarchar(800),@Body varbinary(max) ) DECLARE @NOCOUNT VARCHAR(3) = 'OFF'; IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON' SET NOCOUNT ON;  INSERT INTO [NsbSamplesSqlOutbox].[sender].[Samples.SqlOutbox.Sender] (     Id,     Recoverable,     Expires,     Headers,     Body) VALUES (     @Id,     1,     CASE WHEN @TimeToBeReceivedMs IS NOT NULL         THEN DATEADD(ms, @TimeToBeReceivedMs, GETUTCDATE()) END,     @Headers,     @Body);  IF (@NOCOUNT = 'ON') SET NOCOUNT ON; IF (@NOCOUNT = 'OFF') SET NOCOUNT OFF;
(@Id uniqueidentifier,@TimeToBeReceivedMs int,@Headers nvarchar(800),@Body varbinary(max) ) DECLARE @NOCOUNT VARCHAR(3) = 'OFF'; IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON' SET NOCOUNT ON;  INSERT INTO [NsbSamplesSqlOutbox].[sender].[Samples.SqlOutbox.Sender] (     Id,     Recoverable,     Expires,     Headers,     Body) VALUES (     @Id,     1,     CASE WHEN @TimeToBeReceivedMs IS NOT NULL         THEN DATEADD(ms, @TimeToBeReceivedMs, GETUTCDATE()) END,     @Headers,     @Body);  IF (@NOCOUNT = 'ON') SET NOCOUNT ON; IF (@NOCOUNT = 'OFF') SET NOCOUNT OFF;
(@Id uniqueidentifier,@TimeToBeReceivedMs int,@Headers nvarchar(800),@Body varbinary(max) ) DECLARE @NOCOUNT VARCHAR(3) = 'OFF'; IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON' SET NOCOUNT ON;  INSERT INTO [NsbSamplesSqlOutbox].[sender].[Samples.SqlOutbox.Sender] (     Id,     Recoverable,     Expires,     Headers,     Body) VALUES (     @Id,     1,     CASE WHEN @TimeToBeReceivedMs IS NOT NULL         THEN DATEADD(ms, @TimeToBeReceivedMs, GETUTCDATE()) END,     @Headers,     @Body);  IF (@NOCOUNT = 'ON') SET NOCOUNT ON; IF (@NOCOUNT = 'OFF') SET NOCOUNT OFF;

Subscriptions:

(@Topic_0 varchar(14),@Topic_1 varchar(13),@Topic_2 varchar(18),@Topic_3 varchar(20)) SELECT DISTINCT QueueAddress FROM [NsbSamplesSqlOutbox].[dbo].[Subscriptions] WHERE Topic IN (@Topic_0, @Topic_1, @Topic_2, @Topic_3) 

Related

This was discovered while working on the following SQLP PR:

• Set Size to -1 to optimize SQL Server query plan execution NServiceBus.Persistence.Sql#1254

Additional Context

No response