SQL Server vs EXPRESS

Question

question

Stesvis-5434 asked May 5, '22 Stesvis-5434 commented May 6, '22

SQL Server vs EXPRESS

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?

sql-server-general

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-05-05T16:00:14.927Z

BjoernPeters answered May 5, '22

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-05T21:40:28.687Z

ErlandSommarskog answered May 5, '22 Stesvis-5434 commented May 6, '22

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?

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Stesvis-5434 · May 06 at 02:24 PM

I agree, very difficult also for me to identify the root here.
I use Entity Framework to query the database so I don't really use queries directly...
Code-wise I think I tuned it as much as possible and followed best practices.
Requests are usually really fast when I test them, which makes me think that the only difference is when the users do the same, and at the same time.

0 ·

ErlandSommarskog Stesvis-5434 · May 06 at 09:09 PM

I use Entity Framework to query the database so I don't really use queries directly...

That's not a good start. (You are among database people, and database people generally don't like ORMs, exactly because of what you say.)

Code-wise I think I tuned it as much as possible and followed best practices.

How do you know, when you don't see the queries? :-)

Any way, Query Store is a great tool to find resource-consuming queries.

You still haven't shared the error messages you have seen in your logs.

0 ·

Stesvis-5434 ErlandSommarskog · May 06 at 10:06 PM

Fair enough, it's like doctors vs chiropractors lol.
Thanks for the answer.

0 ·

Answer 3 · 2022-05-06T02:24:47.057Z

AmeliaGu-msft answered May 6, '22

Hi Stesvis-5434,

Agree with others.
In addition, please refer to the following docs to help you monitor performance issues:
https://docs.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://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-cpu-usage?view=sql-server-ver15

Best Regards,
Amelia

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question