Post reply

Memory usage of MSDE keeps growing

  • October 6, 2004 at 6:34 am

    #162380

    I am getting ready to deploy a manufacturing application that uses a SQL2K MSDE database. It is not a very large database but it gets updates every few seconds, so it is very active.

    When I first start the application, the total memory allocated to SQL (as seen in task manager) is about 6M. this is before any transactions stat with the application.

    Once the transactions start, the memory usage grows consistantly. After about 12hours of operation, the usage is over 600M (per Task Manager).

    My question is, is this normal? Can it be controlled? If so, how? If not, what options do I have to manage this?

    It becomes a problem when the PC running the application must start caching resources in order to maintain SQL performance.

    Thanks in advance for your help...

  • October 6, 2004 at 7:26 am

    #525574

    Yes, MSDE is a SQL Server engine so it will continue to request more memory as needed. You can, however, set a maximum amount of memory MSDE can use, much in the same way as with SQL Server. Connect with osql and then issue:

    EXEC sp_configure 'show advanced option', '1'

    GO

    RECONFIGURE

    GO

    EXEC sp_configure 'max server memory', 'memory in MB'

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    The minimum value is 4 (for 4 MB).

    K. Brian Kelley
    @kbriankelley

  • October 6, 2004 at 7:36 am

    #525580

    Thanks, that is exactly whjat I was looking for.

    Now, applying these settings is fairly straight forward, but if they start to cause issues, how do I revert back to default settings and start over?

    I haven't used this before and do not know how it will hit performance.

    btw: I am thinking that setting this to a maximum of 150M should suffice, so the line should be

    EXEC sp_cinfigure "max server memory" "150"

    go

    etc...

    is the correct syntax, right?

  • October 6, 2004 at 7:43 am

    #525581

    The default value is 2147483647.

    The best way to handle this is first to issue the sp_configure statement setting advanced options (with RECONFIGURE) to see the current setting, something like this:

    EXEC sp_configure 'show advanced option', '1'

    GO

    RECONFIGURE

    GO

    Then check to see what the current setting is. To do so, issue the max server memory but don't specify a value (and don't use the RECONFIGURE WITH OVERRIDE).

    EXEC sp_configure 'max server memory'

    GO

    Then you can set the memory to what you want:

    EXEC sp_configure 'max server memory', '150'

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    And that should do it.

    K. Brian Kelley
    @kbriankelley

  • October 6, 2004 at 7:56 am

    #525582

    Running this now!!!

    Thanks for all the help! It is truly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

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