Increasing enough RAM for OS from Total Physical RAM

Question

Here is my situation:

Total Physical RAM : 128 GB

Assigned to SQL Server : 122 GB (Lock pages in memory for SQL Server Account)

So for OS we have close to 6 GB left now.

Now we are facing issue like below:

SQL Server:Buffer Manager Page writes/sec has exceeded the threshold of 90

Now we are planning to decrease the assigned SQL Server Maximum memory from 122 GB to 111 GB.

I used below links to set maximum server settings for SQL Server.

https://github.com/bornsql/scripts/blob/master/max_server_memory.sql

https://bornsql.ca/s/memory/

Will, increasing Physical RAM for OS level help us ?

When I checked the Task Manager process utilization SQL Server doesnot seems to be using a lot of Memory. It is servicehosts and Microsoft Monitoring tool that consumes along with SQL Server occupies second and third position.

I know there are plenty of forums which will help to understand the memory logics in SQL Server but may I know where should I start learning about SQL Server Memory utilization.

How can I find what is consuming the most memory in SQL Server ?

Please advice me ?

Answer 1

(Full disclosure: I created the Max Server Memory Matrix and associated script that you are referring to in your question.)

Per this excellent blog post, which contains a detailed explanation of the issue you're experiencing, you'll find this little quote:

The recommended Page reads/sec value should be under 90. Higher values indicate insufficient memory and indexing issues.

Without knowing too much about your environment, as you did not give us very much to go on, you may benefit from more RAM. For instance, your database is 400GB in size, and data access patterns require large scans to access lots of data.

On the other hand, your indexes might need attention. For instance, your database is 400GB in size, and data access patterns require large scans, when in fact you only need to read one or two columns in that table.

Instead of worrying about buying hardware, perhaps it would benefit you to start with the basics:

• Have you analysed the missing index DMVs to identify possible new indexes?
• Have you considered using filtered indexes?
• Have you rewritten your queries to make better choices with data?

The point is, there's a lot you can do before considering purchasing more RAM. Figure out what your queries are doing, and help them make better life choices.

Brent Ozar has just open-sourced his course on how the database engine works, and I reckon you would benefit from that, as well as many other free online resources.

Answer 2

This Incident is Showing your SQL Server Database is Reading limited to 90 Pages Per Second, you Should to decrease or set the page reading limit