Mixing
Parameterized query creating many plans
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
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.
sql-server execution-plan parameter plan-cache
edited Aug 30 at 17:28
Paul White♦
46.5k14251398
asked Aug 30 at 17:03
Hardik Talwar
111
add a comment |Â
up vote
2
down vote
favorite
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.
sql-server execution-plan parameter plan-cache
edited Aug 30 at 17:28
Paul White♦
46.5k14251398
asked Aug 30 at 17:03
Hardik Talwar
111
2
Something like this happening: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…
– James Z
Aug 30 at 17:18
1
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
add a comment |Â
up vote
2
down vote
favorite
up vote
2
down vote
favorite
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.
sql-server execution-plan parameter plan-cache
edited Aug 30 at 17:28
Paul White♦
46.5k14251398
asked Aug 30 at 17:03
Hardik Talwar
111
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.
sql-server execution-plan parameter plan-cache
edited Aug 30 at 17:28
Paul White♦
46.5k14251398
asked Aug 30 at 17:03
Hardik Talwar
111
edited Aug 30 at 17:28
Paul White♦
46.5k14251398
edited Aug 30 at 17:28
Paul White♦
46.5k14251398
edited Aug 30 at 17:28
Paul White♦
46.5k14251398
46.5k14251398
asked Aug 30 at 17:03
Hardik Talwar
111
asked Aug 30 at 17:03
Hardik Talwar
111
asked Aug 30 at 17:03
Hardik Talwar
111
111
2
Something like this happening: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…
– James Z
Aug 30 at 17:18
1
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
add a comment |Â
2
Something like this happening: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…
– James Z
Aug 30 at 17:18
1
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
2
2
Something like this happening: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…
– James Z
Aug 30 at 17:18
Something like this happening: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…
– James Z
Aug 30 at 17:18
1
1
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
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
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
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
8
down vote
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.
answered Aug 30 at 17:06
sp_BlitzErik
19.4k1161101
@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
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
8
down vote
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.
answered Aug 30 at 17:06
sp_BlitzErik
19.4k1161101
@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
add a comment |Â
up vote
8
down vote
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.
answered Aug 30 at 17:06
sp_BlitzErik
19.4k1161101
@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
add a comment |Â
up vote
8
down vote
up vote
8
down vote
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.
answered Aug 30 at 17:06
sp_BlitzErik
19.4k1161101
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.
answered Aug 30 at 17:06
sp_BlitzErik
19.4k1161101
edited Aug 30 at 17:23
answered Aug 30 at 17:06
sp_BlitzErik
19.4k1161101
answered Aug 30 at 17:06
sp_BlitzErik
19.4k1161101
answered Aug 30 at 17:06
sp_BlitzErik
19.4k1161101
19.4k1161101
@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
add a comment |Â
@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
@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
@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
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
@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
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216330%2fparameterized-query-creating-many-plans%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
2
Something like this happening: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…
– James Z
Aug 30 at 17:18
1
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