Can Stats per partition prevent parameter sniffing problem when data varies by wide margin in partitions?

Question

Currently we have a Datawarehouse that is holding data from multiple tenants. SQL server is on version 2019. Same schema for all the tenant databases and the data from all the tenants is consolidated in the Datawarehouse. Data is partitioned in the datawarehouse on Tenant basis. We have parameter sniffing problem with the new dashboard as the data varies a lot between the tenants. Some tenants have data less than 10000 rows and a couple of tenants have data ranging up to 5 million rows. Due to this, dashboard performance is bad for large tenants if the execution plan is built based on a smaller tenant.

Suggestions on the internet are available asking to use Recompile hint or Optimize for hint etc. But I have a doubt on the basics of this parameter sniffing. As statistics are maintained by the SQL server at partition level, is this statistics information not used to see if the plan built is right for a new run time value? Before executing, are stats ever compared for the plans built on compile time and run time to see if they are valid and the associated plan is valid?

Kindly advise.

It is only going to work if the partition id is statically available at compile time. A cross-partition query is never going to be able to use statistics specifically for one partition. Even a paramter specifying the partition id will not help — Charlieface, Commented Mar 16, 2021 at 12:04
There is only one stats blob per index even if the table is partitioned. When you update stats at the partition level, those are merged into the global stats. For a data warehouse workload, I suggest you just add OPTION(RECOMPILE) and be done with it. — Dan Guzman, Commented Mar 16, 2021 at 12:06
@DanGuzman A filtered index or filtered statistics could work, obviously the partition id needs to be statically known — Charlieface, Commented Mar 17, 2021 at 0:32
@Charlieface - when you say make partition id as statically available at compile time, do you mean provide partition id as a parameter to the stored procedures? Would this not recur the problem of parameter sniffing with a different partition id? — PraveenDS, Commented Mar 17, 2021 at 8:14
@DanGuzman - Following things are stopping me from using OPTION(RECOMPILE): 1. Since there are up to 500 tenants and broadly they divide into two (small and large) or three (small, medium and large) categories. So once a plan is available for that category of tenants, all the remaining tenants shall be able to reuse that plan. 2. We are going to have quite a number of reports and dashboards and I am little worried that this recompile hint could start to show impact as we grow once it is used at every place. — PraveenDS, Commented Mar 17, 2021 at 8:19

David Browne - Microsoft · Accepted Answer · 2021-03-27 20:11:41Z

Embed the Partition number or the TenantID key in the query text

Parameters are for when you want shared, reused query plans. Hard-coding the criteria that cause query plans to vary is the basic right answer here.

And even though "As much as possible, we are refraining from using Dynamic SQL in the code", you should make an exception here.

Use OPTION RECOMPILE

If you don't end up spending too much time in query optimization, this is almost as good. Or

Add a comment into the query that varies by tenant or tenant size to get a partitioned plan cache. This is also useful for correlating queries to the code paths that generate them. eg

/* Dashboard: Sales Overview
   Visual: Total Sales
   TenantID: 12345    */   
select sum(Sales) TotalSales
from T
where TenantId = @tenantId

Thanks David. Will put forth the suggestions given across the team. — PraveenDS, Commented Mar 31, 2021 at 7:53

Collectives™ on Stack Overflow

Can Stats per partition prevent parameter sniffing problem when data varies by wide margin in partitions?

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related