Plan reuse under Simple parameterization

Interesting problem came up today on #sqlhelp. A trivial plan results in multiple prepared Execution plans. To begin with let’s understand parameterization quickly. It is a mechanism that the optimizer uses to create an execution plan for Ad hoc queries (not store procedures) that enables plan reuse. In other words when a query is changed…