SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server is NOT taking allocated Max Memory


SQL Server is NOT taking allocated Max Memory

Author
Message
madhu-686862
madhu-686862
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 1688
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226941 Visits: 46335
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

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


madhu-686862
madhu-686862
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 1688
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?
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19387 Visits: 10042
Are you - by chance - running on an x86 platform?

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226941 Visits: 46335
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

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


madhu-686862
madhu-686862
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 1688
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226941 Visits: 46335
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

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


madhu-686862
madhu-686862
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 1688
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.
arnipetursson
arnipetursson
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1389 Visits: 1019
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
madhu-686862
madhu-686862
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 1688
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search