September 2, 2011 at 2:05 pm
Hello,
I have an argument with a Windows SA about these settings, and I would like ask the community to see what you think.
I have a Windows Cluster 2003 with two nodes.
This is an active/active MS SQL 2005 SP2, which both are under a heavy load.
DB1 is running on node A:
This server has almost 25 GB of RAM,
SQL Server Max Memory Setting is 12.5 GB.
DB2 is running on node B:
This server has almost 15 GB of RAM,
SQL Server Max Memory Setting is capped at 5.5 GB.
My Windows SA says that the settings are correct, because if the instances are to fail over, each instance would use their own max: 12.5 + 5.5 = 18 MB leaving 7 to the OS.
My thinking is that we should increase the Memory as follow:
DB1 is running on node A:
This server has almost 25 GB of RAM,
SQL Server Max Memory Setting is 22 GB.
DB2 is running on node B:
This server has almost 15 GB of RAM,
SQL Server Max Memory Setting is capped at 12 GB.
To take advantage of the memory on each node, and if one instance is to fail over, the node would be overwhelmed, performance issues, etc, but both SQL Server would use whatever memory is available without affecting the normal windows cluster functioning.
Obviously running both instances is not desirable, but it is intended to be a "stop gap" measure, so we should be OK.
What do you think?
Not an option: upgrade to SP4, add more memory, etc. We are planning on doing that, but we need a quick solution.
September 2, 2011 at 2:37 pm
just for info is this on 32-bit/64-bit ? AWE ?
What's the min server memory settings for the instances, because that's the amount of ram the instance will need at startup / failover time.
Max server memory, just indicates the top for the instance. If another application needs ram, sqlsever will shrink its ram usage if needed, unless someone configured the service account to hold it memory. Is that the case?
You could set up jobs so the instances check if they run on the same box and alter their max server memory setting to avoid having to compete for ram amongst themselves.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 2, 2011 at 3:31 pm
I knew I was forgetting something
64 bit and AWE = 1
September 2, 2011 at 3:53 pm
MiguelSQL (9/2/2011)
I knew I was forgetting something64 bit and AWE = 1
Turn AWE off. It has absolutely no use on 64-bit processes.
Using locked pages?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2011 at 12:16 pm
regarding how to find out if your instance service account is using "locked pages in memory".
You will find such reference in the instances errorlog file at startup time (may already have been switched if checking a running instance)
http://support.microsoft.com/kb/970070 states:
To make sure that the trace flag has enabled Locked Pages in 64-bit editions of SQL Server,
verify that the following message is written in the SQL Server error log file at startup:
Using locked pages for buffer pool
http://msdn.microsoft.com/en-us/library/ms190730.aspx
Don't just enable it without understanding what is means and double checking it towards your topology !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2011 at 8:39 pm
Hello,
Interesting stuff the locked page in memory.
I did a search on several log files, and I couldn't find anything in regards to
"Using locked pages for buffer pool", so I guess it is not turned on.
I am running MS SQL 2005 SP2 and I see that support is not available for lock pages in memory (until SP3), and even then, it might have a negative impact, so I'm glad it is not ON.
I agree that AWE does not need to be turn on for a 64 bit, it doesn't hurt, so I haven't said anything yet, as it neither hurt nor help.
Min Server Memory is set to the same value as Max Server Memory, so I guess I'll have to adjust that value downwards to ensure that both instances can start if they are to run on the same node.
I guess I should have explained the main issue:
A few days ago, one of the instances failed over, and no apparent reason for it. A few days later, the other instance failed over.
These two nodes are under a huge load. Looking at the cluster log, it seems that the SQL Server was under heavy duress and it stop responding to the Cluster Service, so the cluster fail them over.
Now, trying to see what we can do in the mean time they move the most used databases out of here.
Thanks
September 6, 2011 at 7:35 am
MiguelSQL (9/5/2011)
Hello,Interesting stuff the locked page in memory.
I did a search on several log files, and I couldn't find anything in regards to
"Using locked pages for buffer pool", so I guess it is not turned on.
Log into the node and go to Administrative Tools > Local Security Policy > User Rights Assignment. Find the option for Locked Pages in Memory and check the accounts listed
MiguelSQL (9/5/2011)
I am running MS SQL 2005 SP2 and I see that support is not available for lock pages in memory (until SP3), and even then, it might have a negative impact, so I'm glad it is not ON.
You're on SQL Server 2005 x64 Standard are you?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 6, 2011 at 11:18 am
MiguelSQL (9/5/2011)
...Min Server Memory is set to the same value as Max Server Memory, so I guess I'll have to adjust that value downwards to ensure that both instances can start if they are to run on the same node.
...
Having both values equal is not an advised setup. I cannot recall the exact case, but I recall it may cause issues and just changing it to be different solved the issue.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 6, 2011 at 11:48 am
ALZDBA (9/6/2011)
MiguelSQL (9/5/2011)
...Min Server Memory is set to the same value as Max Server Memory, so I guess I'll have to adjust that value downwards to ensure that both instances can start if they are to run on the same node.
...
Having both values equal is not an advised setup. I cannot recall the exact case, but I recall it may cause issues and just changing it to be different solved the issue.
Having them the same disables some memory management options. The min server memory is not the amount SQL will allocate on startup. It's the amount that, once allocated, SQL won't drop below.
If min=max, once SQL has allocated that amount it won't release it. If the settings are the same and the OS is under memory pressure, the OS will ask SQL to reduce its memory usage and SQL won't be able to. That can lead of OS memory starvation (especially with locked pages), instability, crashes, cluster failover and all such unpleasant things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2011 at 2:41 pm
For the Local Policies question... the account is not listed.
Yes, this is MS SQL 2005 SP2 Standard Edition on Windows Cluster 2003 SP2.
That is a good explanation as to why it is not advisable to set up the memory settings the same.
Suffice to say is that I did not set up nor administer these clusters. I'm just being thrown into the problem...
I feel like walking into a dirty public restroom... no matter what you say when you leave, everyone would think that it was YOU
September 7, 2011 at 11:00 pm
short answer:
DB1, min 6-7 GB, unlimited max
DB2, min 3-4 GB, unlimited max
as it is currently configured, you have 40 GB of ram and only 18 GB can be used for SQL. not optimal. as others have mentioned, sql will give back memory, so the unlimited maxes won't hurt.
also, see this ibm redbook, pages 13-15 http://www.redbooks.ibm.com/redpapers/pdfs/redp3943.pdf
September 8, 2011 at 1:20 am
SpringTownDBA (9/7/2011)
short answer:DB1, min 6-7 GB, unlimited max
DB2, min 3-4 GB, unlimited max
as it is currently configured, you have 40 GB of ram and only 18 GB can be used for SQL. not optimal. as others have mentioned, sql will give back memory, so the unlimited maxes won't hurt.
also, see this ibm redbook, pages 13-15 http://www.redbooks.ibm.com/redpapers/pdfs/redp3943.pdf
I have to disagree using unlimited max because with x64 that will cause sqlserver to eat up ALL of your available RAM.
I can assure that isn't a pleasant experience !
Set your max to leave enough for OS and other processes running on your node(s) !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 8, 2011 at 2:19 am
ALZDBA (9/8/2011)
SpringTownDBA (9/7/2011)
short answer:DB1, min 6-7 GB, unlimited max
DB2, min 3-4 GB, unlimited max
as it is currently configured, you have 40 GB of ram and only 18 GB can be used for SQL. not optimal. as others have mentioned, sql will give back memory, so the unlimited maxes won't hurt.
also, see this ibm redbook, pages 13-15 http://www.redbooks.ibm.com/redpapers/pdfs/redp3943.pdf
I have to disagree using unlimited max because with x64 that will cause sqlserver to eat up ALL of your available RAM.
I can assure that isn't a pleasant experience !
Set your max to leave enough for OS and other processes running on your node(s) !
I concur, do not leave the max memory unlimited especially if you do decide to use lock pages in memory
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 8, 2011 at 2:35 am
ALZDBA (9/8/2011)
SpringTownDBA (9/7/2011)
short answer:DB1, min 6-7 GB, unlimited max
DB2, min 3-4 GB, unlimited max
as it is currently configured, you have 40 GB of ram and only 18 GB can be used for SQL. not optimal. as others have mentioned, sql will give back memory, so the unlimited maxes won't hurt.
also, see this ibm redbook, pages 13-15 http://www.redbooks.ibm.com/redpapers/pdfs/redp3943.pdf
I have to disagree using unlimited max because with x64 that will cause sqlserver to eat up ALL of your available RAM.
I can assure that isn't a pleasant experience !
Agreed. I've had that happen, server went completely unresponsive after SQL took 47 out of the 48 GB of memory on the server. 1x cluster failover in the middle of peak business. Not fun at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2011 at 9:44 pm
So, you'd recommend configuring max memory to be 1-2 GB less than physical on the box it usually runs on? (23 GB and 13 GB?)
Would you recommend setting min memory as well?
the official guidance from technet is:
single instance of sql on a machine, don't set min or max
multiple instances of sql on a machine (applies in this case), three options:
1. set max memory (disadvantage is wasted memory)
2. set min memory (uses all memory, but can slow the startup of second instance while waiting for memory to be released)
3. do nothing (the only option they don't recommend)
I do like the idea of a startup sql agent job to check what nodes everything is running on and adjust max memory dynamically.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply