Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 26 '12 at 16:53

This question came from our site for professional and enthusiast programmers.

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.

share|improve this answer
    
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. –  F.B. ten Kate Jan 26 '12 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?? –  F.B. ten Kate Jan 27 '12 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 '12 at 15:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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