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 4:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:45 AM
Points: 275, Visits: 863
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.
Post #1507409
Posted Tuesday, October 22, 2013 4:29 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 @ 3:52 PM
Points: 42,849, Visits: 35,978
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 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 #1507414
Posted Wednesday, October 23, 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 1, 2013 1:02 AM
Points: 529, Visits: 1,688
which counters should we monitor to troubleshoot memory pressure in SQL Server?
Post #1507816
Posted Wednesday, October 23, 2013 2:41 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 @ 3:52 PM
Points: 42,849, Visits: 35,978
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 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 #1507824
Posted Friday, October 25, 2013 10:49 AM
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 1, 2013 1:02 AM
Points: 529, Visits: 1,688
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
Post #1508529
Posted Friday, October 25, 2013 10:55 AM


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 @ 3:52 PM
Points: 42,849, Visits: 35,978
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


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



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 #1508533
Posted Monday, October 28, 2013 1:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:22 AM
Points: 85, Visits: 190
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?
Post #1509090
Posted Tuesday, October 29, 2013 3:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 208, Visits: 844
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 memory


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.
Post #1509234
Posted Tuesday, October 29, 2013 6:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:50 PM
Points: 1,232, Visits: 6,646
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.

Post #1509320
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse