January 11, 2024 at 5:33 pm
I have a server with 64Gb memory. SQL Server 2019 is installed and the memory allocated to SQL Server is limited to 40Gb.
I want to know how much memory SQL Server is actually using over time. How do I do this?
Broadly speaking, I know that SQL Server will continue to grab memory as it's needed but not return this memory, even though not currently required, back to the OS. The server I have has, at some point required 40Gb memory so if I look at Memory in Task Manager it shows 40Gb for SQL Server.
I thought sys.dm_os_process_memory.physical_memory_in_use_kb would show me what I want so I started to periodically capture these figures in a table but the value is pretty much always 40Gb, even if there is nothing running on SQL Server. Does this mean that I'm looking at the wrong metric or that even though sp_wh02 tells me no processing is occurring, SQL Server is indeed using 40Gb?
I've looked at sys.dm_os_performance_counters.Target_Server_Memory and sys.dm_os_performance_counters.Total_Server_Memory and both show 40Gb.
Everything shows 40Gb !!!!!!
Any help, much appreciated.
January 11, 2024 at 6:16 pm
I think that your "physical memory in use kb" is the number you are looking for, but even if things aren't running, stuff is in memory. SQL will keep things in memory for faster retrieval. For example, execution plans will sit in memory so if a query is re-run, it doesn't need to re-create the plan. It isn't JUST running queries that are stored in memory, but almost anything SQL decides it may need to use again can be put in memory. It is why after a server restart, the performance of the system is a bit slower until SQL "warms up".
My question would be why do you need this information? I've not really run into any scenario where I need to actively monitor this data. Basically, I would give SQL as much memory as my system allows with enough wiggle room for whatever is running on the OS and let the SQL engine manage the memory without me worrying about it UNLESS I see problems where more memory is going to be the solution.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 11, 2024 at 6:33 pm
Thanks Brian.
I suppose what I'm really trying to see is does SQL Server have enough memory. The server is experiencing periodic performance problems and I'm wondering if more memory will help.
January 11, 2024 at 8:08 pm
In that case, what you are likely more interested in is the page life expectancy. How long are pages estimated to remain in memory:
https://www.sqlshack.com/page-life-expectancy-ple-in-sql-server/
The lower the number, the more memory pressure you are under. Last I heard, numbers under 300 are where you want to start adding memory, but that number may have changed since I last researched it.
Also, periodic performance problems I would be curious if they are predictable. What I mean is can you do good guesses when things will slow down? If so, it is likely a scheduled process. If not, I'd check your monitoring tools to look for any long running queries and review what was happening during that time.
From my experience, periodic performance problems due to memory are not very common and the problem is almost always related to either a scheduled task inside SQL or outside of SQL OR someone running some query against the database and causing blocking on other queries. Not always mind you. I am not saying it ISN'T a memory issue - your scenario could be memory pressure. I am just saying in my experience adding memory doesn't really fix the problem.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 11, 2024 at 8:16 pm
SQL Server will use available memory to cache data. So if you allocate more memory to SQL Server it will probably use it. But that doesn't necessarily mean you need that memory just because it is used.
Why don't you allocate more memory to SQL Server and then see if it improves performance. It is standard practice to allocate the server memory minus (the amount needed by the OS and whatever else is running plus a bit) on the server. So on a 64GB machine you could allocate 58GB to SQL Server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply