2

I have a database on Azure that is around 500mb, ~50 tables and a few tables with 100k+ records. A single table with ~1000k records. This is not a big database (around 20-50 DTU's). I have an ASP.NET MVC application that runs on top of this database.

When I run towards this database either from local or on my test/production environment, the database is extremely fast. My code is "solid production code" (indexes, paging), meaning I do not do anything crazy and this works on pretty heavy production loads.

However, when I import this database locally, it's VERY, VERY slow (pages takes 10-20 seconds to load, or simply fails). This results in I basically cannot run my application locally. Here is an example error I get (DK error message, but the typical error - see The wait operation timed out. ASP ) :

enter image description here

I experience the same problem when I run queries outside my main application. I am 99% sure this is a database problem, not an application problem because when I run scripts it's very slow too.

Any idea what the problem is? Why is my localhost so slow it cannot barely run normal queries?

This is how I created my local databae:

enter image description here

Save to local desktop:

enter image description here

I import into (localdb)\MSSQLLocalDB :

enter image description here

Wait for this to happen and everything is confirmed. I then change my connection string to this database.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lars Holdgaard
  • 9,496
  • 26
  • 102
  • 182
  • 2
    I would start by installing SQL Server Developer Edition, instead of trying to troubleshoot performance on SQL Express LocalDb. https://www.microsoft.com/en-us/sql-server/sql-server-downloads – David Browne - Microsoft Jul 12 '19 at 17:21
  • @DavidBrowne-Microsoft Nice suggestion. I'll download over the weekend and test! – Lars Holdgaard Jul 12 '19 at 18:46
  • There are a couple of solutions here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4e6ba2d7-9565-438c-af97-82ba357302d5/query-runs-much-slower-when-connect-to-sql-server-locally?forum=sqldatabaseengine – Rafe Oct 20 '20 at 06:06

2 Answers2

2

I suspect you do not have enough RAM to host your own server. I had that problem, locally hosting MSSQL server 2012 with 8GB of RAM on a Windows 10 machine, with Core i7 10th Gen. Sometimes there were no problems, but mostly I was getting very slow query response times, and even timeouts. This was my development machine, and I noticed that all the RAM was being consumed, just from development alone, without any database connections. So I suspected that there was not enough memory for querying the database as well.

I increased the RAM to 32GB (but 16GB would have sufficed), and problems solved.

ocm
  • 87
  • 7
0

Enabling TCP/IP connections to your local server could resolve this issue.

  1. Start -> Run -> mmc
  2. File -> Add/Remove Snap-in...
  3. SQL Server Configuration Manager -> [OK]
  4. SQL Server Network Configuration -> Protocols for MSSQLSERVER -> TCP/IP=Enabled
  5. Restart server.
Kris
  • 1,336
  • 11
  • 16
  • Why would this resolve it? – wordsforthewise Aug 20 '23 at 05:28
  • Named Pipes can often have more overhead than TCP/IP due to additional context switching and synchronization, which can cause it to be slower in some scenarios. – Kris Aug 25 '23 at 00:55
  • 1
    That said, it's entirely possible that OP's problem was caused by index fragmentation and could be resolved by rebuilding statistics and optimising indexes. – Kris Aug 25 '23 at 00:57