Search for existing suggestions

Ability to specify query and table hints in SQL Server

Table lock, update lock ( WITH(UPDLOCK) / FOR UPDATE ).
LockMode something like NHibernate.

Also should support controlling parameter sniffing

192 votes
Sign in
Sign in with: Facebook Google
Signed in as (Sign out)

We’ll send you updates on this idea

Usa*Usa shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →


Sign in
Sign in with: Facebook Google
Signed in as (Sign out)
  • Alvaro commented  ·   ·  Flag as inappropriate

    I need this option to to a pesismistic concurrency control, and it would be very useful if I can use the UPDLOCK in the related entities that I need to load in the include method.

  • AdminDiego Vega (Program Manager, Microsoft Entity Framework) commented  ·   ·  Flag as inappropriate

    @Marius Vorster: Thanks for the feedback. This is a feature we would like to add in the future. In EF7 we are laying off some of the necessary infrastructure in the form of query annotations, and we would accept a contribution in this area.

    In the meanwhile for EF6, at least for very simple cases it is possible to append OPTIONS() at the end of queries using a query interceptor, e.g.:

    class SimpleOptimizeForUnknownInterceptor : DbCommandInterceptor
    public override void ReaderExecuting(
    DbCommand command,
    DbCommandInterceptionContext<DbDataReader> interceptionContext)
    command.CommandText = command.CommandText +
    base.ReaderExecuting(command, interceptionContext);

    Usage is:

    DbInterception.Add(new SimpleOptimizeForUnknownInterceptor());

    Of course you will want to add some logic to control when this transformation applies, e.g. add a bool flag to your custom DbContext and then check for the state of that flag inside the interceptor.

  • Marius Vorster commented  ·   ·  Flag as inappropriate

    So we can do some query hints from TransactionScope but there is no way to provide 'Optimize for' hints (Parameter sniffing configuration). I have a case in production now where I expect the big standard deviation (Random slow queries) is due to bad parameter sniffing and my hands are tied. Can we please give it priority to include these configurations in Entity Framework in the future.

  • BG commented  ·   ·  Flag as inappropriate

    There are many scenarios where parameter sniffing in SQL server creates serious performance problems. This is very problematic with many EF generated queries, since there is no control that allows you to tell the database optimizer not to use the parameter values or cache a plan. A simple mechanism to hint OPTION (RECOMPILE) on the generated queries would solve most of these issues.

    A more sophisticated solution that would allow the generated sql to leverage (Optimize for Unknown) would also be useful.

    Parameter sniffing and the related performance problems are a very prevalent issue with SQL server and if you have ever run a real production app you will encounter it time and time again. I'm shocked there is still no workaround in EF (besides creating plan guides in the DB which are very fragile and impossible to manage in a dynamic filter scenario)

    If I'm missing another workaround please advise.

  • Eric commented  ·   ·  Flag as inappropriate

    I have a dynamic query that changes the number of joins based upon the number of keywords passed in and the query never completes via EF. Without even specifying an option the same SQL running in SSMS executes in 3 seconds. We must have the ability to pass query hints or this ORM like many others will be useful only in silo projects with small databases. We need a solution for real world problems.

  • Eric Piazza commented  ·   ·  Flag as inappropriate

    Need hints to control parameter sniffing, we have a query that takes 0 seconds with proper index usage, but the EF query uses a bad execution plan and it takes 20 seconds!

  • Xiaodong Huang commented  ·   ·  Flag as inappropriate

    Without table hints support, it is almost impossible to write a high concurrrent service without resorting to stored procedure which is what we want to avoid by using EF.

  • Cosmin Onea commented  ·   ·  Flag as inappropriate

    I think this is so important to have. I have a long running workflow and when a new message comes to it I need to lock it for a bit. I can query and use change tracker to set the entity as modified and save changes but that requires me to work around the problem instead of having that out of the box.

  • Shlomi commented  ·   ·  Flag as inappropriate

    until then - you can use TransactionScope with ReadUncommitted isolation level.

Feedback and Knowledge Base