Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server is NOT taking allocated Max Memory Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 12:51 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 01, 2013 1:02 AM
Points: 529, Visits: 1,688
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


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.
Post #1507296
Posted Tuesday, October 22, 2013 12:57 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
Is there available memory for SQL to allocate?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1507299
Posted Tuesday, October 22, 2013 1:15 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 01, 2013 1:02 AM
Points: 529, Visits: 1,688
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?

Post #1507308
Posted Tuesday, October 22, 2013 1:31 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
Are you - by chance - running on an x86 platform?

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1507325
Posted Tuesday, October 22, 2013 1:44 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1507336
Posted Tuesday, October 22, 2013 2:09 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 01, 2013 1:02 AM
Points: 529, Visits: 1,688

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?
Post #1507353
Posted Tuesday, October 22, 2013 2:11 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1507355
Posted Tuesday, October 22, 2013 2:28 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 01, 2013 1:02 AM
Points: 529, Visits: 1,688
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.
Post #1507369
Posted Tuesday, October 22, 2013 4:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 5:20 PM
Points: 237, Visits: 763
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
Post #1507405
Posted Tuesday, October 22, 2013 4:14 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 01, 2013 1:02 AM
Points: 529, Visits: 1,688
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
Post #1507407
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse