locked
Normally fast EF LINQ query suddenly starts timing out - works again only after DB server is rebooted RRS feed

  • Question

  • Here is an odd one.  We have a website application using EntityFramework to connect to an MS SQL Server database (SQL2010).  We recently ran into a case where a LINQ query that was normally running very fast (< 2 seconds) suddenly started running so long that the connection timed-out.  There were no significant changes to the database size or the amount of data being returned from the query.  Moreover, when we ran a SQL version of the query in SQL Mgmt Studio, it ran normally (quick).

    The issue was not happening in our dev or staging DBs, but when I connected to the Prod DB in Visual Studio and ran the code in debug mode, I would run into the timeout on the same query.  As a test, I made a small change to the WHERE statement that should have no impact on the query (ie: added a line at the end: '&& table.fieldname != "FOOBAR"') and then the query ran normally (fast).  When I removed the extra useless line from the WHERE statement and ran again, it was back to timing out (tried this back and forth several times).

    The DBA tried doing a DROPCLEANBUFFERS on the DB and that had no effect.  In desparation, we then rebooted the production database server and this fixed the problem (query ran fast again).

    I understand that EF and LINQ cache queries on the DB server.  Based on the above, it sounds to me like the cached version of this query got corrupted somehow.  I would have thought the DROPCLEANBUFFERS would have fixed it, but it appears that there is something else holding the query info that gets cleaned/reset by a reboot, but not by DROPCLEANBUFFERS.

    Has anybody else ran into this issue?  Is there a way to prevent it?  Is there an easier way to flush the cached EF/LINQ queries other than rebooting?

    As a side note, we had a similar problem with this same database in an earlier non-EF/non-LINQ version of the application that pulled data with regular Stored Procedures.  Every so often (2-3 times a year), one of the SPs that normally ran in a few seconds would start timingout, but only when ran from the application.  The same SP executed directly from SQL Mgmt Studio would still run normally.  The only way to fix it was to completely drop and recreate the SP.  The DBA concluded that the database was creating two execution plans for the same SP and this was the root of the issue (although we never learned exactly why it happened or how to prevent it).

    Any help or suggestions would be appreciated!

    Sascoaz

    Tuesday, October 8, 2013 11:38 PM

Answers

  • Maybe you get into some kind of blocking action where the table being quired is in a lock state by the database, and the T-SQL query that EF has submitted doesn't have a NOLOCK, condition which can be set for the query by using System.Transaction ReadUncomitted.   Yes the query would need to be in a transaction.

    http://www.c-sharpcorner.com/UploadFile/ff2f08/prevent-dead-lock-in-entity-framework/

    A good practice in doing queries in a multi-user application hitting the database using EF or Sprocs with T-SQL is to use NoLock to reduce query execution time.

    • Marked as answer by Fred Bao Wednesday, October 16, 2013 9:25 AM
    Wednesday, October 9, 2013 7:16 AM

All replies

  • Maybe you get into some kind of blocking action where the table being quired is in a lock state by the database, and the T-SQL query that EF has submitted doesn't have a NOLOCK, condition which can be set for the query by using System.Transaction ReadUncomitted.   Yes the query would need to be in a transaction.

    http://www.c-sharpcorner.com/UploadFile/ff2f08/prevent-dead-lock-in-entity-framework/

    A good practice in doing queries in a multi-user application hitting the database using EF or Sprocs with T-SQL is to use NoLock to reduce query execution time.

    • Marked as answer by Fred Bao Wednesday, October 16, 2013 9:25 AM
    Wednesday, October 9, 2013 7:16 AM
  • Hi friend, i have the same problem, it is exactly as you described.

    has you found a solution ?

    i will wait for your comments.

    Regards.

    Thursday, February 4, 2016 4:25 AM
  • I have had similar issues that were resolved by index and statistics maintenance on the database. It is also possible that the cached query execution plan as determined by the particular set of parameters used in the first execution after the database is rebooted, or the query plan falls out of the plan cache, is not a good plan for some other set of parameters. If the query execution plan should depend on the parameter values then adding With Recompile to the statement might help. It increases the query compilation time for each execution, but if the resulting plan is more optimized the overall time may decrease and performance may be more consistent.

    Paul

    Thursday, February 4, 2016 12:13 PM