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:
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?