SQL cluster server memory utilization is very high.

How can we set up the SQL so that it will take certain amount of memory from the SQL server?
Sam HaqueTier 3 engineerAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The vast majority of shops just set an upper limit for SQL Server to use.  The easiest way to do that is to right-click on the instance ("server") name, click on "Properties", select the "Memory" tab, then type the max number of MB you want SQL to use.

Be careful not to specify too much, as that can really harm performance on the server.  If you have only 16GB, try 12GB for SQL.  If you have 32GB, you can try 24 or 26 for SQL.

SQL will free memory if Windows really needs it, but it's a slow and expensive process.  You definitely do not want SQL releasing memory except in very unusual situations.
0
 
arnoldConnect With a Mentor Commented:
Please clarify, using sp_configure advanced options, you can set min and max memory usage of SQL instance that will Cary between nodes in a cluster....

Not sure I understand what you are asking given you refer in both cases to SQL.

Node A and Node B have Each X GB of memory.

Is your setup active/active two SQL cluster instance with one running on NodeA while the other Running in NodeB.
In the event of a Node failure, both instance fail onto the reminding node? Or you have a three node active/active with nodeC as the preferred failover....

Potentially you meant to better split between SQL and the OS ?

Basically, if not limited SQL server will consume as much memory as the system has available as the need ...... It will not release memory once allocated.
0
 
Sam HaqueTier 3 engineerAuthor Commented:
We have split between SQL and OS. We allocated the memory by going to properties>Memory Tab.
0
 
arnoldCommented:
allocating a split (50/50) OS to sql ....

OS usually needs only 2GB or so provided only sql is running on it, if there are other functions the  system provide......

SQL will consume as much available memory as there is. The size of the database/s is what dictates ..

if the entire DB can be "loaded" into memory the proformance will be better compared to when the cummulative size of the active DBs are a large factor of available memory. This could potentially cause the system resources moving one data set out while responding to another query.....

If the change works for you, great..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.