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 «««123

SQL Server Memory Configuration, Determining MemToLeave Settings Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 12:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 19, 2015 8:53 AM
Points: 1,686, Visits: 1,802
prash.k.r.p (10/22/2012)
HI Jonathan,

I have one issue here, If nay idea please let me know

Some of my SQL server are not releasing a VAS memory so we need to restart the server every time to release it. any cause or any solution / settings needed please.

Thanks in advance.
KRP


What VAS consumers exist in the instance? The only thing you can do is start removing them one at a time to find the one that is causing the problem. If you can't do that, upgrade to 64-bit, where you won't have problems caused by fragmentation of the VAS.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1375657
Posted Tuesday, October 23, 2012 3:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 26, 2012 8:38 AM
Points: 2, Visits: 6
HI ,
Vas reaches up to 90 %, i mean when we check total memory is 108 mb and the free space would be below 20 mb like that. it is not coming down until it restarts SQl server.

Thanks.
KRP
Post #1375923
Posted Tuesday, October 23, 2012 6:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 19, 2015 8:53 AM
Points: 1,686, Visits: 1,802
prash.k.r.p (10/23/2012)
HI ,
Vas reaches up to 90 %, i mean when we check total memory is 108 mb and the free space would be below 20 mb like that. it is not coming down until it restarts SQl server.

Thanks.
KRP


That doesn't answer the question I asked. I am sorry, but unless you answer the specific questions I ask about this problem, I can't help you understand the causes beyond what is in the article. I don't have time to ask the same questions over and over and chase down your problem for you if you aren't going to answer the questions.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1375993
Posted Tuesday, August 18, 2015 11:52 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:06 AM
Points: 480, Visits: 1,571
Hey Everyone,

Interesting thread, and I have ended up here via various articles because I am seeing alot of the following messages 2-3 times a minute in the logs for a number of databases...,

AppDomain 161 (DatabaseName.dbo[runtime].174) is marked for unload due to memory pressure.

...sometimes it will be just one database several times in one minute, then other times all three will appear. There are more than 3 DB's on the server (11 user DBs in total), but these ones are the only ones to appear with the error. The entire screen can be full of nothing but these errors.

I thought I found my answer here, but it raises more questions. Higher up in the thread I read that if I am on 64bit it's not relevant, we are running Windows 2012 and SQL Server 2012 SP1 build 11.0.3000.0, so perhaps none of this really relates.

As for the physical memory, which reading earlier also does not seem relevant (but I'll include it here anyway), is set at
MIN 61440
MAX The default of 2147483647

...not set by me, when I am in charge I set max appropriately.

I ran the VAS query and got the following results...

Total Available Mem: 137145368572
Max Free Size : 137109232840

...here I am just totally confused. This is in the TBs. The box is physical, not virtual.

I'd love a bit of guidence in getting this cleared up. I'm not sure what way to turn as this point, and the box is critical. Obviously I am more than happy to provide more information.

Thank you for reading, and your time.

Regards,
D

PS: As an aside, I just ran the following query in case it revealed anything extra, from the bottom line onwards it was the same...

SELECT type, virtual_memory_committed_kb, pages_kb
FROM sys.dm_os_memory_clerks
WHERE virtual_memory_committed_kb > 0 OR pages_kb > 0
order by virtual_memory_committed_kb desc;

type virtual_memory_committed_kb pages_kb
MEMORYCLERK_SQLCLR 123612 24712
MEMORYCLERK_SQLBUFFERPOOL 89204 978872
OBJECTSTORE_LOCK_MANAGER 65536 1160
MEMORYCLERK_SOSMEMMANAGER 20912 0
MEMORYCLERK_XE_BUFFER 4608 0
MEMORYCLERK_SQLSTORENG 1344 11552
USERSTORE_TOKENPERM 0 8

Post #1712844
Posted Wednesday, August 19, 2015 9:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 19, 2015 8:53 AM
Points: 1,686, Visits: 1,802
Duran (8/18/2015)


I'd love a bit of guidence in getting this cleared up. I'm not sure what way to turn as this point, and the box is critical. Obviously I am more than happy to provide more information.



How good are you at profiling .NET memory usage and reading/changing .NET code? Or even better, what do they need to use SQLCLR in the database for exactly? A lot of cases of app domain unloads can simply be memory leak detection in the .NET hosted API's and SQLOS is unloading the AppDomain as a part of those policies. It's not difficult to trigger that with just a basic DataSet object that you populate with some data. Or use String.Split with a big string as explained by Adam Machanic here http://sqlblog.com/blogs/adam_machanic/archive/2009/04/26/faster-more-scalable-sqlclr-string-splitting.aspx

The simple answer is that you might not be able to figure it out from a SQL Server standpoint, you would have to be reviewing the .NET code and using a performance profiler as well to see what's going on there too.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1713018
Posted Tuesday, August 25, 2015 6:24 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:06 AM
Points: 480, Visits: 1,571
Hi Jonathan,

Thank you for replying, well Ive not profiled .Net memory usage before, is this done using SQL profiler/Performance monitor? I can change the code, but I can work with the developers are writing/updating it. Can you recommend a performance profiler that would be good for me, I understand you get what you pay for, but it would need to be free, and that's if I am allowed to install it on a machine in the first place.

I did run perfmon the last time we did a test run, the pain is that the test box is VM running on an already stressed host, but within the test window I saw regular and significant spikes with Granted Workspace Memory and Memory Grants Outstanding (which hit 100, sometimes momentarily, sometimes for a few seconds) at the same time. The process hits two databases on different instance (on different hosts) and I saw the same activity on both). The PLE was quite low as well, but I'd expect that under heavy querying.

I'll have a read of the blog, thanks for the link.

Regards,
Paul.
Post #1714495
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse