Parameter substitution increases plan cache bloat

[tivivi63]

SQLQueryStress is a great tool, really. But I noticed that generating fake parameter values from a query tends to create multiple plan cache entries for the same exact command text, when it comes to nvarchar command parameter types (that is obviously not a problem with integer-types parameters).

The risk here is to bloat plan cache when param substitution query return a ton of different-length values. In my example, "random" parameter generator consists in querying a name from a users table and this created 14 different plans with the same statement, mapping name length occurrence natural distribution to execution count.

In the UI, I can't see any way to customise inferred-from-query parameter type, automatically set to nvarchar where expected type is nvarchar(32) :

Any idea how to work around this situation? Thanks,

[ErikEJ]

So if you had a option to choose nvarchar(50) that would work for you?

[tivivi63]

For my current needs, nvarchar(50) shall fit indeed, but this is not a global solution.

Actually, my favorite option would be to allow specifying (or not) a free-text length as a new column in the Parameter mappings grid, with voided default value, even if I know it is harder to complete 🥴 But that should cover every future need.

Thanks for the work done,