February 5, 2020 at 11:10 am
I have SQL Server 2019 Standard Edition in Windows 2019 box with 32Gb RAM. I set the SQL Server's maximum server memory to 24Gb which leaves the remaining 8Gb to be used by OS and 2 web apps deployed on the same server. Checking the RAM used by SQL Server on the first day via task manager, it only consumes like 30% of the total RAM but after 3 days, it climbs to 80+%. My questions are as follows:
February 5, 2020 at 12:05 pm
This is by design.
SQL is designed to be the only thing on the server. It will consume what you tell it to consume. So you said to consume 24GB which is just a bit less than 80% of the 32GB available. Then the OS and WebApps will need some allocation too, so not surprised to see your seeing a >80% usage.
If it where me I would move everything that's not SQL related to their own servers, and then increase the RAM allocation to 28GB and leave it be.
Hopefully you have capped the application pools in IIS or whatever web software your using to use no more than 4GB total across them, to ensure they can't impact SQL.
Mixing services (Web/SQL) could be seen as a security risk in some organisations.
As a side and a starter for 10, I would grab a copy of the Accidental DBA guide and take a look at chapter 4 for memory management. Some good topics there to mull over to see if you need to do further memory management.
http://assets.red-gate.com/community/books/troubleshooting-sql-server-accidental-dba.pdf
February 5, 2020 at 3:08 pm
sql will start off using minimum memory - then as you run more queries then the mem usage will go up (data pages being put into buffer cache)
general rule of thumb - leave 4GB for the kernel and if you have other services then allow a bit more - sounds like your mem setup is fine, if your system is at 80% mem usage then the buffer cache is doing it's job.
there are a few perfmon stats you can check
Buffer cache hit ratio : what percent of queries get data from memory rather than disk (memory good, disk bad)
Page life expectancy : how long the average 8kb block of data stays in RAM
these will tell you if you need more RAM for SQL
MVDBA
February 5, 2020 at 7:32 pm
Is this RAM usage spike after 3 days expected considering I already set the max server memory to 24Gb? I thought setting the max limit no longer allow SQL to use beyond that.
Max Server Memory controls most of SQL Server like the buffer pool, proc cache, etc, but there are a few things it doesn't so usange could end up being slightly higher than the 24 Gig you've specified:
https://bornsql.ca/blog/need-know-memory-limits-sql-server/
Is this a normal SQL Server behaviour and should I do nothing? Or should I configure something that should flush RAM usage to make it lower again?
As others have stated, yes that is normal behavior for it's memory usage to grow over time. If you do something to flush things out of RAM, then they will have to be read from disk again, so I'd advise against doing this. SQL Server is good at managing its own memory using a "least recently used" algorithm.
Based on the scenario above, is 24Gb allocation to SQL Server and the remaining 8Gb to the rest (OS, web apps, etc) an optimal settings?
...Should I just allot additional RAM by making it 64gb or more? Will this ensure that I will not reach the 80+% RAM usage or will it still reach that as SQL Server will just use whatever memory it can acquire?
Are you seeing a large number of hits to the OS's swap file? If Windows isn't paging things out too frequently then the overall system memory usage isn't a problem. You're trying to find the balance with keeping as much in physical RAM as possible since writing to OS swap file adds an extra disk write and and extra disk read to an operation that your program will think is in RAM. If you are seeing a lot of OS swap file usage and this is causing increased disk IO then you can consider lowering SQL Server's Max Server Memory or adding more RAM.
February 6, 2020 at 6:21 am
Thanks guys. All infos above are useful. I will further check what I can find as I go along and will update this thread.
February 10, 2020 at 3:59 pm
http://assets.red-gate.com/community/books/troubleshooting-sql-server-accidental-dba.pdf%5B/quote%5D
Legitimate question, this book was written for 2008 and 2008 R2, how much of the information in there is still relevant, even in the introduction to the book its stated that many sections of the book had to be rewritten to apply more current technology, theres been many advances in SQL server since this was written.
Is there a more recent version of this?
Thanks
February 10, 2020 at 4:10 pm
http://assets.red-gate.com/community/books/troubleshooting-sql-server-accidental-dba.pdf%5B/quote%5D
Legitimate question, this book was written for 2008 and 2008 R2, how much of the information in there is still relevant, even in the introduction to the book its stated that many sections of the book had to be rewritten to apply more current technology, theres been many advances in SQL server since this was written.
Is there a more recent version of this?
Thanks
The core of any DB technology won't change much ,the last ANSI standard for a select statement was 28 years ago (someone will correct me on this)
yes, those books are a bit outdated, but a good starting point as long as you are not trying to use graph tables or columnstore indexes
I would say that Online is your friend.. brent ozar, scarydba , redgate lots of cool places - just take 2 minutes out of your day and pick a blog to read.. even better, subscribe and you get a bunch of cool emails
MVDBA
February 12, 2020 at 4:37 am
This was removed by the editor as SPAM
February 12, 2020 at 8:50 am
Sometimes SQL Server underestimates the work you’re about to do, and doesn’t grant you enough memory. Say you’re working with a table variable, and SQL Server only estimates it’ll find one row inside – but millions of rows start to come back. Your query doesn’t get granted more memory, oh no – it just spills to disk (TempDB.) What’s worse, if you run that query a hundred times, it’ll spill to disk every single time.
I was about to comment on this, then I read the end of your post. 2019 has addressed this issue, but it has broken a dozen more. #prayingforaservicepack
MVDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply