none
mmsm.exe using lots of memory

    Question

  • Hi,

     

    We have a new win 2k8 r2 server running SQL 2k8 r2 with 4gb ram.

    There is not much on there yet, about 5 small db's and already the mmsm.exe is just about maxing out the memory...

    Any ideas?

    Thursday, July 8, 2010 8:07 AM

Answers

  • Its not generally recommended that you run SQL Server with Boost Priority checked.  If it is SSMS that is using the memory, there isn't anything in SQL Server that you can change to affect that.  SSMS is a separate application, and generally, you wouldn't be running that regularly on the SQL Server itself.  You would generally use SSMS from a workstation (for example your desktop/laptop) and you'd connect to the SQL Server, that way the server isn't wasting memory and CPU resources running the client tools, your desktop is.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, July 9, 2010 10:02 PM
    Moderator
  • I do not see any program in SQL Server 2008 called "mmsm.exe" .  There is a "ssms.exe" is the SQL Server Management Studio application.  This is a user tool and would need to be open in order to be using memory.  Closing this application should clear the memory.

     

    However, if you did not set the "max memory" setting in SQL Server, the default is to use all RAM (physical and virtual) until the server starts paging and starts to crawl.

     

    Please see:

    http://support.microsoft.com/kb/321363

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

    Thursday, July 8, 2010 2:15 PM
    Moderator
  • Hi,

    1. What’s your operation system architecture (32-bit or 64bit)? Generally, A 32-bit machine can directly address only 4 GB of memory, and by default, Windows itself reserves the top 2 GB of address space for its own use, which leaves only 2 GB as the maximum size of the VAS (virtual address space) for any application, such as SQL Server. However, the following facts will affect the SQL Server memory to use more than 2GB memory.
    a. If you enable   /3GB flag in the system's Boot.ini file, it allows applications to have a VAS of up to 3 GB.
    b. SQL Server 2008 R2 supports dynamic allocation of AWE mapped memory. Enabling AWE allows SQL Server access to available physical memory in excess of the limits set on their configured virtual memory address space. If you also enable /PAE which is the added ability of the IA32 processor to address more than 4GB of physical memory, SQL Server can access more than 4GB memory.If fewer resources are required, SQL Server can return AWE mapped memory to the operating system for use by other processes or applications. Also, dynamic AWE mapped memory is supported on servers with less physical memory than the configured limit on virtual memory address space.

    On a 64-bit platform, the AWE Enabled configuration option is present, but its setting is ignored. However, the Windows policy Lock Pages in Memory option is available, although it is disabled by default. This policy determines which accounts can make use of a Windows feature to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. It is recommended that you enable this policy on a 62-bit system.

    2. To check the exactly memory used in SQL Server, please refer to the following FAQ:
    http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/8dc892cd-633f-4fa2-a973-b9a0c3430b6d

    3. If you want to limit the memory used by SQL Server, try to set the max server memory option:
    http://msdn.microsoft.com/en-us/library/ms178067.aspx

    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, July 9, 2010 8:45 AM
    Moderator

