Sample Header Ad - 728x90

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.
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: Query Plans associated with the same Query Hash 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?
Asked by Joshua Grippo (140 rep)
May 14, 2021, 08:10 PM
Last activity: May 15, 2021, 03:21 PM