Parameter Sniffing and Multiple Plans in Cache

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?

There are a few different questions in here.

Q: If two identical queries use two different parameter names, what does SQL Server do?

SQL Server will build a separate execution plan for each of the queries. SQL Server considers even the slightest change of the text - even as simple as a space - to be a different query.

Each variation will get compiled and cached completely independently of the other.

Q: Will these two queries get different plans or the same plan?

SQL Server will examine the contents of the parameter and build an execution plan based on that. @Firm611's plan will be compiled based on the selectivity of @Firm611, and @Firm625's plan will be compiled based on the selectivity of @Firm625.

If the two parameters happen to have the same selectivity, both compiled plans may end up with the exact same shape, same index usage, same memory grants, etc. However, they will still be compiled independently and cached separately.

Q: What does the warning mean in sp_BlitzCache?

The warning indicates that you have two "different" queries that aren't really different - they're getting plans that are shaped the same. That means you're wasting time building multiple versions of them because:

• You're wasting CPU resources repeatedly compiling the same plan

• You're wasting memory caching different plans (that are really the same plan)

Q: Would OPTION (RECOMPILE) fix this?

No, because then all of them would get recompiled every time they run, leading to higher CPU usage. I only recommend recompile hints when a query runs once per minute or less - if it runs more often than that, I start worrying about the CPU overhead due to the frequent compilations of all the queries where you're using that trick.

I use a baseline of once per minute because that's only this query - you have to keep in mind the total workload on the server. If you slap recompile hints on every query, you can end up with 100% CPU utilization - even if each individual query is only run 1x per second, that's a lot of compiles when you consider every query in total.

StackExchange Database Administrators Q#291597, answer score: 6