Database Server Memory always showing 98%

  • rameelster

    Right there with Babe

    Points: 779

    Hi,

    Production DB server is showing 98 percent always.How to bring it down.

    http://s392.photobucket.com/user/Twothousand_BTHIS IS CLEARLY SPAMdia/Capture_zps1khl0ra8.jpg.html

  • spaghettidba

    SSC Guru

    Points: 105673

    SQL Server tries to use all the available RAM by default. This is expected and desirable in a dedicated SQL Server box.

    If you have other applications running on the same server, you may want to lower the "MAX Server Memory" setting on the instance.

  • rameelster

    Right there with Babe

    Points: 779

    DB server consists of Inter Xeon processor(2 Processors) with 40 cores and a memory size of 64 GB. I have allocated 61 GB for sql server and the rest for windows server 2008. There is no other application running on the server other than sql server 2008

    http://s392.photobucket.com/user/Twothousand_BTHIS IS CLEARLY SPAMdia/Capture1_zpsgr558n6a.jpg.html

  • spaghettidba

    SSC Guru

    Points: 105673

    OK, so what's the problem?

  • rameelster

    Right there with Babe

    Points: 779

    Since I have allocated 98 percent of memory for Sql server.Could it be the reason I am getting page_io_Latches

    frequently

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    rameelster (11/20/2015)


    DB server consists of Inter Xeon processor(2 Processors) with 40 cores and a memory size of 64 GB. I have allocated 61 GB for sql server and the rest for windows server 2008. There is no other application running on the server other than sql server 2008

    Quick thought, in my experience 3Gb for the OS is too little even if nothing else is running on the server other than SQL Server. For an OLTP database server with these specs I would start with 20% for the OS and 80% for the SQL, then monitor and adjust if necessary.

    😎

  • rameelster

    Right there with Babe

    Points: 779

    but the database instance contain 2 databases each with size 240 GB and 714 GB respectively. Dont you think that 80 percent memory is very less.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    rameelster (11/20/2015)


    but the database instance contain 2 databases each with size 240 GB and 714 GB respectively. Dont you think that 80 percent memory is very less.

    No I don't think so and as I said previously, monitor the server and adjust if necessary. Two things to take into consideration, firstly if the OS is pressured for memory i.e. paging/swapping memory constantly then the SQL Server is going to feel that pressure as the OS will be reclaiming memory back from it. Secondly the sizes of the databases are almost irrelevant here, more useful information would be number of concurrent users/connections, transactions p. second, type and volume of activity etc.

    😎

  • Grant Fritchey

    SSC Guru

    Points: 396714

    rameelster (11/20/2015)


    Since I have allocated 98 percent of memory for Sql server.Could it be the reason I am getting page_io_Latches

    frequently

    IO Latches are about reading and writing to disk. It's possible that it's related to memory, but it could just simply be a disk issue. Don't just focus on the waits, understand why you're getting the waits, which processes are specifically getting the waits. That will help you understand what the root cause is.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Gail Shaw

    SSC Guru

    Points: 1004484

    rameelster (11/20/2015)


    Production DB server is showing 98 percent always.How to bring it down

    Why? What good is free memory that's not being used?

    Surely you want all the resources in the server actually being used?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • benjamin.reyes

    SSCertifiable

    Points: 5249

    rameelster (11/20/2015)


    but the database instance contain 2 databases each with size 240 GB and 714 GB respectively. Dont you think that 80 percent memory is very less.

    How much data are the problem queries moving?

  • rameelster

    Right there with Babe

    Points: 779

    Memory conjuction is happening when a job which was set to run daily to groom data older than 40 days .

  • rameelster

    Right there with Babe

    Points: 779

    Daily 2447639 records are getting groomed to an archive table located in the same database.

  • rameelster

    Right there with Babe

    Points: 779

    Memory conjuction is happening when a job which was set to run daily to groom data older than 40 days .

    Daily 2447639 records are getting groomed to an archive table located in the same database.

  • Grant Fritchey

    SSC Guru

    Points: 396714

    So it's a giant batch process. You could try breaking up the batch into smaller pieces. It might run longer overall that way, but it'll use fewer resources while it's running. Make sure the query is tuned.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply