NHibernate automatically uses sp_executesql with different paramters to execute it's data. Now what we're noticing in production is that we're beeing hit by parameter sniffing sometimes. In such a way that query performance isn't just effected it's actually causing timeouts.

I'm not a DBA so i'm somewhat winging it but to me it seems like a query is beeing optimised for a small amount of data (10 rows) and then has to query a big amount (150000 rows) which is grinding it to a halt.

Now i've foundout about the 4136 Trace which disables parameter sniffing entirely for an instance of SQL Server.

Now since we're also in the process of upgrading some of our servers i'm wondering if NHibernate behaves different on SQL Server 2008 then it does in 2005. Does it for example add the OPTIMISE FOR UNKOWN bits and pieces.

As far as i know some "tools" have been added into SQL 2008 to allow for better ways of handling these parameter sniffing issues. I'm very curious if NHibernate has taken these tools and uses them or if we change to SQL Server 2008 we'd still have to use this trace or something simular to handle these issues.

1 Answer 1


I don't know which version of NH you're using, but I'm using 3.1 with SQL 2008 and it does not have any special features to handle Parameter Sniffing. I know this because I've suffered quite a bit trying to overcome this issue.

I don't know if you use HQL/Criteria/QueryOver/LINQ, but I found two ways to (kind of) solve this:

  • Appending "option(recompile)" on the query -> with an hook on the query execution if you're using the likes of QueryOver/LINQ.

Although this works, it no longer fetches existing query plans from the cache, and every query has to build it ground-up.

  • Conditionally, add a dummy predicate to the query

Or something that would force a different query plan to be fetched. For example, if the date interval is less than a month add a .Where(x => true). It would then have a different plan for large intervals and a plan for small intervals.

  • Well, currently we're having problems with a part that runs NHibernate 3.2 with Criteria's building the queries. I've read how you overcame your issues in some other question but both just sound like something i really shouldnt have to bother with. The first beeing a very permanent solution, the later requiring some custom code to handle "Spefici" situations. Jan 26, 2012 at 15:38
  • Went with the an Interceptor since we're using DetachedCriteria's here. Basically solution #1 which you suggested. Biggest issue now is that i'd only like to use this in specific situations. One solution i found was using SQL Comments but there is bug in NHibernate which makes that not work. Also when using LINQ to Nhibernate (which we use in newer projects) how would i go about setting this? Somehow ont he IQueriable?? Jan 27, 2012 at 13:02
  • That was exactly my problem, and the reason that I went for the "hacky" second solution :(. I also tried that SQL Comments way, but couldn't get it to work with QueryOver/LINQ.
    – psousa
    Jan 27, 2012 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.