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


SQL Server Memory Configuration, Determining MemToLeave Settings


SQL Server Memory Configuration, Determining MemToLeave Settings

Author
Message
Jonathan Kehayias
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6571 Visits: 1818
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
prash.k.r.p
prash.k.r.p
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Jonathan Kehayias
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6571 Visits: 1818
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
Duran
Duran
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2906 Visits: 1819
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
Jonathan Kehayias
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6571 Visits: 1818
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
Duran
Duran
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2906 Visits: 1819
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.
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