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:

UnicornManager: Prism 4, MEF, WPF, EF 4.1 code first, NuGet, MVVM, DataValidation, …

This blog post is an attempt to learn and discover Prism 4 together with MEF, EF 4.1 code first and by trying to use concepts like a repository, data validation and Unit of Work.

The reason why I post this here is to (1) learn from your comments and (2) create a basic guided tutorial for beginners.

I hope you don’t mind that I’ve chosen unicorns for this example ;)

Go!

Let’s start Visual Studio 2010 Service Pack 1 and create a new WPF Application called UnicornManager:

The solution will contain 3 projects at the end:

  • UnicornManager
  • UnicornManager.Core
  • UnicornManager.Data

UnicornManager.Core will be our core module, hence the name.
The first thing that we are going to do is preparing our UnicornManager project so that it can import other modules via MEF.

First we are going to add some references.
Right-click UnicornManager and select [Add Reference…] and search for System.ComponentModel.Composition in the [.NET] tabpage.

Next we need to install Prism with MEF. Make sure the UnicornManager project is selected and in Visual Studio click [Tools] > [Library Package Manager] > [Manage NuGet Packages…]
Click [Online] on the left and in the [Search Online] textbox type in: Prism.
Select Prism.MEFExtensions and click [Install].
(This will automatically also install Prism, because it depends on it)

Now rename MainWindow.xaml to Shell.xaml by right-clicking it and [Rename].
Open the Shell codebehind file (Shell.xaml.cs) and also rename MainWindow to Shell.

Above the partial Shell class add the [Export] attribute.

The codebehind for Shell should look like this now:

namespace UnicornManager
{
/// <summary>
/// Interaction logic for Shell.xaml
/// </summary>
[Export]
public partial class Shell : Window
{
public Shell()
{
InitializeComponent();
}
}
}

Now in the Shell xaml code we will create a very basic layout. We have a region to show our content and on the left a region for the sidebar. I have called these two regions MainRegion and SidebarRegion where our Views (UserControls) will be made visible. For the regions I’m using two ContentControls, but don’t forget that an ItemsControl also exists to host multiple UserControls in it.

This is the XAML:

<Window x:Class="UnicornManager.Shell"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:Regions="http://www.codeplex.com/prism">http://www.codeplex.com/prism"
Title="Shell" Height="600" Width="800">
<Grid>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="200" />
<ColumnDefinition Width="*" />
</Grid.ColumnDefinitions>

<ContentControl x:Name="MainRegion"
Regions:RegionManager.RegionName="MainRegion"
Grid.Column="1" />

<ContentControl x:Name="SidebarRegion"
Regions:RegionManager.RegionName="SidebarRegion"
Grid.Column="0" />
</Grid>
</Window>

Add a new class to the UnicornManager project and name it CoreBootstrapper.cs.
This class will be the starting point of our application.

Make the class public and let it inherit from MefBootstrapper.
The other methods are being used to tell our application which is our main Window to start up and where our other Views and objects  are located.

The bootstrapper code looks like this:

namespace UnicornManager
{
public class CoreBootstrapper : MefBootstrapper
{
protected override System.Windows.DependencyObject CreateShell()
{
return this.Container.GetExportedValue<Shell>();
}

protected override void InitializeShell()
{
base.InitializeShell();
Application.Current.MainWindow = (Window)this.Shell;
Application.Current.MainWindow.Show();
}

protected override void ConfigureAggregateCatalog()
{
base.ConfigureAggregateCatalog();
this.AggregateCatalog.Catalogs.Add(new AssemblyCatalog(typeof(CoreBootstrapper).Assembly));
}
}
}

Now if you try to run the project you will receive an IOException that the resource ‘mainwindow.xaml’ cannot be located. Don’t worry, we just forgot to remove the startup path from the App.xaml code:

<em> </em>StartupUri="MainWindow.xaml"

Now in the App codebehind tell the application that it should use our bootstrapper instead:

public partial class App : Application
{
protected override void OnStartup(StartupEventArgs e)
{
base.OnStartup(e);

CoreBootstrapper bootstrapper = new CoreBootstrapper();
bootstrapper.Run();
}
}

If everything went fine, you should be able to run the application. :D

Now add a new Class Library project called UnicornManager.Data to the solution. This project will contain our models by using the Entity Framework 4.1 code first.

Delete Class1.cs from the project and create two folders: DAL and Models.
(DAL = Data Access Layer)

Make sure you have the Data project selected and install EntityFramework via NuGet:

Add also the System.ComponentModel.Composition reference to the project.

Lets create our simple model by adding a new Unicorn class to the Models folder. Here is the code:

