locked
Least accessing instances show as high memory utilization in my resource monitor RRS feed

  • Question

  • Hi All,

    Currently 5 SQLServer instances are running on one of my clustered node and least accessing  instances show as high memory utilization in my resource monitor. Can someone advice the reason behind this and way to reduce that. i am using SQLServer 2008 R2

    Allocated memory for database instance: 6144 MB

    Target Server Memory(KB) 6,291,456.00

    total Server Memory(KB)  2,691,632.00

    Image                   PID         Hard Faults/sec                 Commit (KB)      Working Set (KB)              Shareable (KB)                  Private (KB)

    sqlservr.exe       8104       0                                              3,333,880             3,068,244                             47,656                                   3,020,588

     

    Thanks,

    robb

    Monday, April 8, 2013 10:03 AM

Answers

  • Hi Robb,

    I want to inform that SQL Server will take up more memory as the Database Engine workload increases until buffer pool takes up as much memory as specified in “max server memory”, this design will improve SQL Server performance. Since there are 5 SQL Server instances are running on one of my clustered node, I think set “min server memory” to 6GB is acceptable. For more detail information, please refer to the following link:

    Effects of min and max server memory
    http://msdn.microsoft.com/en-us/library/ms180797.aspx


    Allen Li
    TechNet Community Support

    • Marked as answer by Robb123 Monday, April 15, 2013 9:20 AM
    Tuesday, April 9, 2013 2:53 AM

All replies

  • That means   you have 6GB RAM installed on the server, but have  you set up MAX memory param? BTW is that 64 or 32 bit server/sql server?

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, April 8, 2013 10:44 AM
    Answerer
  • When executed following three commands will free up memory for SQL Server by cleaning up its cache.
    DBCC FREESYSTEMCACHE
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE 

    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, April 8, 2013 10:51 AM
  • When executed following three commands will free up memory for SQL Server by cleaning up its cache.
    DBCC FREESYSTEMCACHE
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE 

    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    DBA Dont mind please,thats an trial method & an temporary solution.

    @robb can you please verify which instance is that & what is SP_configure set & its an 32 or 64bit?


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Monday, April 8, 2013 11:07 AM
  • Hi 

    Please refer below link for memory settings.

    ref: http://msdn.microsoft.com/en-in/library/ms178067.aspx

    As well as check the missing indexes , it also impact on sql server performance consumes max memory .


    Monday, April 8, 2013 11:28 AM
  • Do you advise running the above command on Production Server?????

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, April 8, 2013 12:25 PM
    Answerer
  • Do you advise running the above command on Production Server?????

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    No we shouldn't usually & not recommending so, but can try in an worst case by knowing the each dbcc

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Monday, April 8, 2013 3:34 PM
  • That is completely normal and expected behavior if you do not configure "Max Server Memory".

    Please see:

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

    Monday, April 8, 2013 4:09 PM
  • Hi Rama,Thanks a lot for your immediate feedback. my databases are running on 64 bit. also it's 3 node cluster and each node have 48 GB RAM and i have set the max memory for this instance as 6GB. is that OK to execute above mentioned  3 commands while database running? will that through any timeouts?

    Thanks,

    Robb

    Tuesday, April 9, 2013 2:01 AM
  • Hi Robb,

    I want to inform that SQL Server will take up more memory as the Database Engine workload increases until buffer pool takes up as much memory as specified in “max server memory”, this design will improve SQL Server performance. Since there are 5 SQL Server instances are running on one of my clustered node, I think set “min server memory” to 6GB is acceptable. For more detail information, please refer to the following link:

    Effects of min and max server memory
    http://msdn.microsoft.com/en-us/library/ms180797.aspx


    Allen Li
    TechNet Community Support

    • Marked as answer by Robb123 Monday, April 15, 2013 9:20 AM
    Tuesday, April 9, 2013 2:53 AM