1

We are having performance issues due to parameter sniffing in SQL Server. Our application uses NHibernate with the LINQ provider.

I am searching for a way to add for example "OPTION(RECOMPILE)" to that certain SQL query. I now I could use interceptors for that, but I want it to be added only to certain IQueryable queries. So not on all my nhibernate queries, because then I would lose the advantages of query plan caching.

Other options to solve the parameter sniffing on 1 specific query with NHibernate are also welcome.

Dommicentl
  • 721
  • 1
  • 5
  • 12
  • http://www.codewrecks.com/blog/index.php/2011/07/23/use-sql-server-query-hints-with-nhibernate-hql-and-icriteria/ – Lukasz Szozda Sep 17 '15 at 06:18
  • 1
    We already stumbled upon this link, but that uses an implementation depending on the IQuery interface, while we use IQueryables. I do not now a way to add a comment to a SQL query through IQueryables. – Dommicentl Sep 17 '15 at 06:28

1 Answers1

0

Use a query plan guide for it to specify an optimize for.

Here's a basic walkthrough: http://realsqlguy.com/dont-get-slimed-bad-parameter-sniffing/

Although this fixes it on the server side, so you'll need to find the query that is causing the issue, find the correct plan, and generate a plan guide for it. Maybe the next version of SQL Server will resolve some of the parameter sniffing issues.

Robert McKee
  • 21,305
  • 1
  • 43
  • 57