October 22, 2013 at 12:51 pm
Hi,
we have two failover clustered sql server instances on active cluster node.
Total RAM =16 GB
Max Memory for INS1 = 8 GB
Min Memory for INS1 = 1 GB
Max Memory for INS2 = 4 GB
Min Memory for INS2 = 1 GB
Memory left for OS = 4 GB
Problem: PLE is dropping to zero verfy frequently for INS1[/b]
Analysis:
1. INS1 is taking only 3 GB out of 8 GB max memroy
2. INS1 has only 2.34 GB cache siz and 500 MB of procdure cache
3. INS2 is taking only 1 GB out of 4 GB max memory
4. INS2 has only 571 GB cache siz and 415 MB of procdure cache
When Appliation queries are running, PLE is dropping to zero very frequently on INS1.
Questions:
1. WHy INS1 and INS2 are NOT taking their allocated max memroy?
2. If INS1 takes all allocated memory, then cache size will increased then there will be no PLE issue. What is stopping INS1 to take its allocated 8 GB memeory?
Please provide insputs to further analyze this issue.
October 22, 2013 at 12:57 pm
Is there available memory for SQL to allocate?
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
October 22, 2013 at 1:15 pm
Is there available memory for SQL to allocate?
8 GB Max Memory is configured for INS1 but it is taking only 3 GB. That means it has 5 GB available right?
October 22, 2013 at 1:31 pm
Are you - by chance - running on an x86 platform?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 22, 2013 at 1:44 pm
madhu-686862 (10/22/2013)
Is there available memory for SQL to allocate?
8 GB Max Memory is configured for INS1 but it is taking only 3 GB. That means it has 5 GB available right?
No.
I asked, is there available memory for SQL to take, not how much of the configured max server memory has it used.
I can configure SQL with a max server memory of 20GB on my desktop and it will only take at most 10. That doesn't mean there's 10GB available. There will be none available as the machine has only 16 GB and other applications use up to 6GB.
So, is there available memory for SQL Server to allocate?
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
October 22, 2013 at 2:09 pm
Are you - by chance - running on an x86 platform?
No, We are using x64.
Hi Gail, I looked at Memory counter for AvailbelMBytes and its value is 198. Is that the one you are asking for?
October 22, 2013 at 2:11 pm
Yup, so the server has less than 200 MB of available memory, so there's no free memory for SQL to allocate. Hence there's no way it could allocate more memory. Sounds like you either need more memory or to find out what's using all the memory and move it elsewhere.
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
October 22, 2013 at 2:28 pm
Thanks Gail.
INS1 is taking only 3 GB out of 8 GB max memroy and INS2 is taking only 1 GB out of 4 GB max memory
So total 4 GB is used for both the instances out of 16 GB. So where is the rest of memory 12GB going?
One thing I'm not able to understand is where the memory is going and how to find where the memory is going?
What should be the Ideal or acceptable value for AvailableMbytes?
Can you please shed some light on this?
Thanks in advance.
October 22, 2013 at 4:10 pm
Have you looked in task manager?
Btw
Max Memory for INS1 = 8 GB
Min Memory for INS1 = 1 GB
Max Memory for INS2 = 4 GB
Min Memory for INS2 = 1 GB
Makes Total 14GB not 12GB
October 22, 2013 at 4:14 pm
Have you looked in task manager?
Btw
Max Memory for INS1 = 8 GB
Min Memory for INS1 = 1 GB
Max Memory for INS2 = 4 GB
Min Memory for INS2 = 1 GB
Makes Total 14GB not 12GB
I did not include Min memory when I calculated. Do we need to include Min memory too?
From my understanding, task manager will not give the correct picture
October 22, 2013 at 4:21 pm
It might tell you what else is using memory on the server.
Also in my experience Task manager may show that you are using less memory than you really are, but not more.
Sorry, I mis-read the min memory part.
October 22, 2013 at 4:29 pm
madhu-686862 (10/22/2013)
So total 4 GB is used for both the instances out of 16 GB. So where is the rest of memory 12GB going?
That's what you're going to have to figure out. Look in Task manager (though note that it lies) and the memory-related counters in perfmon, including those in the process object.
What should be the Ideal or acceptable value for AvailableMbytes?
A few hundred at very least. More if you remote desktop into the machine
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
October 23, 2013 at 2:09 pm
which counters should we monitor to troubleshoot memory pressure in SQL Server?
October 23, 2013 at 2:41 pm
It's not memory pressure in SQL Server that you have (well, you know you have that already). Something else, outside of SQL, is apparently taking large amounts of memory, so that's what you need to investigate.
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
October 25, 2013 at 10:49 am
For INS1 reduced the Max memory to 6gb from 8 gb and now ins1 is using 3 GB out of 6 GB
For INS2 reduced the Max memory to 3gb from 4 gb and now ins2 is using 1 GB out of 3 GB
Now 9gb (6+3) is allocated for both instances and left 7 GB to OS. But still the available memory is 196 mb
That means, clearly some OS process is taking lot of memory. But how to find what it is?
In Task manager, I did NOT see any OS process taking lots of memory:crying:
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply