Performance & Plan Cache Issues

[Drewan]

I'm getting very high cpu usage using rebus pub/sub and saga in an azure database.

I've noticed a high number of sql query plan compilations which I think is due to rebus not setting the parameter length when inserting varbinary(max) column using ado.net.

Would adding -1 allow sql server to compile and reuse a single plan?

command.Parameters.Add("headers", SqlDbType.VarBinary, -1).Value = serializedHeaders;
command.Parameters.Add("body", SqlDbType.VarBinary, -1).Value = message.Body;

Also noticed that this query generates a new plan every time.

SET NOCOUNT ON

  ;WITH TopCTE AS (
    SELECT  TOP 1
        [id],
        [headers],
        [body]
    FROM  [Schema].[Table] M WITH (ROWLOCK, READPAST)
    WHERE  
                M.[visible] < getdate()
    AND    M.[expiration] > getdate()
    ORDER
    BY    [priority] DESC,
        [id] ASC
  )
  DELETE  FROM TopCTE
  OUTPUT  deleted.[id] as [id],
      deleted.[headers] as [headers],
      deleted.[body] as [body]

This article discusses the impact not setting parameter lengths can have

https://blogs.msdn.microsoft.com/psssql/2010/10/05/query-performance-and-plan-cache-issues-when-parameter-length-not-specified-correctly/

[mookid8000]

Would adding -1 allow sql server to compile and reuse a single plan?

Never heard you could do that.... 🤔 I've seen other people set the length to the first-coming power of 2, which would drastically limit the number of cached query plans, so I will go do that.

Also noticed that this query generates a new plan every time.

If that's the case, then I don't know what to do. The query is the one responsible for receiving the next message, so if issuing a query like that generates a new query plan every time, then I have no idea what would help.

Can you somehow see in which way the cached plans differ?

[mookid8000]

I've released Rebus.SqlServer 5.0.1 now, which sets the length on all parameterized queries with VARBINARY and NVARCHAR. If you try it out, I'd be happy to hear your feedback 😄

[Drewan]

Apologies I misunderstood the difference between a "prepared" and "adhoc" query execution plan. The query in question is "adhoc" which just means it isn't parameterised not that it isn't cached.

This SO question explains https://stackoverflow.com/questions/38072550/what-is-the-difference-between-ad-hoc-and-prepared-query-in-sql-server-plan-cach