More instances of sqlserver and memory usage

Question

1.00/5 (2 votes)

See more:

Hi,

A customer has a server (Windows Server 2019 with 55 Gb memory) with two instances of SQLServer (SS 2019 Standard edition), one for production, one for testing. The production SQLServer instance ends up occupying almost all the available memory at the end of the day. The test instance does not have enough memory if you need to run tests in the evening.

Thank you

What I have tried:

It seems that Memory pressure detection[^] doesn't seems to work properly in that server. How can I monitor and regulate its behaviour? Leaving aside the behavior of the test environment, there may also be other processes that require memory and that SQL server must let act as designed.

Posted 22-Feb-24 0:01am

Menci Lucio

Updated 22-Feb-24 4:51am

v2

Add a Solution

2 solutions

Solution 2

Griff's solution is the correct one. When you think about it, SQL Server isn't like other typical server applications that are designed to free memory up. SQL Server is designed to be performant, and to treat the server as its own playground to grab resources as needed. It does this for a very good reason; if it can pull information (such as data pages) out of an in-memory cache, this is far faster than actually reading them from disk. If you could somehow force SQL Server to dump that memory, you're forcing it to rebuild the caches over a period of time, which will result in slower queries.

Don't try to use SQL Server in a way it's not intended for. Use it for its strengths, and allow it to do what it's good at doing. Let your production database hog as much memory as it possibly can with good, fast access data.

Posted 22-Feb-24 5:59am

Pete O'Hanlon

Comments

Menci Lucio 22-Feb-24 12:13pm

No. SQL Server IS DESIGNED to free memory up. From the link I posted (https://learn.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver16#memory-pressure-detection):
"The Resource Monitor task monitors the state of external and internal memory indicators. [...] Memory brokers monitor the demand consumption of memory by each component and [..,] it calculates and optimal value of memory for each of these components. [...] This information is then broadcast to each of the components, which grow or shrink their usage as required."

Dave Kreskowiak 22-Feb-24 12:56pm

...which GROW and shrink their usage AS REQUIRED

SQL Server is written to take up as much memory as it can get away with for performance reasons, mostly for caching indexes and other data.

You're reading it like it should play nice with everything else running on the server. It doesn't play nice. It favors its own performance over that of other non-SQL related processes.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2024-02-22T01:12:00

Solution 1

A better solution is to move the test instance to a separate server: that way there is less chance of "interactions" where software being tested affects the production DB. Remember, if the production server is using that much memory, it's for a reason - so any other load you add to that server will probably have an effect on production response times.
A physical separation prevents any mistakes you make from impacting production (and has the advantage of a "spare machine" you can fall back to if there is a hardware problem with the production server).

Posted 22-Feb-24 1:12am

OriginalGriff

Comments

Richard Deeming 22-Feb-24 7:57am

"Everyone has a test environment; some people are lucky enough to have a separate environment to run production in." 🤣

OriginalGriff 22-Feb-24 8:02am

🤣
If I could upvote that, I would!

Menci Lucio 22-Feb-24 9:25am

Right observations, but not the right answer. It seems that Memory pressure detection[^] doesn't seems to work properly in that server. I don't know how to monitor it and change its behaviour.

Dave Kreskowiak 22-Feb-24 10:11am

Oh, it's the correct answer all right.

What you're not considering is if you screw something up on the test instance and it start hogging all the memory on the box, you're going to bring production to a halt.

ALWAYS run production on its own dedicated hardware. Failure to do so is just begging for problems and lost production.

Menci Lucio 22-Feb-24 10:52am

I think the focus of the problem is not properly centered. Let me correct the question

Dave Kreskowiak 22-Feb-24 11:44am

It doesn't matter how you frame the question. You will still have the exact same problem. Memory pressure detection works, just not how you think it does.

Your overall problem is still the same. YOU ARE RISKING PRODUCTION by putting UNSTABLE AND UNSECURED non-production work on the same server as production work. In every environment I've worked in the last 30 years this would never be allowed.

OriginalGriff 22-Feb-24 12:13pm

Dave is right - think about it.
If your production server is having memory pressure problems then that's slowing the production server - and depending on what it's serving to whom, that's costing the company money. If it's an internal server that costs worker time and that's expensive. If it's customer facing data (like an online shop for example) then it can lose you significant amounts of money as they bugger off elsewhere and you lose the sale.
So ask yourself this: if your software test blocks the server how much does it cost the company, and who will get blamed when it happens? Because it does; it will! That's what testing is for - to ensure those problems never get near the production server!

Menci Lucio 22-Feb-24 12:27pm

We have dozen of customers that has the same configuration. The customer that has the little DB space (some of them has over 150Gb of DB, one of them is over 200Gb, this one has 11 Gb) and uses less the server (it makes about 10 invoices at a day, some other customers has the same work per minute, it uses only few barnches of the applications on the DB, the others uses much more). All other customers works well in both test and production (that are in the same machine but in different sandboxes, and stress test on test machine doesn't impact in the production one). Why has this customer problems on test memory space if all the other that stress much more the memory and cpu usage haven't if the pressure memory detection works properly? What is that make you figure out that are so secure that memory pressure detection works properly? Do you saw some reports?

Dave Kreskowiak 22-Feb-24 13:06pm

You would be much better off asking your customer what else is running on this production SQL server. The server is taking up the memory it needs to support whatever else it's doing, possibly outside of the work of your application.

One of the production SQL Servers I run works on 8GB of RAM for months at a time and it only takes about 3 to 4GB of RAM the entire time it's running, for a database that's about 14GB.

There's more running on your customers server than you know about.

Menci Lucio 23-Feb-24 3:38am

That machine has three SQL instances. The first is the default installation and contains only the master database. The other two instances each hold only the ERP database, one for production and one for testing.

There is nothing else. My sensations say that there is something works bad in that machine. I asked for that.

FOR ALL WHO READ UNTIL HERE. There might be a misunderstanding. When I talk about "test," I do not refer to developer testing. Our supplier has its own developer and test environments. We, while developing the customization, have our own developer and test environments. We put the application (often, when required) in a fifth environment to permit third parties to test their development when an external connection is required. Finally, the sixth and seventh environments are the production and user testing environments (where customer employees can test and learn how to use new capabilities). These are the two environments present in the production machine, what I have called until now "production" and "test".