Wish I could come over and play with this. The check constraint should allow the optimizer to eliminate servers from the choice. The fact that security might have changed the behavior makes me wonder about the ANSI settings. That, might, maybe, could influence the check constraint (lots of weasel words).

And parameter sniffing (a good thing normally) shouldn't matter that much, although, if it does, using a hard value like the string you passed is exactly the same thing as parameter sniffing (using an actual value against statistics to determine cardinality). And using recompile with a local variable is something of a waste of time since you'll always get average plans. Either recompile (so you get every value sniffed individually), or a local variable (so you get averages instead of specific values), each independently.

I'm not sure what's going on. Can you post the execution plans?