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?
LVL 1
Sam HaqueSr EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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
Scott PletcherSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sam HaqueSr 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.