What is parameter-sensitive query behavior?

A parameter-sensitive query is one where different parameter values can require substantially different execution strategies.

For example, one CustomerId may match five rows while another matches several million. A plan optimized for one distribution can perform poorly for the other.

Traditional approaches include query redesign, OPTION (RECOMPILE), OPTIMIZE FOR, carefully designed dynamic SQL, or separating workload patterns. SQL Server 2022 introduced Parameter Sensitive Plan optimization for eligible queries, allowing multiple plan variants for different parameter ranges.