Higher SQL Server CPU/RAM utilization SQL dialect queries not hitting SQL query cache

[andreasohlund]

Symptoms

Not specifying the size of the parameters forces the execution engine to convert parameters to the max size specified in the column definition. This increases CPU usage on the database server.

While this should apply to all commands issued by persister we have a user report specifically for the outbox inserts https://discuss.particular.net/t/nservicebus-inefficient-sql-with-persistence/2945

Who's affected

SQL Dialect users (Microsoft SQL Server, Azure SQL, and other hosted Microsoft SQL instance types)

Root cause

The SQL query cache is not used which results in SQL Server to create a new execution plan for every a lot of queries where the param length differs.