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:

Advertisements

WebMatrix 3 (preview)

webmatrix 3 preview

WebMatrix3 preview is available for download.

New changes for even simpler website creation

In addition to everything which WebMatrix 2 offers, WebMatrix 3 brings you easy access to your Windows Azure websites. Icon-sized previews of your sites make them easy to find and open. Access your remote sites as seamlessly as local sites. When you need to protect your work or collaborate on team projects, you’ll love our integration of Git and TFS source control systems. These are just a few of the highly requested features we’ve added in WebMatrix 3!
http://www.microsoft.com/web/post/webmatrix-3-preview-how-to-articles

 

Try it out today on http://www.microsoft.com/web/webmatrix/next/

Web Scraping with PowerShell

In PowerShell v3 you have some new useful cmdlets that allow you to download and parse a website.
The code in this post will demonstrate very basic scripts that could get you started with Web Scraping.

If you don’t know if you have PowerShell v3, use this command to find out:

get-host

The first script to get you started with web scraping:

$site = Invoke-WebRequest -UseBasicParsing -Uri www.bing.com
$site.Links | Out-GridView

This will give you all the links from the given website in a gridview.

The next script will give you all the email addresses that are in a mailto: anchor:

$site = Invoke-WebRequest -UseBasicParsing -Uri www.mywebsite.net
$site.Links | foreach {
if ($_.href.ToLower().StartsWith("mailto:")) {
$_.href.SubString(7) | Out-Default
}
}

By coincidence the ‘mywebsite.net’ has anchors using the mailto: prefix.

The last script is a very cool script from StackOverflow where I just modified the url to make sure the script works in several European countries:

function Get-FlightStatus {
     param($query)
$url = "http://www.bing.com?cc=us&amp;q=flight status for $query"
$result = Invoke-WebRequest $url
$result.AllElements |
        Where Class -eq "ans" |
        Select -First 1 -ExpandProperty innerText
}

Use it like this:
(to test you can just paste this after the function in Windows PowerShell ISE )

Get-FlightStatus LH3102

It will give you a result similar to this:

Flight status for Lufthansa 3102 
flightstats.com · 2 minutes ago   

Departing on time at 5:35 PM from HAM 
FROMHAM 
Hamburg5:35 PM 
12/30/2012Terminal 2 
TOVIE 
Vienna7:05 PM 
12/30/2012

PS C:\>

Don’t forget, web scraping can be illegal!

Have fun 😉

Take a look at “Web Scraping with Perl” and the PowerShell tag.

New microsoft.com Metro website

Check it out on: http://www.microsoft.com/en-us/preview/

Tip:

I Write Like

Check which famous writer you write like with this statistical analysis tool, which analyzes your word choice and writing style and compares them with those of the famous writers.

Any text in English will do: your latest blog post, journal entry, comment, chapter of your unfinished book, etc. For reliable results paste at least a few paragraphs (not tweets).

Check it out at: http://iwl.me/

Runno: foursquare for sports

Runno is a game played using a mobile app and on a website
By using a mobile app during outdoor activities of running, cycling, rollerblading (more activity modes will be added) you earn points or the currency of Runno called Walluta, and each calorie burnt will earn you 7 Wallutas. At the same time, you have the possibility of claiming areas and building your own Kingdom. To claim an area, you walk, run or bike around that.
Needs a Windows Phone app. *hint* 🙂

info ]