3

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?

0
6

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.

2
  • 2
    Once per minute is as good as a no-brainer threshold as any. Adding that I've seen benefit with OPTION(RECOMPILE) in kitchen sink queries that execute several times per second (with <100ms compile time). The threshold is one of the many "it depends" choices and there may be better options (no pun intended) like OPTIMIZE FOR or forcing compromise plan. – Dan Guzman May 15 at 10:59
  • After reading and re-reading, I think I was not understanding how query-hash was working. I didn't realize the hash itself is sans whitespace and parameters. Adding the different parameter name is giving me a new entry in cache and the entry may or may not be different depending on what sql server believes is the best course of action for the parameters provided. I know that having a plan for each company could cause lots of duplicate plans, but only one for each company and that will be reused. I think that is better than having one re-used plan that might be really bad. – Joshua Grippo May 17 at 17:59
6

I found that it is just compiling down to the same query and causing duplicate cache entries

I can see why you would read the output that way (DistinctPlanCount) but it seems the code has a bug. The COUNT aggregates should reference query_plan_hash not query_hash (which is a constant).

FROM ( SELECT query_hash,
COUNT(DISTINCT(query_hash)) AS DistinctPlanCount,
COUNT(query_hash) AS PlanCount

FROM sys.dm_exec_query_stats
GROUP BY query_hash
) AS q

should be:

FROM
(
    SELECT
        qs.query_hash,
        COUNT(DISTINCT(qs.query_plan_hash)) AS DistinctPlanCount,
        COUNT(qs.query_plan_hash) AS PlanCount
    FROM sys.dm_exec_query_stats AS qs
    GROUP BY
        qs.query_hash
) AS q

You're using quite an old version of the procedure, but the issue still exists in the very latest (though other fixes have been applied).

Even though I am changing the parameter name, is it really still using the same plan and parameter sniffing?

No, you are getting separate plans per parameter name as expected. Each plan (per firm) sniffs its own parameters on first execution and reuses that plan on subsequent calls, until a recompile occurs for whatever reason.

In other words, your scheme is working as you hoped. You can still hit problems when the per-firm parameter value on first execution does not generate a plan that is reasonable for all later executions for the same firm. That's just the normal parameter-sensitive-plan problem, with the usual workarounds.

As David Browne remarked:

This is one of the big problems with putting all your tenants in the same database. You should plan on at least segregating "big" and "small" tenants. Also it's totally appropriate to simply hard-code the tenantId in your queries to get tenant-specific plans. Parameters are for when you want shared plans.

1
  • I do actually want shared plans. However, I want the plan to be shared for only within a company. I had originally had the parameter named as @FirmID and added a comment to the end of the SQL with the FirmID in it to cause it to gen a new plan. It looks like I was not understanding the nuances of Query_Hash. Thanks for the info. – Joshua Grippo May 17 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.