1

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 ?

6
  • 1
    sqlservercentral.com/Forums/Topic1215340-391-1.aspx Might be helpful.
    – atokpas
    Nov 22, 2016 at 6:07
  • 3
    You asked similar question here was that not helpful.
    – Shanky
    Nov 22, 2016 at 6:31
  • It was very helpful @Shanky.But now I am trying to decrease the value for SQL Server.It is PROD server,so I am trtying to be more careful sir and get adviced from experts about advantages and disadvantages sir. Nov 22, 2016 at 6:52
  • 1
    So my question is how often you get such alerts ?, Are you facing performance issue. If you decrease buffer pool such messages would get frequent.
    – Shanky
    Nov 22, 2016 at 7:08
  • 1
    I suggest you ignore this for time being,but keep monitoring SQL Server memory parameters if you want to completely stop this alert you can change the threshold value in SCOM, may be to 100
    – Shanky
    Nov 22, 2016 at 7:21

2 Answers 2

3

(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.

0

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

1
  • Yes @Web Engg Vipin,can you please be of more detail on this.Can you let me know about limit values in details if you have detail on them.Thanks Nov 22, 2016 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.