All replies

  • Sorry but what is mmsm.exe
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 8, 2010 8:22 AM
    Answerer
  • I dont know... Must be something for sql 2008?
    Thursday, July 8, 2010 8:34 AM
  • I am not aware of, have you searched on internet?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 8, 2010 9:41 AM
    Answerer
  • The location the service is running from:

    C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE

    SQL Server Management Studio it looks like... how can that use most of my memory...

     

    After a bounce it is using about 25mb of mem at the moment but will keep a eye on it. Looked like it could be a memory leak?

    Thursday, July 8, 2010 10:05 AM
  • Make sure that  you have added SQL Server account in Lock Page Memory policy....

    Close SSMS and re-open see what is going on


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 8, 2010 10:36 AM
    Answerer
  • Make sure that  you have added SQL Server account in Lock Page Memory policy....

    Close SSMS and re-open see what is going on


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 8, 2010 10:36 AM
    Answerer
  • I do not see any program in SQL Server 2008 called "mmsm.exe" .  There is a "ssms.exe" is the SQL Server Management Studio application.  This is a user tool and would need to be open in order to be using memory.  Closing this application should clear the memory.

     

    However, if you did not set the "max memory" setting in SQL Server, the default is to use all RAM (physical and virtual) until the server starts paging and starts to crawl.

     

    Please see:

    http://support.microsoft.com/kb/321363

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

    Thursday, July 8, 2010 2:15 PM
    Moderator
  • Ah, sorry, typo, it is ssms.exe While im reading the above article...: That is better now but the SQL service is now using 3.1GB of memory... i Checked Boost SQL priority... can that cause it? Is that recommended? It is a dedicated SQL Server Is 4GB enough in a SQL server 2008 r2 box? These db's and many more was running fine on my Win 2003 server with SQL 2005?
    Friday, July 9, 2010 6:15 AM
  • Ok, i read the article. Interesting... So this is normal... And it looks like the memory is all used up but actually it is not? Well that is the way i understand it. And the SQL server priority... should i check that?
    Friday, July 9, 2010 7:27 AM
  • Hi,

    1. What’s your operation system architecture (32-bit or 64bit)? Generally, A 32-bit machine can directly address only 4 GB of memory, and by default, Windows itself reserves the top 2 GB of address space for its own use, which leaves only 2 GB as the maximum size of the VAS (virtual address space) for any application, such as SQL Server. However, the following facts will affect the SQL Server memory to use more than 2GB memory.
    a. If you enable   /3GB flag in the system's Boot.ini file, it allows applications to have a VAS of up to 3 GB.
    b. SQL Server 2008 R2 supports dynamic allocation of AWE mapped memory. Enabling AWE allows SQL Server access to available physical memory in excess of the limits set on their configured virtual memory address space. If you also enable /PAE which is the added ability of the IA32 processor to address more than 4GB of physical memory, SQL Server can access more than 4GB memory.If fewer resources are required, SQL Server can return AWE mapped memory to the operating system for use by other processes or applications. Also, dynamic AWE mapped memory is supported on servers with less physical memory than the configured limit on virtual memory address space.

    On a 64-bit platform, the AWE Enabled configuration option is present, but its setting is ignored. However, the Windows policy Lock Pages in Memory option is available, although it is disabled by default. This policy determines which accounts can make use of a Windows feature to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. It is recommended that you enable this policy on a 62-bit system.

    2. To check the exactly memory used in SQL Server, please refer to the following FAQ:
    http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/8dc892cd-633f-4fa2-a973-b9a0c3430b6d

    3. If you want to limit the memory used by SQL Server, try to set the max server memory option:
    http://msdn.microsoft.com/en-us/library/ms178067.aspx

    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, July 9, 2010 8:45 AM
    Moderator
  • Hi, the system is x64.

    Ok, for now i have checked the Boost sql priority.

    To get the  "Windows policy Lock Pages in Memory option" set: http://msdn.microsoft.com/en-us/library/ms190730.aspx

    To enable the lock pages in memory option

    1. On the Start menu, click Run . In the Open box, type gpedit.msc .

      The Group Policy dialog box opens.

    2. On the Group Policy console, expand Computer Configuration , and then expand Windows Settings .

    3. Expand Security Settings , and then expand Local Policies .

    4. Select the User Rights Assignment folder.

      The policies will be displayed in the details pane.

    5. In the pane, double-click Lock pages in memory .

    6. In the Local Security Policy Setting dialog box, click Add .

    7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

    Any other settings i should consider for the server?

    Should i upgrade the Ram or is it to early to tell?

    Friday, July 9, 2010 9:13 AM
  • Its not generally recommended that you run SQL Server with Boost Priority checked.  If it is SSMS that is using the memory, there isn't anything in SQL Server that you can change to affect that.  SSMS is a separate application, and generally, you wouldn't be running that regularly on the SQL Server itself.  You would generally use SSMS from a workstation (for example your desktop/laptop) and you'd connect to the SQL Server, that way the server isn't wasting memory and CPU resources running the client tools, your desktop is.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, July 9, 2010 10:02 PM
    Moderator