3

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.

This image shows the plancount an individual query is creating and the queries

3

1 Answer 1

13

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.