How to handle more multiple filters(more than 15) in big query
First try is to add OPTION (RECOMPILE) to the query – you pay a compilation hit for every query, but that combination of parameters gets its own plan.
I have a pretty low threshold for switching this to parameterized dynamic SQL (and ensuring that "optimize for ad hoc workloads" is enabled). This way you build a query that is tailored for that combination of parameters, and you can enjoy reuse without filling the plan cache with single-use plans.
I talk about this in the following video: