Parameterized query creating many plans

I have some queries that are parameterized, but they are still creating a new execution plan each time. I am using SQL Server 2016.

Queries are like:

(@P1 varchar(1043),@P2 varchar(6))
UPDATE table 
SET FILEDATA=@P1 
WHERE FILEID=@P2

This query is not using the already generated execution plan from the cache, rather it is creating a new plan each time.

The length of @P1 is different in all of those.

You're getting different plans because you're not explicitly setting parameter lengths in your code.

Any difference in strings, no matter how minor, will generate a new plan. Data type sizes, spaces, comments, all of them will cause new entries in the plan cache. Here's a demo video illustrating the problem.

The solution is to define your parameter with the same length and type as the length and type of the column it's interacting with in the query. This will give you a single plan, and reliable plan reuse.

The way you do this will vary based on how the insert is being performed. If you're using stored procedures, you can update the parameters to use the same length as the columns. If you're using .NET's AddWithValue() method (which is evil), use one of the APIs that allows you to set the parameter length.

StackExchange Database Administrators Q#216330, answer score: 13