Parameter Sniffing and Multiple Plans in Cache
3
votes
2
answers
1623
views
We have a multi-tenanted database. FirmID is the partition key and we have lots of different firms.
I am running into a parameter sniffing issue and I am having a heck of a time getting around it.
I would rather not use any [Options] on the query.
My latest thought was to change the name of the parameter I am using for the firm. In the snippet below you will see that instead of using @FirmID I called it @Firm611 where 611 is the actual firm of the ID. This will give me a unique query for every firm.
My question is am I reading that result right? Even though I am changing the parameter name, is it really still using the same plan and parameter sniffing?
select
c.ID [_cid],
c.Name [Name]
from vwClaims c with(nolock)
where c.FirmID=@Firm611
and (c.Name is not null and c.Name!='')
select
c.ID [_cid],
c.Name [Name]
from vwClaims c with(nolock)
where c.FirmID=@Firm625
and (c.Name is not null and c.Name!='')
After running Brent Ozar's sp_BlitzCache, I found that it is just compiling down to the same query and causing duplicate cache entries:

Asked by Joshua Grippo
(140 rep)
May 14, 2021, 08:10 PM
Last activity: May 15, 2021, 03:21 PM
Last activity: May 15, 2021, 03:21 PM