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

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.

  • @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. – sp_BlitzErik Aug 30 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 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. – sp_BlitzErik Aug 30 at 17:56

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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