namespace UnicornManager.Data.Models
{
public class Unicorn
{
public int UnicornId { get; set; }

public string LastName { get; set; }
public string FirstName { get; set; }
}
}

In the DAL folder we are going to add some repositories, UnitOfWork and code necessary for EF 4.1 code first. I’m not posting the code here, you can download this and check the DAL folder in the UnicornManager.Data project.

Because this is just a basic demo application with one class, we would not need a UnitOfWork because it is just an extra wrapper around the UnicornRepository. However if you have more models you will have some extra Repositories, which can be placed in the UnitOfWork.

Our last step is to create our Core module. Add a new Class Library project to the solution called UnicornManager.Core. This will contain our ViewModels and Views.

Delete Class1.cs and add a new class called CoreModule.cs.
Remember we also need to add Prism here by getting the Prism.MEFExtensions from NuGet and a reference to System.ComponentModel.Composition and System.Xaml.

The CoreModule is the entry point for our Bootstrapper. Here we Export our module and register our Views with the regions of our Shell.
This is the code:

[Module(ModuleName = "CoreModule")]
[ModuleExport(typeof(CoreModule))]
public class CoreModule : IModule
{
private IRegionManager _RegionManager;

[ImportingConstructor]
public CoreModule(IRegionManager regionManager)
{
this._RegionManager = regionManager;
}

#region IModule Members

public void Initialize()
{
_RegionManager.RegisterViewWithRegion("MainRegion", typeof(Views.MainView));
_RegionManager.RegisterViewWithRegion("SidebarRegion", typeof(Views.SidebarView));
}

#endregion
}

In the Initialize method, we register two views, but we didn’t create them yet, time to do this first.

Add two folders in the Core project: ViewModels and Views.
In the ViewModels folder add two classed called: MainViewModel.cs and SidebarViewModel.cs.

Code for MainViewModel:

namespace UnicornManager.Core.ViewModels
{
[Export]
[PartCreationPolicy(CreationPolicy.NonShared)]
public class MainViewModel : NotificationObject
{
private IUnitOfWork _UnitOfWork;

[ImportingConstructor]
public MainViewModel(IUnitOfWork unitOfWork)
{
this._UnitOfWork = unitOfWork;
Init();
}

public ObservableCollection<Unicorn> Unicorns { get; set; }

private void Init()
{
this.Unicorns = _UnitOfWork.UnicornRepository.GetSyncedEntityItems();
}
}
}

Don’t forget to add a reference from the Core to the Data project. Here we see that we get our UnitOfWork object via MEF by using the [ImportingConstructor] attribute.

We also inherit from NotificationObject, this way we don’t have to implement the INotifyPropertyChanged ourselves. Thank you Prism for adding this already to the ViewModels base class.

Code for the SidebarViewModel:

namespace UnicornManager.Core.ViewModels
{
[Export]
[PartCreationPolicy(CreationPolicy.NonShared)]
public class SidebarViewModel : NotificationObject, IDataErrorInfo
{
private IUnitOfWork _UnitOfWork;
private DelegateCommand<object> _AddUnicornCommand;

private string _FirstName;
private string _LastName;

[ImportingConstructor]
public SidebarViewModel(IUnitOfWork unitOfWork)
{
_UnitOfWork = unitOfWork;
_AddUnicornCommand = new DelegateCommand<object>(AddUnicornExecute,
(o) => { return !string.IsNullOrEmpty(FName) && !string.IsNullOrEmpty(LName); });
PropertyChanged += new PropertyChangedEventHandler(SidebarViewModel_PropertyChanged);
}

public DelegateCommand<object> AddUnicornCommand
{
get { return _AddUnicornCommand; }
}

public string FName
{
get { return _FirstName; }
set
{
if (_FirstName != value)
{
_FirstName = value;
RaisePropertyChanged(() => FName);
}
}
}

public string LName
{
get { return _LastName; }
set
{
if (_LastName != value)
{
_LastName = value;
RaisePropertyChanged(() => LName);
}
}
}

private void SidebarViewModel_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
switch (e.PropertyName)
{
case "FName":
case "LName":
AddUnicornCommand.RaiseCanExecuteChanged();
break;
}
}

private void AddUnicornExecute(object parameter)
{
Unicorn unicorn = new Unicorn() { FirstName = FName, LastName = LName };
_UnitOfWork.UnicornRepository.Insert(unicorn);
_UnitOfWork.Save();
}

#region IDataErrorInfo Members

public string Error
{
get
{
// no single error for the entire object
return null;
}
}

public string this[string columnName]
{
get
{
string errorMessage = null;
switch (columnName)
{
case "FName":
if (validateNameString(FName))
errorMessage = "Not valid this";
break;
case "LName":
if (validateNameString(LName))
errorMessage = "May not be empty";
break;
}

return errorMessage;
}
}

#endregion

// some basic input checks
private Func<string, bool> validateNameString = x => string.IsNullOrEmpty(x) || x.Length > 50;
}
}

