none
Why SQL SERVER 2008 takes all free Memory and utilize 100 percent

    Question

  • I am using SQL SERVER 2008 R2 and it is running fine. On One of the client machine within few hours it occupies all the physical memory of the server (which is 8GB) and every thing slows down. I am unable to find why it happens.

     

    Database setting 

    Simple,AutoShrink,autoCommit.


    Shamas Saeed (if Post helpful please mark as Answer) SQL SERVER 200/2005/2008/R2 http://sqlservercoollinks.blogspot.com
    Friday, April 22, 2011 7:20 AM

Answers

  • If it is 64 bit machine, set max memory option on SQL Server. Also, enabling autoshrink is one of the worst things you can do for your database, I would strongly recommend NOT using it.

     

    MC

    Friday, April 22, 2011 8:26 AM
  • I am using SQL SERVER 2008 R2 and it is running fine. On One of the client machine within few hours it occupies all the physical memory of the server (which is 8GB) and every thing slows down. I am unable to find why it happens.

     

    Database setting 

    Simple,AutoShrink,autoCommit.


    Shamas Saeed (if Post helpful please mark as Answer) SQL SERVER 200/2005/2008/R2 http://sqlservercoollinks.blogspot.com


    By default SQL Server is suppose to take as much memory as required and its not going release it unless there is a situation where there is s memory pressure and OS forcibly takes some chunk from SQL reserved memory (not getting too deep in to details ) . You should also check if SQL Service account is added in the Lock pages in memory becasue is this is done SQL Server's working set will not be removed from the RAM and will surely casue other processes to slow down ....

    Further if you find that target server memory as well as total server memory are same in perfmon (under <instance:Memory manager>) and see if both the counters are same and if it is nearing towards 8 GB or whatever is showing in task manager .

    Its good to set the max server memory of SQL Server instance but also keep noticing that it does not affect SQL Server performance .check SQL Server error logs and see if you find any memory related entries there ...

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, April 22, 2011 11:40 AM
  • Thank Marko, I have changed autoshrink and database option from Simple to Full and it is working fine now.
    Shamas Saeed (if Post helpful please mark as Answer) SQL SERVER 200/2005/2008/R2 http://sqlservercoollinks.blogspot.com
    • Marked as answer by Shamas Saeed Tuesday, September 06, 2011 5:49 AM
    Tuesday, September 06, 2011 5:49 AM

All replies

  • Monitor the Total Server memory (KB) windows performance counter. If it grows beyond Target Server memory (KB), there is a issue

    Also check the Working set of sql server process. If working set is less than total server memory, trimming of memory is taking place. Check for the reasons

    Friday, April 22, 2011 7:30 AM
  • If it is 64 bit machine, set max memory option on SQL Server. Also, enabling autoshrink is one of the worst things you can do for your database, I would strongly recommend NOT using it.

     

    MC

    Friday, April 22, 2011 8:26 AM
  • The following may prove helpful in finding the reason for the 100% spin:

    1. Activity Monitor

    2. Server Standard Reports

    3. Database Standard Reports

    4. DMV queries: Optimizing SQL Server CPU Performance

    5. SQL Profiler - SQL Server Performance Statistics Using a Server Side Trace



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    • Edited by Kalman Toth Sunday, September 30, 2012 7:50 PM
    Friday, April 22, 2011 11:14 AM
  • I am using SQL SERVER 2008 R2 and it is running fine. On One of the client machine within few hours it occupies all the physical memory of the server (which is 8GB) and every thing slows down. I am unable to find why it happens.

     

    Database setting 

    Simple,AutoShrink,autoCommit.


    Shamas Saeed (if Post helpful please mark as Answer) SQL SERVER 200/2005/2008/R2 http://sqlservercoollinks.blogspot.com


    By default SQL Server is suppose to take as much memory as required and its not going release it unless there is a situation where there is s memory pressure and OS forcibly takes some chunk from SQL reserved memory (not getting too deep in to details ) . You should also check if SQL Service account is added in the Lock pages in memory becasue is this is done SQL Server's working set will not be removed from the RAM and will surely casue other processes to slow down ....

    Further if you find that target server memory as well as total server memory are same in perfmon (under <instance:Memory manager>) and see if both the counters are same and if it is nearing towards 8 GB or whatever is showing in task manager .

    Its good to set the max server memory of SQL Server instance but also keep noticing that it does not affect SQL Server performance .check SQL Server error logs and see if you find any memory related entries there ...

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, April 22, 2011 11:40 AM
  • Thank Marko, I have changed autoshrink and database option from Simple to Full and it is working fine now.
    Shamas Saeed (if Post helpful please mark as Answer) SQL SERVER 200/2005/2008/R2 http://sqlservercoollinks.blogspot.com
    • Marked as answer by Shamas Saeed Tuesday, September 06, 2011 5:49 AM
    Tuesday, September 06, 2011 5:49 AM