none
SQL Server 2008 - High Memory Usage

    Question

  • Hi ,

      We are facing problem with the high memory usage with SQL Server 2008. 64 bit , standard edition sp2 . 8 GB RAM , 8 processors .

    Memory usage keep on growing ..sometimes it will reach to 7.5 GB also .... and its not coming down ..untill we restart the service...

    These are the details of DBCC MEMORYSTATUS :: Please suggest how to overcome this problem

    Memory Manager                           KB
    ---------------------------------------- -----------
    VM Reserved                              8484792
    VM Committed                             4220368
    Locked Pages Allocated                   0
    Reserved Memory                          1024
    Reserved Memory In Use                   0

    Memory node Id = 0                       KB
    ---------------------------------------- -----------
    VM Reserved                              8481144
    VM Committed                             4216832
    Locked Pages Allocated                   0
    MultiPage Allocator                      36464
    SinglePage Allocator                     2687456

    Memory node Id = 64                      KB
    ---------------------------------------- -----------
    VM Reserved                              2560
    VM Committed                             2504
    Locked Pages Allocated                   0
    MultiPage Allocator                      2416
    SinglePage Allocator                     2687456

    MEMORYCLERK_SQLGENERAL (node 0)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     3520
    MultiPage Allocator                      3096

    MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB
    ---------------------------------------- -----------
    VM Reserved                              8413184
    VM Committed                             4150528
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      400

    MEMORYCLERK_SQLQUERYEXEC (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     128
    MultiPage Allocator                      0

    MEMORYCLERK_SQLSTORENG (node 0)          KB
    ---------------------------------------- -----------
    VM Reserved                              3520
    VM Committed                             3520
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     2592
    MultiPage Allocator                      4416

    MEMORYCLERK_SQLCONNECTIONPOOL (node 0)   KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     544
    MultiPage Allocator                      0


    MEMORYCLERK_XE_BUFFER (node 0)           KB
    ---------------------------------------- -----------
    VM Reserved                              4224
    VM Committed                             4224
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      0

    MEMORYCLERK_SOSNODE (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     7480
    MultiPage Allocator                      13384

    MEMORYCLERK_SOSNODE (node 64)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      2336

    MEMORYCLERK_SOSNODE (Total)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     7488
    MultiPage Allocator                      15720

    MEMORYCLERK_FULLTEXT_SHMEM (node 0)      KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              192
    SM Committed                             192
    SinglePage Allocator                     0
    MultiPage Allocator                      0


    MEMORYCLERK_XE (node 0)                  KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     88
    MultiPage Allocator                      392

    CACHESTORE_OBJCP (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     97800
    MultiPage Allocator                      3920

    CACHESTORE_SQLCP (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     2531360
    MultiPage Allocator                      7912

    CACHESTORE_PHDR (node 0)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8480
    MultiPage Allocator                      16

    CACHESTORE_XPROC (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     120
    MultiPage Allocator                      0

    CACHESTORE_TEMPTABLES (node 0)           KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     384
    MultiPage Allocator                      0


    USERSTORE_SCHEMAMGR (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     6080
    MultiPage Allocator                      152

    USERSTORE_DBMETADATA (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     6264
    MultiPage Allocator                      0

    OBJECTSTORE_LOCK_MANAGER (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              16384
    VM Committed                             16384
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16336
    MultiPage Allocator                      0

    OBJECTSTORE_LOCK_MANAGER (Total)         KB
    ---------------------------------------- -----------
    VM Reserved                              16384
    VM Committed                             16384
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16360
    MultiPage Allocator                      0

    Buffer Pool                              Value
    ---------------------------------------- -----------
    Committed                                509696
    Target                                   806589
    Database                                 160915
    Dirty                                    5734
    In IO                                    0
    Latched                                  0
    Free                                     12849
    Stolen                                   335932
    Reserved                                 0
    Visible                                  806589
    Stolen Potential                         659008
    Limiting Factor                          13
    Last OOM Factor                          0
    Page Life Expectancy                     75735

    Process/System Counts                    Value
    ---------------------------------------- --------------------
    Available Physical Memory                2912673792
    Available Virtual Memory                 8787090063360
    Available Paging File                    11828006912
    Working Set                              4389765120
    Percent of Committed Memory in WS        100
    Page Faults                              5448707
    System physical memory high              1
    System physical memory low               0
    Process physical memory low              0
    Process virtual memory low               0

    Procedure Cache                          Value
    ---------------------------------------- -----------
    TotalProcs                               15595
    TotalPages                               331201
    InUsePages                               11081

    Global Memory Objects                    Pages
    ---------------------------------------- -----------
    Resource                                 376
    Locks                                    2045
    XDES                                     49
    SETLS                                    8
    SE Dataset Allocators                    16
    SubpDesc Allocators                      8
    SE SchemaManager                         364
    SE Column Metadata Cache                 414
    SQLCache                                 2640
    Replication                              2
    ServerGlobal                             50
    XP Global                                2
    SortTables                               35

    Query Memory Objects (internal)          Value
    ---------------------------------------- -----------
    Grants                                   0
    Waiting                                  0
    Available                                390625
    Current Max                              390625
    Future Max                               390625
    Physical Max                             593320
    Next Request                             0
    Waiting For                              0
    Cost                                     0
    Timeout                                  0
    Wait Time                                0

    Small Query Memory Objects (internal)    Value
    ---------------------------------------- -----------
    Grants                                   0
    Waiting                                  0
    Available                                12800
    Current Max                              12800
    Future Max                               12800

    Optimization Queue (internal)            Value
    ---------------------------------------- --------------------
    Overall Memory                           5296381952
    Target Memory                            2325504000
    Last Notification                        1
    Timeout                                  6
    Early Termination Factor                 5

    Small Gateway (internal)                 Value
    ---------------------------------------- -----------
    Configured Units                         32
    Available Units                          32
    Acquires                                 0
    Waiters                                  0
    Threshold Factor                         380000
    Threshold                                380000

    Memory Pool Manager                      Pages
    ---------------------------------------- -----------
    Reserved Current                         0
    Reserved Limit                           430327

    Memory Pool (internal)                   Pages
    ---------------------------------------- -----------
    Allocations                              335932
    Predicted                                484267
    Private Target                           0
    Private Limit                            0
    Total Target                             766259
    Total Limit                              766259
    OOM Count                                0

    MEMORYBROKER_FOR_CACHE (internal)        Pages
    ---------------------------------------- -----------
    Allocations                              334051
    Rate                                     3
    Target Allocations                       616046
    Future Allocations                       0
    Overall                                  646531
    Last Notification                        1

    MEMORYBROKER_FOR_STEAL (internal)        Pages
    ---------------------------------------- -----------
    Allocations                              1881
    Rate                                     2
    Target Allocations                       283875
    Future Allocations                       0
    Overall                                  646531
    Last Notification                        1

    MEMORYBROKER_FOR_RESERVE (internal)      Pages
    ---------------------------------------- -----------
    Allocations                              0
    Rate                                     0
    Target Allocations                       430322
    Future Allocations                       148330
    Overall                                  646531
    Last Notification                        1


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Tuesday, October 12, 2010 4:11 AM

All replies

  • Hi ,

      We are facing problem with the high memory usage with SQL Server 2008. 64 bit , standard edition sp2 . 8 GB RAM , 8 processors .

    Memory usage keep on growing ..sometimes it will reach to 7.5 GB also .... and its not coming down ..untill we restart the service...


     

    SQL is designed to keep data in memory as much as possible to avoid disk IO.
    Limit the memory by max server memory setting.


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Tuesday, October 12, 2010 4:31 AM
  • Hi Balmukund,

        Thanks for the reply ..

    1) To limit the MAX memory option , what is the criteria to decide the MAX memory benchmark ..is there any systematic way to decide the MAX value ....

    2) If I limit the MAX memory option, then, Id SQL Server really requires more than what we have set , then will there be any problems?

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Tuesday, October 12, 2010 4:44 AM
  • Hi Balaji,

    There is no set criteria for specifying MAX SERVER MEMORY of SQL. It depends on series of testing like what type of queries are running, what is workload on the server etc. etc.

    We need to test our environment and see how system works if everything is good that means that setting is fine else SQL will himself start complaining in the form of Errors messages (which it will log in SQL Error Logs).

    But still as a general rule of thumb we say that leave atleast 10-15% of Physical Memory for the OS. In your case its x64 bit with 8 GB memory so I can suggest to set 6 GB as MAX SERVER MEMORY for SQL and leave 2 GB for the Operating System.

    With regard to memory, I must say first set a specific limit for SQL and then monitor it and see if there are any memory related errors that are coming like

       Fail to reserve contigous memory of 65536

       There is insufficient memory to run this query etc. etc.

    HTH


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++++++++++++++++++++++++++++++++++ Please mark "Propose As Answer" if my answer helped +++++++++++++++++++
    Tuesday, October 12, 2010 4:51 AM
  • Hi all,

      Thanks for the replies .. Can we get any information out of the MEMORYSTATUS info that I posted before (in the question) .. Any information or root cause of this memory problem ...

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Tuesday, October 12, 2010 4:59 AM
  • "Root cause of memory problem?"

    I don't see that as "Problem". Is there anything which is running slow due to this? As I said earlier, its by design.

    About the optimal value, testing is the key. Thumb rule value is already given by Gursethi
    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Tuesday, October 12, 2010 6:18 AM
  • Procedure Cache                          Value
    ---------------------------------------- -----------
    TotalProcs                               15595
    TotalPages                               331201
    InUsePages                               11081

    There are 15,595plans in cache, and there are Free 12849 pages (103Mbytes) free for the data cache.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 12, 2010 6:25 AM
  • Hi Uri,

       So, that means ... based on your comments , what is the next course of action that we can take to restrict the memory growth .. As suggested before , can we change the MAX memory option to say 5 GB (since it is 8 GB RAM) ..Please suggest ...

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Tuesday, October 12, 2010 7:07 AM
  • Hi Bala,

    As I mentioned leave atleast 10-15%  of memory for OS and rest you can use for SQL. In your situation 6 GB for SQL and 2 GB for OS should be fine as a starting point.

    HTH


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++++++++++++++++++++++++++++++++++ Please mark "Propose As Answer" if my answer helped +++++++++++++++++++
    Tuesday, October 12, 2010 8:02 AM
  • We were experiencing something similiar to what you have observed. 64bit Server with 8Gig of memory almost all of which appeared to be used by SQL Server even though there was only one user on the application!

    Being new to Sql Server I thought that it was normal, turns out there was something physically wrong with the memory. Unfortunately I can't say more because it was handled by the Windows Server Administrator and the Engineer from the Server manufacturer.

    There may have been some error messages appear when starting the server from being completely shut down which lead the engineer to believe that there was a problem with the memory.

    Perhaps something to investigate.

    Tuesday, October 12, 2010 2:20 PM