Establishing SQL Connection Taking 10 - 15 Seconds

Question

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?

Answer 1

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.

Answer 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.

Answer 3

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.