Parameterized query creating many plans
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.
James Z's link is where I started understanding this also. Then prompted this question which might help your developers: dba.stackexchange.com/questions/195937/…
– Peter
Aug 30 at 18:14
Show how the application constructs and executes the query. That's not it - that's just how the plan cache shows it.
– Aaron Bertrand♦
Aug 30 at 23:15
1 Answer
1
The length of @P1 is different in all of those.
@P1
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
Required, but never shown
Required, but never shown
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.
Something like this happening: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…
– James Z
Aug 30 at 17:18