Thx David, Excellent article. I gave a five star rating for it.

We are using nHybernate here. We have noticed blotting of the plan cache. There fore the first thing I did was to set Optimization for Ad-Hoc queries. Then clear the cache. I have not seen much blotting after that.

I also made sure that the developers used the right data types when passing the parameter. I am also thinking of passing in the in parameters as table valued parameters.