Robert klimes (8/7/2014)

---

Roger Sabin (8/7/2014)

---

I tried what you suggest and it does force the "bad" plan when I use 40 and a "good" plan when I use 17. Thanks.

So now you verified what is happening the "fix" is up to you.

As Kevin suggested, you can use optimize for (which I have used on a few occasions) which will always create a plan for a specific parameter or if you use unknown then it will build a plan based on all stats for all values. in either case you will not always have an optimal plan.

Another option is to add with recompile to the proc which will generate the best plan for each parameter at the expense of having to recompile each time it runs. Depending on your workload and resources this may be acceptable or not.

yet another option would be to refactor the proc so it always generates the same plan.