SQL Server vs EXPRESS

Question

Hello all,
I have an app that connects to a SQL Server EXPRESS database. It's been like that for a few years, but lately the traffic increased, there are a few times where there is a "spike".
Not thousands of users connected simultaneously, but at times I have hundreds doing some heavy lifting requests from web and from mobile.

The result, is that I see some errors in my logs, and they are about DB locks. When it happens of course the app is unresponsive, it takes a long time to load, and then it fixes itself and it starts working again.

I already tried to optimize the code as best as I could, now I am wondering if the limitations of the EXPRESS edition could play a role here. For example limited 1GB memory etc.
Would it be recommended to migrate to the paid version of SQL Server to solve these kinds of issues? Or do you think it's not related?

Answer 1

You can check this with some TSQL queries related to memory pressure which might tell you your SQL Server is under memory pressure (cause of that memory limit)

As a short explanation... every query needs some memory to work (e.g., working buffer, cache, resultset, sorting, versioning).
If you have just one user, everything will be fine, and if you have ten users with different queries, your SQL Server will request the ten-time amount of memory. If you have 100s of users...

so yes, your performance issue might correlate with the memory (or even - depending on the version - with compute power).

But there also might be some options to speed up your queries - with query and/or index tuning.

I recommend setting up a second instance with developer edition to test/dev a new version of your application. There you have the chance to increase the working memory to at least 2GB or 4GB (or even more).

Answer 2

It is difficult to give advice with that brief information. I will have to admit, though, that "hundreds doing some heavy lifting requests from web and from mobile" certainly does not sound like something that I would expect Express to be able to cope with.

Then again, it is not unlikely that some query tuning could save you from having to cough up the license cost for Standard. But that also requires that you have the knowledge to do that tuning.

What more exactly are those error messages you get?

Answer 3

Hi Stesvis-5434,

Agree with others.
In addition, please refer to the following docs to help you monitor performance issues:
https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-memory-usage?view=sql-server-ver15
https://social.technet.microsoft.com/wiki/contents/articles/3214.sql-server-monitoring-disk-usage.aspx
https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-cpu-usage?view=sql-server-ver15

Best Regards,
Amelia