none
Setting SQL server Express maximum memory usage

    Question

  • Hi All, How can we set the maximum memory usage for SQL server Express? I have tried setting it in Microsoft SQL Server Management Studio Express - Memory option, but it didn't have any effect.A problem I'm facing now is that the sqlservr memory usage keeps increasing and eventually slows down my application. Is there a way to set the upper limit or clear the unused memory? Thank you. Regards, Lawrence
    Thursday, June 22, 2006 10:02 AM

Answers

  • Changing max server memory does not require a restart of SQL Server. You should see a drop in memory usage pretty quickly, providing the majority of memory used by the sql server process space is from the buffer pool. 

    To be sure that your change is taking effect run this command from a query window

    exec sp_configure 'max server memory', <memory amount in MB>

    reconfigure

     

    Friday, June 23, 2006 12:35 AM

All replies

  • Did you restart service after changing the setting?
    Thursday, June 22, 2006 2:32 PM
  • sql express only supports 1GB of memory and I dont believe changing the min/max buffer pool/memory settings requires a reboot.
    Thursday, June 22, 2006 5:16 PM
  • Changing max server memory does not require a restart of SQL Server. You should see a drop in memory usage pretty quickly, providing the majority of memory used by the sql server process space is from the buffer pool. 

    To be sure that your change is taking effect run this command from a query window

    exec sp_configure 'max server memory', <memory amount in MB>

    reconfigure

     

    Friday, June 23, 2006 12:35 AM
  • I have done the above query, the memory still shooting up.  Does anyone has any idea on how to check the allocation of memory and how to find out if the memory used is from buffer pool?

    Monday, June 26, 2006 7:51 AM
  • Hi again mschai...

    "Does anyone has any idea on how to check the allocation of memory and how to find out if the memory used is from buffer pool?"

    Actually just as of late I have had to get into doing this myself as some of my clients are beginning to create successful websites that are powered by sql 2005. I find that performance monitor is an invaluable tool for questions such as yours here.

    SQL Server Buffer Manager: Total Pages is defined as

    "Number of pages in the buffer pool (includes database, free, and stolen pages)."

    for more information go to http://msdn2.microsoft.com/en-us/library/ms189628.aspx

    hope that helps,

    Derek

    Tuesday, June 27, 2006 1:18 AM
  • DBCC Memorystatus will give you a breakdown of what memory has been allocated by the SQL Server process.  It will be a superset of what's reported by Total pages.  http://support.microsoft.com/kb/907877/en-us

    Do you have any linked servers or extended stored procedures in use? 

    Tuesday, June 27, 2006 5:15 AM
  • Hi all, thanks for the reply. 

    Well, by setting the max server memory doesn't seem to be the solution because this will only influence the memory from buffer pool, and there are still a lot of other memories holding up, like the query execution cache, transaction log buffer, etc where you can find them from DBCC MemoryStatus.  My question is how can I relinquish all of them from an instance, as I found out that the instance process sqlservr.exe is still retained in a high memory usage even though my application has been closed which indicates database connection is closed and the database file is detached?

    Cheers Moon.

    Tuesday, June 27, 2006 6:24 AM
  • What size are you expecting the process to shrink to?  How much of the committed memory is actually in working set?
    Tuesday, June 27, 2006 9:52 PM
  • Since my application has been closed, i would say to shrink the process memory as much as possible.  I have also explored the option DBCC DELETEINSTANCE to kill the instance process, but to no avail, any idea?

    By just opening my application without much activities, according to the below figures getting from DBCC MemoryStatus, i guess the comitted memory is about 23MB.  How does the size of commited memory affecting in this issue?

    VM Reserved 1450688
    VM Committed 23200
    AWE Allocated 0
    Reserved Memory 1024
    Reserved Memory In Use 0

    VM Reserved 1449600
    VM Committed 22172
    AWE Allocated 0
    MultiPage Allocator 4424
    SinglePage Allocator 4384

    Jerome, Thank you for your help.

    Wednesday, June 28, 2006 2:13 AM
  • DBCC Deleteinstance certainly isn't going to affect memory usage in any way, and it has nothing to do with process managment.

    Any process that's running is going to have some overhead, even if it's idle.  Threads & thread stacks, file handles, image data (the exe and associated dll's), and yes some memory for internal structures. 

    Since you are running express it's pretty likely that any memory consumed by the process will get paged out by windows and have minimal impact.  You can confirm this by looking at the process\sqlservr\working set counter. 

    Wednesday, June 28, 2006 3:04 AM
  • When my application is closed, i do notice that the working set counter of sql main process(sqlservr) is dropping, but the instance process (sqlservr spanned from the main process) is still retained high in memory usage, possibly near to 1GB.  This certainly is going to have a big impact on us because this is not only slow down my application after opening for sometimes, but also affecting the performance of all other windows applications even after my application is closed.  I understand that every process running is going to have some overhead, isn't the memory should be released to minumum when it's not being used?

    I really hope there is something we can do to resolve this.  Thanks.

    Wednesday, June 28, 2006 3:38 AM
  • You can stop the instance by issueing the SHUTDOWN command before disconnecting.  That will stop the child instance.

     

    Friday, June 30, 2006 8:29 PM
  • Yeah great, this is something at least we can do and i hope we will not run into any memory problem.  Thank you Jerome.
    Monday, July 03, 2006 5:43 AM
  • Jerome,

     

    If you shutdown, the server shuts down right. Is there a correct way to identify and free the memory used by sql server ?

     

    Thursday, February 21, 2008 8:20 PM