Solving slow parameterized query plans with SQL Server

This morning I was listening to Stack Overflow podcast#45 in which Jeff Atwood indicated he had uncovered situations involving poor performing parametrized query's. The solution involves optimizing for 'UNKNOWN' as an optional hint when dealing with parametrized queries.

Example:

@p1=1, @p2=9998,

Select * from t where col > @p1 or col2 > @p2 order by col1

option (OPTIMIZE FOR (@p1 UNKNOWN, @p2 UNKNOWN))

This optional optimization is available for SQL 2008 only. The option forces the query optimizer to look at all available statistical data to come up with a more intelligent deterministic view of what values the local variables used to generate the query plan should equate to rather than using the parameters being passed in by the application. The workaround alternatives do not really offer any good alternatives to solve this issue most notably when dealing with dynamic parameters.

Workarounds

1. Recompile every time the query is executed using the RECOMPILE hint - This can be very CPU intensive and effectively eliminates the benefits of caching query plans. ex. option(RECOMPILE)

2. Un-parametrize the query – Not a viable option in most cases due to SQL injection risk.

3. Hint with specific parameters using the OPTIMIZE FOR hint (However, what value(s) should the app developer use?) This is a great option if the values in the rows are static, that is; not growing in number, etc. – However in my case the rows were not static.

4. Forcing the use of a specific index

5. Use a plan guide – Using any of the recommendations above.

Implications with NHibernate or other Object Relational Mappers's

I am a user of NHibernate. At present NHibernate does not provide support for the SQL 2008 dialect and recommends using the SQL 2005 dialect configuration option to deal with SQL 2008 data sources. I am wondering if anyone in the NHibernate community has come across this issue with slow parametrized SQL? Is this an issue that an ORM needs to be aware of when supporting a given database dialect? My view is yes. However I am still somewhat of a newb with NHibernate.