Keep in mind that we save our object via the UnitOfWork and not directly via the UnicornRepository. IDataErrorInfo was also implemented.

It is time to add two Views to the Views folder. Start by adding a new User Control (WPF) called MainView.xaml to the project.

XAML code:

<UserControl x:Class="UnicornManager.Core.Views.MainView"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006">http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008">http://schemas.microsoft.com/expression/blend/2008"
mc:Ignorable="d"
d:DesignHeight="300" d:DesignWidth="300">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>

<TextBlock Text="Hello From MainView (Core Module)"
Grid.Row="0"/>
<ListBox ItemsSource="{Binding Unicorns, Mode=TwoWay}"
Grid.Row="1">
<ListBox.ItemTemplate>
<DataTemplate>
<TextBlock Text="{Binding FirstName}" />
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
</Grid>
</UserControl>

MainView codebehind:

namespace UnicornManager.Core.Views
{
/// <summary>
/// Interaction logic for MainView.xaml
/// </summary>
[Export]
[PartCreationPolicy(CreationPolicy.NonShared)]
public partial class MainView : UserControl
{
public MainView()
{
InitializeComponent();
}

[Import]
public MainViewModel ViewModel
{
set { DataContext = value; }
}
}
}

Here we Export our view and we Import our ViewModel. Just some basic MEF importing and exporting.

Add another User Control (WPF) called SidebarView.xaml to the Views folder:

<UserControl x:Class="UnicornManager.Core.Views.SidebarView"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006">http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008">http://schemas.microsoft.com/expression/blend/2008"
xmlns:prism="http://www.codeplex.com/prism">http://www.codeplex.com/prism"
mc:Ignorable="d"
d:DesignHeight="300" d:DesignWidth="300">
<Grid>
<StackPanel>
<Label Content="First Name:" />
<TextBox Text="{Binding FName, Mode=TwoWay,
UpdateSourceTrigger=PropertyChanged,
ValidatesOnDataErrors=True,
NotifyOnValidationError=True}"/>
<Label Content="Last Name:" />
<TextBox Text="{Binding LName, Mode=TwoWay,
UpdateSourceTrigger=PropertyChanged,
ValidatesOnDataErrors=True,
NotifyOnValidationError=True}"/>

<Button Height="30"
Content="Add Unicorn"
Command="{Binding AddUnicornCommand}"/>
</StackPanel>
</Grid>
</UserControl>

Codebehind for the SidebarView:

namespace UnicornManager.Core.Views
{
/// <summary>
/// Interaction logic for SidebarView.xaml
/// </summary>
[Export]
[PartCreationPolicy(CreationPolicy.NonShared)]
public partial class SidebarView : UserControl
{
public SidebarViews()
{
InitializeComponent();
}

[Import]
public SidebarViewModel ViewModel
{
set { DataContext = value; }
}
}
}

If we now run our application we still see the same empty window. This is because we forgot our Bootstrapper to tell that it can also search in our Module!

Select the UnicornManager project and add a reference to our Core project.
Next in the ConfigureAggregateCatalog method in our CoreBootstrapper add new AssemblyCatalogs for the Data and Core projects:

protected override void ConfigureAggregateCatalog()
{
base.ConfigureAggregateCatalog();
this.AggregateCatalog.Catalogs.Add(new AssemblyCatalog(typeof(CoreBootstrapper).Assembly));
this.AggregateCatalog.Catalogs.Add(new AssemblyCatalog(typeof(Data.Models.Unicorn).Assembly));
this.AggregateCatalog.Catalogs.Add(new AssemblyCatalog(typeof(CoreModule).Assembly));
}

Now run the project and have fun! ;)

What I like to hear from you (and if possible with code examples or a link to the adjusted solution)

  • Is the structure for this project ok?
  • There is no need to configure a ModuleCatalog in the bootstrapper when working with Prism and MEF or is that not true?
  • The Entity Framework has also some validation of its Entities (see UnitOfWork class: DbEntityValidationException). Is there a way to work with DataAnnotations on the Entity Frame Model classes instead of implementing the IDataErrorInfo on the ViewModel. Because this feels like writing double code. How do you show Entity (validation) exceptions on the UI directly?
  • How would you implement a Ribbon + add and merge tabpages when having multiple Modules?
  • MVVM: Do the Models also require INotifyPropertyChanged or only the ViewModels?

Download the project source here. (It is UnicornManager.zip)

Links:

(Maybe somebody can extend the solution so that others (and myself!) can learn from it)

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 ;-)