6

We are having some strange performance issues and I was hoping somebody may be able to point us in the right direction. Our scenario is an ASP.NET MVC C# website using EF4 POCO in IIS 7 (highly specced servers, dedicated just for this application).

Obviously it's slow on application_startup which is to be expected, but once that has loaded you can navigate the site and everything is nice and snappy 0.5ms page loads (we are using Mini-Profiler). Now if you stop using the site for say 5 - 10 minutes (we have the app pool recycle set to 2 hours and we are logging so we know that it hasn't been recycled) then the first page load is ridiculously slow, 10 - 15 seconds, but then you can navigate around again without issue (0.5ms).

This is not SQL queries that are slow as all queries seem to work fine after the first page hit even if they haven't been run yet so not caching anywhere either.

We have done a huge amount of testing and I can't figure this out. The main thing I have tried so far is to Pre generate EF views but this has not helped.

It seems after looking at Sql Server Profiler after 5 minutes give or take 30 seconds with no activity in Sql Server Profiler and no site interaction a couple of "Audit Logout" entries appear for the application and as soon as that happens it then seems to take 10 - 15 seconds to refresh the application. Is there an idle timeout on Sql Server?

user351711
  • 3,171
  • 5
  • 39
  • 74

8 Answers8

5

This should work if you use the below in your connection string:

server=MyServer;database=MyDatabase;Min Pool Size=1;Max Pool Size=100

It will force your connection pool to always maintain at least one connection. I must say I don't recommend this (persistant connection) but it will solve your problem.

EkoostikMartin
  • 6,831
  • 2
  • 33
  • 62
2

Are you using LMHOSTS file? We had same issue. LMHOSTS file cache expires after 10 minutes by default. After system has been sitting idle for 10 minutes the host would use Broadcast message before reloading the LMHOSTS file causing the delay.

Bob Lotz
  • 85
  • 1
  • 10
1

It seems after looking at Sql Server Profiler after 5 minutes give or take 30 seconds with no activity in Sql Server Profiler and no site interaction a couple of "Audit Logout" entries appear for the application and as soon as that happens it then seems to take 10 - 15 seconds to refresh the application. Is there an idle timeout on Sql Server?

This is telling me that the issue most likely lies with your SQL server and/or the connection to it rather than with your application. SQL server uses connection pooling and SQL will scavange these pools every so often and clean them up. The delay you appear to be experiencing is when your connection pools have been cleaned up (the audit logouts) and you are having to establish a new connection. I would talk/work with your SQL database people.

For testing, do you have access to a local/dev copy of the database not running on the same SQL server as your production app? If not, try and get one setup and see if you suffer from the same issues.

Tommy
  • 39,592
  • 10
  • 90
  • 121
  • It does appear to be something to do with the Sql Connection being re-established but I have never known it to take this long and doesn't on our other systems but we cannot figure out any timeout settings in Sql that could be causing this. – user351711 Jun 19 '12 at 18:39
0

I would run "SQL Server Profiler" against SQL Server and capture a new trace while reproducing the problem by accessing the site after being idle 5-10 mins. After that look at the trace. Specifically, look for entries in which ApplicationName starts with "EntityFramework...". This will tell you what is EF doing at that moment. There could be some issue with custom caching on top of EF, or some session state that is expiring (check sessionState timeout in web.config)

j0aqu1n
  • 1,013
  • 7
  • 14
  • I have profiled the site in SQL and there is nothing wrong that I can see, nothing taking more than 100ms and most sitting around 0 - 10 so this it's not a slow query as far as I can tell. – user351711 May 22 '12 at 13:59
  • Also all the ApplicationName entries are .Net SqlClient Data Provider. – user351711 May 22 '12 at 14:00
  • OK so after 5 minutes give or take a 30 seconds with no activity in Sql Server Profiler and no site interaction a couple of Audit Logout entries appear for the application and as soon as that happens it then seems to take 10 - 15 seconds to refresh the application. Is there an idle timeout on Sql Server? – user351711 May 22 '12 at 14:16
0

Since it is the first run (per app?) that is slow, you may be experiencing the compilation of the EDMX or the LINQ into SQL.

Possible solutions:

  1. Use precompiled views and precompiled queries (may require a lot of refactoring).
    http://msdn.microsoft.com/en-us/library/bb896240.aspx
    http://blogs.msdn.com/b/dmcat/archive/2010/04/21/isolating-performance-with-precompiled-pre-generated-views-in-the-entity-framework-4.aspx
    http://msdn.microsoft.com/en-us/library/bb896297.aspx
    http://msdn.microsoft.com/en-us/magazine/ee336024.aspx

  2. Dry run all your queries on app start (prior to first request is received).
    You can run queries with a fake input (e.g. non existing zero keys) on a default connection string (can be to an empty database). Just make sure you don't throw exceptions (use SingleOrDefault() instead of Single() and handle null results and 0-length list results on .ToList()).

Danny Varod
  • 17,324
  • 5
  • 69
  • 111
0

Create a simple webpage that accesses the SQL Server with a trivial query like "Select getDate()" or some other cheap query. Then use an external service like Pingdom or other monitor to hit that page every 30 seconds or so. That should keep the connections warm.

Turnkey
  • 9,266
  • 3
  • 27
  • 36
0

Try to overwrite your timeout in the web.config like in this example:

Data Source=mydatabase;Initial Catalog=Match;Persist Security Info=True
;User ID=User;Password=password;Connection Timeout=120

If it works, this is not a solution..just a work around.

theforce
  • 1,505
  • 1
  • 11
  • 13
  • It isn't a timeout issue on a long running query, once the connection is established the query takes 1ms but it is taking 5 seconds to establish a connection after 5 minutes of no calls. – user351711 Jun 20 '12 at 08:54
0

In our case the application was hosted in Azure App Service Plan and was having similar problem. Turned out to be a problem of not configuring virtual network. See the question/answer here - EF Core 3.1.14 Recurring Cold Start

Varun Sharma
  • 2,591
  • 8
  • 45
  • 63