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.