CODEWITHSUNDEEP
sql-serverasp.net-mvcperformanceiis-7entity-framework-4
user351711
user351711

Reputation: 3301

Establishing SQL Connection Taking 10 - 15 Seconds

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?

Upvotes: 6

Views: 6392

Answers (8)

Varun Sharma
Varun Sharma

Reputation: 2761

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

Upvotes: 0

Bob Lotz
Bob Lotz

Reputation: 85

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.

Upvotes: 2

EkoostikMartin
EkoostikMartin

Reputation: 6921

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.

Upvotes: 5

theforce
theforce

Reputation: 1525

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.

Upvotes: 0

Turnkey
Turnkey

Reputation: 9416

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.

Upvotes: 0

Tommy
Tommy

Reputation: 39807

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.

Upvotes: 1

Danny Varod
Danny Varod

Reputation: 18118

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()).

Upvotes: 0

j0aqu1n
j0aqu1n

Reputation: 1021

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)

Upvotes: 0

Related Questions