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
arnipetursson
arnipetursson
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 1019
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86116 Visits: 45229
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

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


madhu-686862
madhu-686862
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 1688
which counters should we monitor to troubleshoot memory pressure in SQL Server?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86116 Visits: 45229
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

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


madhu-686862
madhu-686862
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 1688
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 memoryCrying
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86116 Visits: 45229
madhu-686862 (10/25/2013)
That means, clearly some OS process is taking lot of memory. But how to find what it is?


GilaMonster (10/22/2013)
Look in Task manager (though note that it lies) and the memory-related counters in perfmon, including those in the process object.


In Task manager, I did NOT see any OS process taking lots of memory Crying


No one said it was an OS process (other than you)

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


craig.schlieve
craig.schlieve
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 259
SQL Server is already taking all the memory it can. That's why lowering the Max Memory allocation didn't change anything.

What other programs or processes are running on this server? Does this machine have 12 GB of memory available or does it only have 6? Is this a virtual server where there is another server taking that memory?
crmitchell
crmitchell
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 1759
madhu-686862 (10/25/2013)
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 memoryCrying


It could also mean that whatever you are doing on INS1 and INS2 don't require any more memory than they are using and as a consequence doesn't use it.

How much memory shows up in system idle processes in task manager? - That is your available free memory. If its a low percentage you have something else taking up resources - so look to see what that something is! If on the other hand it is up around 90%+ then you aren't running anything which needs more than 10% of memory.
Greg Edwards-268690
Greg Edwards-268690
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1495 Visits: 8481
You mention application queries.
This might imply you have other processes (IIS, SSAS, SSRS, etc.) running on the machiine.
These are not included in the SQL instance memory, even thought some may be part of the SQL install.


I'd start by looking in perfmon for indications that the system in paging out.
Process Explorer (part of the old Sys Internals toolset) might give a better look than task manager.

SQL Server only uses the memory it needs, and Max Memory is used as a guideline for it to clear out older / less frequently used items. Reducing this, and your results showing that very little really changed, shows that something else besides SQL Server is demanding memory.

I agree with Gail - this is what you need to track down.
You do not mention performance issues, just memory allocation.
Although I would suspect that performance be degraded started the hunt.
There are many things that can contribute to this, but start with the simple checks on what is really running.
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