February 15, 2021 at 4:36 pm
Hi all,
Our application has some parameterised dynamic SQL that looks like this;
SET NOCOUNT ON; DECLARE @HOST VARCHAR(500); SELECT SOME, STUFF FROM MYTABLE WHERE MAINHOST = @HOST OR BACKUPHOST = @HOST
When I look at the execution plan cache it is caching an execution plan every time this is executed because it is defining a @p0 parameter as a char(n) where n is the length of the parameter passed in the query. So the SQL for the execution plan looks like;
(@P1 char(25))SET NOCOUNT ON; DECLARE @HOST VARCHAR(500) = @P1...
Is there anything I can do about these sorts of query structures to prevent query caching for each and every query..?
Thanks in advance
Rolf
February 15, 2021 at 5:28 pm
This might help: https://docs.microsoft.com/en-us/sql/relational-databases/performance/specify-query-parameterization-behavior-by-using-plan-guides?view=sql-server-ver15
But - research and test fully on your system.
Also - take a look at the setting 'Optimize for ad-hoc workloads'. This might also be an option.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 15, 2021 at 8:26 pm
You might also want to look into the correct use of sp_execute for such things.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2021 at 6:40 am
Double check configuration setting 'optimize for ad hoc workloads'
exec sp_configure 'optimize for ad hoc workloads', 1;
reconfigure
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 16, 2021 at 2:05 pm
If you set the parameters yourself, as Jeff is suggesting, then you don't have to worry about simple parameterization or forced parameterization (either of which is what you're hitting). That's the best solution. Otherwise, yeah, with that kind of ad hoc querying, you're very likely to get multiple plans in cache.
I do agree with others, setting optimize for ad hoc to on will absolutely help. In fact, I think that ought to be on by default. I've not heard of anyone having actual problems from it. Sometimes it doesn't help, but I've not heard of it hurting. However, this will only help. If you want to resolve it, parameterize the queries appropriately.
"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
February 16, 2021 at 3:04 pm
1 Be sure to specify the schema name on the table, assuming of course that it doesn't change from user to user.
FROM dbo.MYTABLE
2. Make the variable the exact same type and length as the table column, if at all possible. What data type(s) are MAINHOST and BACKUPHOST? If they are char(), then the variable should not be varchar(), since varchar has a higher precedence.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply