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:

SQL Fiddle: a tool for database developers


(via)

SQL Fiddle is a tool for database developers to test out their SQL queries.  If you do not know SQL or basic database concepts, this site is not going to be very useful to you.

[ link ]

See also: JsFiddle, a playground for web developers

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!

;-)

EF Code First change the default generated database name

I’m playing around with ASP.NET MVC 3 + EF Code First. At a testing stage my models change often. To always have some clean content when building and running the project I have overridden the Seed method in my custom database initializer class (inherit from DropCreateDatabaseIfModelChanges<T>).

using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using Test.Models;

namespace Test.DAL
{
public class TestInitializer : DropCreateDatabaseIfModelChanges<TestContext>
{
protected override void Seed(TestContext context)
{
var categories = new List<ProductCat>
{
new ProductCat { Name = "Wood" },
new ProductCat { Name = "Metal" },
new ProductCat { Name = "Plastic" }
};

var products = new List<Product>
{
new Product { Name = "Bench", ProductCat = categories.Single(c => c.Name == "Wood")},
new Product { Name = "Bottle", ProductCat = categories.Single(c => c.Name == "Plastic")},
new Product { Name = "Electric cable", ProductCat = categories.Single(c => c.Name == "Metal")}
};

products.ForEach(p => context.Products.Add(p));
context.SaveChanges();

base.Seed(context);
}
}
}

Now if you have SQL Server Management Studio (SSMS) open, you will see that your generated database is something like Project.Namespace.Class, which is actually not a good name. To change that EF Code First generated name you can do something like this in your DbContext class:

using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;

namespace Test.Models
{
public class TestContext : DbContext
{
public TestContext()
: base("MyNewDBName") // <-- database name
{ }

public DbSet<Product> Products { get; set; }
public DbSet<ProductCat> Categories { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}
}
}

Keep in mind that this is only one of the places where you can change the default database name.

Now your database will be called MyNewDBName in SSMS.
Have fun ;-)

ASP.NET: Adding Membership to an mdf file

In case you need to add Membership to an *.mdf file you need to use the tool: aspnet_regsql.exe.

This tool can be found in:

C:\WINDOWS\Microsoft.NET\Framework\<versionNumber>\

In my case it was:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\

Now getting the tool working with an *.mdf file (MyData.mdf) should be done like this:

C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -C "Data Source=
.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\Users\Teusje\do
cuments\visual studio 2010\Projects\MySite1\MySite1\App_Data\MyData.mdf" -A all

Start adding the following features:
Membership
Profile
RoleManager
Personalization
SqlWebEventProvider

................

Finished.

C:\Windows\Microsoft.NET\Framework\v4.0.30319>

More information about can be found here and here (msdn).

Enjoy ;)

WebMatrix (beta)

(pictures from ScottGu‘s blog)

WebMatrix is everything you need to build Web sites using Windows. It includes IIS Developer Express (a development Web server), ASP.NET (a Web framework), and SQL Server Compact (an embedded database). It streamlines Web site development and makes it easy to start Web sites from popular open-source apps. The skills and code you develop with WebMatrix transition seamlessly to Visual Studio and SQL Server.

[ source ] [ source2 ]