October 5, 2021 at 4:33 am
Hi All ,
My server has 2 TB data files ..
CPU is 16
sql 2016 enterprise
memory for SQL is 50 GB out of 57 GB
Do you think this server requires more memory ?
how much is recommended
Appreciate your feedback
thank you
October 5, 2021 at 8:01 am
Hi,
how should someone know how much memory you will need.
What kind of application do you run against the database. Is a OLTP or an OLAP system?
Have you ever tried some query to take a lock, if your sql server will need some memory?
Have you got an monitoring system, or something else?
Kind regards and good luck,
Andreas
October 5, 2021 at 12:20 pm
You have to monitor the system in order to understand how it is behaving. Capturing wait statistics and queues can tell you where slow downs are occurring. Capturing performance metrics around memory can show you how the service is being managed. Capturing query metrics to see which queries are causing the most memory use leads you to the core of the problem.
This isn't a really simple math formula where X amount of data means Y amount of memory and TA-DA, we never have a bottleneck. Your data structure affects memory use. The number of users affects memory use. The queries, the indexes, the statistics. On and on. There just aren't simple answers to these kinds of questions. For good or for ill, these are complex systems and they will absolutely have complex answers. If databases were as easy to manage as toasters, we wouldn't be getting paid.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2021 at 4:08 pm
The other thing to keep in mind is do you have anything else running on that server? From a SQL Server standpoint, SSIS, SSAS, and SSRS all use their own memory space outside of the SQL Server memory. Plus if you have any other tools running on there (antivirus for example), you will need memory available for that.
Plus you want to leave some for the OS. I like to leave roughly 4 GB for the OS so I can RDP in if needed and not have to wait for things to shuffle around on the page file. In your case, you have 7 GB left over for the OS and other processes which may or may not be enough.
Usually, if I am thinking about adding more memory, it is due to some performance thing and I have already exhausted all other options AND have confirmed there is memory pressure inside SQL. Are you noticing performance issues? If so, it may make more sense to investigate those performance issues rather than throwing random hardware at the problem and hoping it fixes things. If SQL has 50 GB of memory and your PLE (for example) is 2 months, adding memory may not help. Not that PLE is the ONLY thing to look at, it is just one of the metrics. And sometimes, memory pressure can be resolved by tuning some queries.
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply