September 29, 2015 at 8:12 am
Hello!
Here is the situation:
- the database has the Parameterization set to FORCED
- if i run a query like:
SELECT * FROM TableT WHERE col1 = 'aaa' and col2 = 'bbb'
, where the values of 'aaa' and 'bbb' can change, it does reuse the execution plan (which is good)
- if I run a query like:
exec sp_executesql N'SELECT * FROM table WHERE col1 = ''aaa'' and col2 = @colb', N'@colb nvarchar(50)', @colb='bbb'
, where again the valus of "aaa" and "bbb" can change, it does not reuse the execution plan when values of "aaa" changes.
In fact, it generates a new execution plan each time other values are used in place of "aaa".
Is this behavior as expected? If yes, could one please explain why it does not reuse the execution plan or how could I somehow enforce it to use it?
Thank you!
September 29, 2015 at 8:15 am
September 29, 2015 at 11:41 am
Just know that forced parameterization is only going to be of limited use in getting plans to be reused. You're likely going to have to go to fully parameterized queries or stored procedures to get the full functionality of parameterization.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 29, 2015 at 11:01 pm
Yes, already recommended fully parameterized queries. Thank you!
So there is nothing i can do to force the query to reuse some execution plan?
I mention that I cannot change the query (can't add any query hints) ; it is send by an ORM framework and developers will not change it any time soon.
September 30, 2015 at 3:21 am
You can try doing plan forcing through plan guides. But it's also subject to the issues around white space and value changes within the code. As long as the code itself can be changed you're looking at variability in the plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply