none
SQL min 24000MB max 31000MB config running value, but only 5GB being used

    Question

  • Though running value max servermemory is 31GB, minum 24GB, but shows only 5GB being used.  does it not allocate all minimum memory first?

    Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory: dsmcsvc.exe (3540) consumed 134330114048 bytes, sqlservr.exe (1932) consumed 828960768 bytes, and ReportingServicesService.exe (2236) consumed 192974848 bytes.

    SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 172.27.1.127]

    Thursday, March 01, 2012 3:15 PM

Answers

All replies

  • How much RAM does the server have? Why did you set MIN 24 GB???? What does the below return?

    --sql server uses the memory
    select 
    CONVERT(VARCHAR,CAST(bpool_committed *8  AS MONEY),1)AS [SIZE],
    bpool_committed,bpool_commit_target 
      from 
    sys.dm_os_sys_info


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, March 01, 2012 3:19 PM
  • Task Manager does not show all RAM associated with a program.

    Please see the FAQ: http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/8dc892cd-633f-4fa2-a973-b9a0c3430b6d

    You will need to reduce the "max memory" setting until the server stops page swapping.  Also, there is no reason to set the "min memory".  I would recommend setting it back to 1024.

    Thursday, March 01, 2012 3:23 PM
  • it returns 3GB size.
    Thursday, March 01, 2012 3:27 PM
  • So sql server is using 3GB, how much RAM does the server have?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, March 01, 2012 3:29 PM
  • sorry didn't answer first time, it has 32GB totally, will plan 2nd instance
    if I give min server memory 5GB for 2nd instance, will it require lower down max server memory for the default instance?
    • Edited by George Zhou Thursday, March 01, 2012 3:38 PM
    Thursday, March 01, 2012 3:36 PM
  • Start with  MAX memory param to 12GB  (may increase to 16GB) for each instance.... Do not forget to grant Locked Pages in memory to an account that sql server runs under..

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, March 01, 2012 3:49 PM
  • thanks, we need more on the default instance, the 2nd instance just for one app.

    Also we are in x64 bit, looks like doesn't need lock pages in memory, does it?

    Thursday, March 01, 2012 4:33 PM
  • Hi George Zhou,
    >> we are in x64 bit, looks like doesn't need lock pages in memory, does it?

    SQL Server will use as much memory as needed and will NOT release that back to OS/other programs unless they request them. This causes memory starvation and may result in poor performance. You want to avoid this situation and the best practice would be to leave 1-2 GB to OS and some extra memory if there are any other programs running on the box.

    So as you mentioned, the total memory is 32GB, if there is any other applications running you can leave 1-2 GB to OS and then 30GB for SQL Server. But it really depends on your situation. Physical memory distribution depends on system load. Just keep in mind that SQL Server will allocate memory on demand meaning server will only allocate memory if load present.
    For more information, please refer to Importance of setting Max Server Memory in SQL Server and How to Set it

    The lock page in memory is an optional in 64-bit SQL Server.  Enabling Locked Pages may have a negative performance impact on your system. You should carefully consider any potential effects before you use Locked Pages.
    For more information about  assign the Lock Pages in Memory privilege , please refer to the articles as below:
    1. http://blogs.msdn.com/b/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx
    2. Lock Pages in Memory ... do you really need it? http://blogs.technet.com/b/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx
    3. Server Memory Options http://msdn.microsoft.com/en-us/library/ms178067.aspx


    Regards, Amber zhang

    Thursday, March 08, 2012 7:04 AM