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 

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


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.

| improve this answer | |
  • @HardikTalwar link updated, but the video doesn't show you how to set lengths in your app code, it just shows you how different things affect plan caching, etc. – Erik Darling Aug 30 '18 at 17:24
  • I followed the video and did the same show in that video thereby running that stored procedure I am getting some queries which are exactly same there is no difference of gaps or anything they are same but still executing different times – Hardik Talwar Aug 30 '18 at 17:54
  • @HardikTalwar I'm not sure if that's a different question than your original one or not. If it's a different question, feel free to ask it on the main site. If it's another point about your original question, please update it with the relevant information. – Erik Darling Aug 30 '18 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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