Post reply

Memory pressure when calling a C# assembly

  • September 11, 2009 at 11:10 am

    #1052456

    That is *extreme and soon to be fatal* fragmentation. I used to consider a reboot when we got down below 50MB contiguous.

    8MB is not a big block - and who's to say how long it lasted? Remember that SQL Server itself uses the multi-page allocator too.

    In addition, if the function required workspace or dependent classes needed to be loaded...well.

    Anyway, can we just agree that 8MB contiguous in the entire 2GB user address space is not very much?

    If you were on 64-bit, you would have 8TB (7TB on Itanium) of VAS.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 11, 2009 at 11:56 am

    #1052484

    I think we can agree. But as you can see from my query, we are starting at less then 50MB of Max free space, KB. So my only option other then 64 bit servers is to try using the -g switch... say -g512? Will this switch increase the Max free space, KB value?

    Daniel

  • September 11, 2009 at 1:34 pm

    #1052533

    Jeff Moden (9/8/2009)

    I can't help on the problem you're having, Daniel, but I am curious... what does the CLR do?

    Heh... you can avoid all this memory problem stuff by avoiding the CLR to begin with. So, let me ask again... "What does the CLR do?" 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 11, 2009 at 8:37 pm

    #1052628

    danielmanke (9/11/2009)

    I think we can agree. But as you can see from my query, we are starting at less then 50MB of Max free space, KB. So my only option other then 64 bit servers is to try using the -g switch... say -g512? Will this switch increase the Max free space, KB value?

    Daniel

    Hey Daniel, yes it will (but 512 is a lot - consider -g384 first). Being lazy by not reading back, but I am assuming you have AWE enabled? AWE will let SQL Server manage the buffer pool in a very VAS-efficient way, making higher values for -g more practical.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 13, 2009 at 6:30 am

    #1052806

    Yes, AWE is enabled... with about 20GB max server memory

    Daniel

  • September 13, 2009 at 6:38 am

    #1052808

    danielmanke (9/13/2009)

    Yes, AWE is enabled... with about 20GB max server memory

    Daniel

    Ok Daniel. Just remember that the 20GB is physical RAM, and the 32-bit virtual address space available to SQL Server is less than 2GB by default. The -g allocation comes out of this <2GB memory (AWE-mapped memory can only be used for buffer pool) so go steady.

    Try -g384 first, please!

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 13, 2009 at 2:23 pm

    #1052853

    Fine... if you don't want to tell me what the CLR does so maybe I can come up with an alternate solution, then enjoy your memory problems. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 13, 2009 at 2:51 pm

    #1052855

    Jeff Moden (9/13/2009)

    Fine... if you don't want to tell me what the CLR does so maybe I can come up with an alternate solution, then enjoy your memory problems. 😉

    My guess is that Daniel doesn't have total control of the design, or the .NET code is doing stuff that would be difficult or impossible in T-SQL. The fact that it uses a good deal of VAS is maybe a pointer to its complexity...?

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 13, 2009 at 3:05 pm

    #1052858

    He might if we could find out what it was about and perhaps make a suggestion on the T-SQL side.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 14, 2009 at 6:36 am

    #1053140

    Sorry Jeff,

    Sunday was a day for Golf and football... I did see your request for what the CLR (C# code) does. Unfortunately, the application is an off the shelf

    ERP and I have no control over the development and function of this code. At one time it was a t-sql code (which I could at least read to know what it was trying to do). I was forced into converting to a C# assembly after migrating to sql server 2005. So I have to work with what they give me...I was able to test the -g switch. I believe it will give me more time until memory pressure, but it is not a solution. I am painted into the corner of restarting sql server on a periodic basis. I am hoping to restart only on the weekends.

    Thanks so much for the dicussion. Again I apologize for not being more responsive (it was sunday!)

    Daniel

  • September 14, 2009 at 7:42 am

    #1053202

    danielmanke (9/14/2009)

    Sorry Jeff,

    Sunday was a day for Golf and football... I did see your request for what the CLR (C# code) does. Unfortunately, the application is an off the shelf

    ERP and I have no control over the development and function of this code. At one time it was a t-sql code (which I could at least read to know what it was trying to do). I was forced into converting to a C# assembly after migrating to sql server 2005. So I have to work with what they give me...I was able to test the -g switch. I believe it will give me more time until memory pressure, but it is not a solution. I am painted into the corner of restarting sql server on a periodic basis. I am hoping to restart only on the weekends.

    Thanks so much for the dicussion. Again I apologize for not being more responsive (it was sunday!)

    Daniel

    Hi Daniel,

    You don't need to restart the instance to fix this. All you need to do is run "DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS". Granted, it is not the ideal solution, but it would be much less invasive than restarting the instance.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • September 14, 2009 at 3:18 pm

    #1053469

    Marios Philippopoulos (9/14/2009)

    You don't need to restart the instance to fix this. All you need to do is run "DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS". Granted, it is not the ideal solution, but it would be much less invasive than restarting the instance.

    No! Dropping all execution plans and dumping the buffer pool like that will minimal effect on the multi-page allocator. You might free a few pages which were allocated by SQL Server outside the buffer pool, but never do this on a production server!

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 14, 2009 at 4:13 pm

    #1053491

    Paul White (9/14/2009)

    Marios Philippopoulos (9/14/2009)

    You don't need to restart the instance to fix this. All you need to do is run "DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS". Granted, it is not the ideal solution, but it would be much less invasive than restarting the instance.

    No! Dropping all execution plans and dumping the buffer pool like that will minimal effect on the multi-page allocator. You might free a few pages which were allocated by SQL Server outside the buffer pool, but never do this on a production server!

    Sorry, my bad, I meant:

    dbcc freesystemcache ('all')

    This will address the problem, while helping you avoid an instance restart.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • September 14, 2009 at 4:38 pm

    #1053498

    Marios Philippopoulos (9/14/2009)

    Sorry, my bad, I meant:

    dbcc freesystemcache ('all')

    This will address the problem, while helping you avoid an instance restart.

    Really sorry Marios, but still no go 🙂

    Books Online

    Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachstore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." This message is logged every five minutes as long as the cache is flushed within that time interval.

    Generally speaking, there is no way to free up the so-called MemToLeave region (used by CLR) aside from restarting SQL Server.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 14, 2009 at 8:26 pm

    #1053532

    danielmanke (9/14/2009)

    Sorry Jeff,

    Sunday was a day for Golf and football... I did see your request for what the CLR (C# code) does. Unfortunately, the application is an off the shelf

    ERP and I have no control over the development and function of this code. At one time it was a t-sql code (which I could at least read to know what it was trying to do). I was forced into converting to a C# assembly after migrating to sql server 2005. So I have to work with what they give me...I was able to test the -g switch. I believe it will give me more time until memory pressure, but it is not a solution. I am painted into the corner of restarting sql server on a periodic basis. I am hoping to restart only on the weekends.

    Thanks so much for the dicussion. Again I apologize for not being more responsive (it was sunday!)

    Daniel

    Heh... no problem. I forget that there are folks in this world that do know what "Sundays" are. I guess I'll have to Google the new word. 😛

    Anyway, understood on the CLR someone has heaped upon you and I apologize for not being able to help with the memory problems it's causing. Thank you for taking the time to explain, Daniel.

    As a side bar, you've given me an extra item for my checklist to reject 3rd party software for. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 44 total)

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