Slow performance on ASP.NET MVC application using EntityFramework + SQL Server

Used technologies and software:

  •  ASP.NET MVC 5
  • Entity Framework Code First 6.x
  • SQL Server 2014 Express
  • Windows Server 2012
  • IIS 8.0
  • Hyper-V Virtual Machine (SSD and 8GB RAM set dynamic with 4GB as start up value).
  • Visual Studio 2013

Problem:

Website in production is slow, takes several seconds (+30s) or even minutes to load a page. But this does not always happen.
In development everything works always fast.

Steps taken to figure out the problem:

  • Netword config, NIC, IP, DNS, … –> ok
  • Checking if connection string is set correctly. –> ok
    (tip: see https://teusje.wordpress.com/2012/02/21/how-to-test-an-sql-server-connection/ )
  • Adding caching to the ASP.NET MVC application. –> ok, but still too slow
  • SQL Server 2014 Profiler:
    Trying to figure out if it is the application or the SQL Server, so we ran the Profiler and copy several queries and executed them directly on the SQL Server without any problem.
  • Log files:
    Custom log file in the ASP.NET MVC application gave us a good hint:

    Exceptions:

     sqlexception: Timeout expired EntityCommandExecutionException
    
     SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
     Stack:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
        at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
        at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
        at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
        at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
        at System.Data.SqlClient.SqlDataReader.get_MetaData()
        at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
        at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
        at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
        at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
        at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
        at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
        at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
        at Glimpse.Ado.AlternateType.GlimpseDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
        at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
        at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.b__c(DbCommand t, DbCommandInterceptionContext`1 c)<
        at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
        at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
        at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
        at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
        at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Start Microsoft SQL Server Management Studio and navigate to:
[Server Instance] –> [Management] –> [SQL Server Logs] –> double click [Current – …] there you select [Windows NT]

There we’ve seen the real problem several times in the logs:

"AppDomain 50 (master.sys[runtime].48) is marked for unload due to memory pressure."

sql server problem

Now we simply started Task Manager on the server and watched the ‘Memory’ in the ‘Performance’ tab.
The memory was constantly at 3.7GB or 3.8GB when we visited the website.

vm memory
Hooray! We found the problem. :-) Now we need to find a solution.

As you might remember from the beginning of this post, we’ve configured our Hyper-V Virtual Machine with the Dynamic Memory option.
This had a start up value of 4GB… and our server was constantly at 3.7GB or 3.8GB…

So why does our server not dynamically increase the RAM of the virtual machine?

It turns out that Hyper-V Dynamic Memory, introduced in Windows Server 2008 R2 SP1, is only supported in SQL Server Enterprise and Datacenter Editions!

Solution:

Don’t enable Hyper-V Dynamic Memory for your virtual machine but set it static with a value of e.g. 8GB RAM.

Voila:

vm memory fixed

:-)

Your Host Operating System needs to be at least Windows Server 2008 R2 SP1 or Microsoft Hyper-V Server 2008 R2 SP1 as your Hyper-V host.
The Guest Operating System edition (= your virtual machine) needs to be Web, Standard, Enterprise or Datacenter.
You need SQL Server Enterprise or Datacenter edition if you want that SQL Server automatically uses more RAM when needed using the Hyper-V Dynamic Memory feature.
In case you just have a Windows Server Standard and SQL Server Express and you have the same symptoms as described above just set your virtual machine RAM to a static value instead of using Hyper-V Dynamic Memory.

Extra information:

How to test an SQL Server connection

  1. Create a new empty text document (.txt) on your pc. (e.g.: on your desktop)
  2. Rename it to X.UDL
  3. Double click the file to open the Data Link Properties
  4. Select the correct provider on the [Provider] tabpage
    Fill in your connection details on the [Connection] tabpage
  5. Click [OK] to close the Data Link Properties window when you have a succesful connection (via [Test Connection])
  6. Now open the X.UDL with your favorite text editor (e.g.: notepad, notepad++)
    You should see your connection string now!

;-)