How to handle more multiple filters(more than 15) in big query

Amit Rohatgi 2014-10-01 06:11:32

Aaron Bertrand 2014-10-01 13:51:01
Can't tell because the biggest query is so long it's actually truncated in the plan XML. Sounds like what I call "the kitchen sink" – so many parameters are optional and it's impossible for SQL Server to generate a single plan that optimizes well for any combination of parameters.

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: