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.
Best Answer
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:
Although this works, it no longer fetches existing query plans from the cache, and every query has to build it ground-up.
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.