Click here to monitor SSC
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
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47254 Visits: 44383
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
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9829
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47254 Visits: 44383
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
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47254 Visits: 44383
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
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 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
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 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
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 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