Click here to Skip to main content
15,908,768 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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
Updated 22-Feb-24 4:51am
v2

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).
 
Share this answer
 
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
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.
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900