How Do I Measure MemtoLeave

  • Currently on SQL 2005 SP3

    I was getting;

    AppDomain 50 (dbname.dbo[runtime].62) is marked for unload due to memory pressure.

    After researching, I decided to increase my MemtoLeave using the -g startup parm. The memory pressure errors went away. However, I still want to verify the -g parm worked OK.

    I've been searching quite a bit on how to measure the current memtoleave area and have not been very successful.

    DBCC MEMORYSTATUS shows me 63 different memory sections. MS Support doc (or BOL) on this command is not very helpful.

    Is there a simple way to get the current allocation of memtoleave?

    thanks very very

    Tim White

  • Tim - First of all I really like the account name that you have!

    Wondering if this BLOG POST helps. Doesn't seem to answer the question completely but seems to provide some insight.

    Hope this helps!

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Thanks David, I had seen this blog and ran the script, I just wasn't so sure how to read the output.

    Total Avail = 314776

    Max free = 51776

    An interesting note is that the total avail changed from the last time I ran the script (a couple of hours ago).

    I used -g512 in my startup parm.

    So, just guessing here, is this saying my total memtoleave may be 512 (or not) and SQL is using a total of 314 ? So, other non-sql processes may be using the difference?

    Thanks for helping!

    Tim White

  • The answer is.... I don't know. I am poking around at it a bit and will take a deeper look and reply back with what I find.

    I am certainly interested with anything else that you find on this as well. Thanks!

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Thanks,

    Just another guess, since the Total Avail changes over time, this could be the total available memtoleave for SQL in the total pool of 512. I saw it dip below 256 this morning, so that would explain my AppDomain errors going away.

    Just not able to see anything that says the total memtoleave area is 512.

    Tim White

  • Tim - I was able to validate that the "Total Avail Mem, KB" from that script is the MemToLeave on a test instance that I have with very little activity. Once the instance starts it is pretty close to what I set it as and then does fluctuate based on utilization of that.

    Also found THIS BLOG as well which I would like to dig through a bit more when time permits. 😉

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Update:

    The query in David's first link will return the "Total Bytes Free" in the MemToLeave memory area. (in kb)

    PerfMon:

    PROCESS - PRIVATE BYTES (Choose "sqlserv" on the right side)

    will return the "Total Bytes Used" in the MemToLeave memory area.

    These two together will give you the total memtoleave area.

    Tim White

  • AppDomain 50 (dbname.dbo[runtime].62) is marked for unload due to memory pressure.

    I would ask what are you loading at AppDomain 50 at runtime that is not cleaning up your resources?

    You may have to run SQL Server profiler, FXCOP and the CLR Debugger to see what is using all your memory.

    Kind regards,
    Gift Peddie

  • We deal with a lot of 3rd party vendor software here that uses SQL Server as their DB platform. If it were a bigger problem and I didn't have an easy work-around (-g512), I'd pressure the vendor some more.

    I had one case a while back where the vendor said they were using CLR's for encrytion. I asked them to turn it off and the APPDomain errors went away.

    In this most recent case though I got a lot of "duhhhhh" when I mentioned it to the supporting developer. so, I just moved on.

    A thanks to David for the support.

    Tim White

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply