0

I've recently had my laptop replaced and I've had to install Visual Studio 2015 and SQL Server 2014 Express with Management Studio.

My previous environment was Visual Studio 2015 with SQL Server 2008 R2 Express with Management Studio.

I restored the 2008 R2 databases into SQL Server 2014 Express, same database names, logins etc.

Now when I run any of my ASP.NET MVC 5 applications (using Entity Framework 6) on my laptop using Visual Studio, I'm getting sporadic timeout errors. Please see below.

enter image description here

enter image description here

enter image description here

Occasionally the application database calls will perform as expected, but mostly they are either very slow or timeout.

I'm finding it difficult to understand why this is as on my previous laptop using SQL Server 2008 R2 Express I never had any of these issues.

Also, these applications are on a live web server and being used by 1000s of users each day without any of these problems. This makes me think there is something possibly wrong with the installation of SQL Server 2014 Express on my laptop.

I have seen others comment on extending the Command Timeout on my DbContext

public class MyDatabase : DbContext
{
    public MyDatabase ()
    : base(ContextHelper.CreateConnection("Connection string"), true)
    {
    ((IObjectContextAdapter)this).ObjectContext.CommandTimeout = 180;
    }
}

But I don't see this as a solution, as I didn't need this with my previous laptop/ environment and the current live applications also don't need it.

I'm stumped here and would really appreciate any help or guidance.

Thanks.

Update

Thanks to the suggestions from Steve Py I decided to check the memory performance from my new laptop when running Visual Studio 2015 and SQL Server Express 2104 concurrently. I've included a screen shot below which shows that 90% of the available memory is used (3.5G out of 3.9G). I'm far from an expert in tuning up a device for software development, however, this seems it may be a reason as to why when I run my applications locally that they are timing out.

Is there anyone on Stackoverflow who ca inform me if this looks like the possible problem?

Thanks.

enter image description here

Improve this question
3
  • 1
    Ugh, I'm not an expert but those numbers look scary for a development PC that would be running SQL Server + Visual Studio. CPU and disk are also getting thrashed. 4GB is a minimum for a Windows box, but I wouldn't recommend anything less than 8GB for SQL Server. CPU and Disk show very high activity prior to this screenshot. Do you still have the old laptop because I'd be looking at the specs of the old laptop compared to this new one. Commented May 2, 2019 at 22:22
  • 1
    @StevePy I've had my laptop upgrade to 12G of Memory and hey presto, all of my timeout issues have gone. Everything is working as expected now. Lesson here is that on a Windows 10 machine with Visual Studio and MS SQL Server, 4G of memory isn't enough. At least it wasn't on my laptop. Commented May 13, 2019 at 9:20
  • 1
    Good to hear. :) Yeah, 4GB is bare bones for a laptop without the load of VS & SQL Server. Not to mention that laptops are commonly delivered with quite a few extra services and such installed and active by default which doesn't help. Commented May 13, 2019 at 11:24

1 Answer 1

Reset to default
2

Firstly I'd look at hooking up a profiler to capture the queries coming from EF. For SQL Server you can use ExpressProfiler. This will give you the actual SQL EF is trying to run, the # of row reads, writes, and execution time. Copy the SQL queries and paste them into a new query window on the DB and execute them, plus have a look at the execution plan. Does the execution time correlate with EF? (change parameters and re-run in SQL to ensure you aren't getting cached results)

Other factors are the hardware on the two laptops. You'd hope that the new laptop would have more grunt than the old one, more cores, better cores, more RAM, but how do they compare? How much memory is free when nothing is running? What kind of HDD was in the two machines? For instance dropping from an i7 with an SSD down to an i3 with 5400rpm HDD, and half the RAM will be extremely noticeable, even if the clock speed is higher.

When it comes to databases there are a number of factors that can impact performance, even when backing up and restoring. For instance the Isolation Level and recovery model settings for the database can play a part, especially for larger databases. I'd also look at server settings such as how much RAM the database server is allocated to be able to use. Feel free to paste some results from the profiler for slow queries.

Edit: Based on the screenshot of the resource use, my guess is your new laptop is potentially underpowered. 4GB of RAM is bare-bones with Windows 10 especially to be running Visual Studio and SQL Server, even for just a development use database. The history graph for CPU and disk also show heavy activity. If that's all you've got to work with then the next step would be to look at what is using the memory. SQL Server by default will attempt to use whatever is available, and it can be quite greedy, but it's generally a good idea to set boundaries on the server. From SQL Management you can bring up the properties of the server and select "Memory" to set a minimum and maximum memory size. For 4GB I'd set the minimum to 500MB and the max to 2000MB. For processors you can use "Boost SQL Server Priority"

Next, on the database side of things look into the file and recovery options. What is the size of the database MDF file, and transaction log? (LDF file) From the database properties window under "General" you should see the "Size" which is the MDF size. For the LDF you will probably need to check on the file system. A large LDF can be bad for performance and indicate your database should be backed up and the log compressed/truncated. Lof files default to grow by percent so they can grow fast and churn the disk. In the "Options" tab you cna check the "Recovery Model" and set that to "Simple" for a dev database to significantly cut on log file churn/growth. Production databases will use "Full".

For development purposes it helps to have a bit more grunt from a laptop. While things like "ultra books" look like good options and are nice and lightweight, they rarely have the grunt and resources needed for a dev environment. (plus generally poor keyboards and displays to boot! :) ) There is also a significant gap in price between ultra books and workstation replacement laptops. What I've found fit in that gap and serve as exceptional development PC replacements are gaming laptops. They are tuned for performance and usually come with 8GB minimum with expansion available. They also happen to come with exceptional keyboards and displays. They're typically a fair bit cheaper than workstation replacements that seem to price in a premium. I use an MSI GE65 series which came with 16GB, SSD+HDD, a great keyboard, and was over $1000 cheaper than the closest "workstation" laptop. It does draw a couple stares coming into a client site with a gaming laptop with it's LED keyboard and lid badge, not a single game on it though! :)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for these suggestions. They will help me in trying to find out what is going on here! Thanks.
I added some notes on things to check around the database settings that might help breathe some life into the database performance. Also check what else is running that can free up resources. That laptop seems pretty bare-bones for dev work. :{

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.