Erik Darling has a bone to pick with TOP PERCENT:
There was a three-part series of posts where I talked about a weird performance issue you can hit with parameterized top. While doing some query tuning for a client recently, I ran across a funny scenario where they were using TOP PERCENT to control the number of rows coming back from queries.
With a parameter.
So uh. Let’s talk about that.
Read on to see the mess of an execution plan TOP PERCENT creates and a more complex query which performs considerably better under the circumstances.