Database Server Memory always showing 98%

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    This whole thing doesn't make sense unless more information is provided, memory configurations and specs are relative to all the work the server has to perform, not exceptions like occasional large batches etc.

    😎

  • rameelster

    Right there with Babe

    Points: 779

    Grant,

    This job is running from front end application and do not provide the provision of splitting the batch as instructed.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Then leave it as-is. There's nothing you've mentioned in this thread that's a problem. As mentioned before, SQL will use all memory that it's allowed to use. This is expected, documented behaviour and it's a good thing.

    Now, you may have it using a little too much and risking starving the OS. You probably want to lower max server memory if it's set to a non-default value. If it's set at default still, you definitely want to change it. See chapter 4 of https://www.red-gate.com/community/books/accidental-dba

    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
  • Grant Fritchey

    SSC Guru

    Points: 396714

    I'm pretty much with Gail. Just because it says it's using 98% of memory doesn't mean there's anything approaching a problem.

    If you do know that the batch is a problem, then you need to address the batch. Flipping memory settings is unlikely to do anything about the batch issues.

    ----------------------------------------------------
    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

  • benjamin.reyes

    SSCertifiable

    Points: 5249

    Grant Fritchey (11/23/2015)


    I'm pretty much with Gail. Just because it says it's using 98% of memory doesn't mean there's anything approaching a problem.

    If you do know that the batch is a problem, then you need to address the batch. Flipping memory settings is unlikely to do anything about the batch issues.

    Unless the setting is allocating 200+ more GB of ram. 😀

  • Gail Shaw

    SSC Guru

    Points: 1004484

    benjamin.reyes (11/23/2015)


    Unless the setting is allocating 200+ more GB of ram. 😀

    Nothing wrong with allocating 200+GB memory to SQL, providing the server has it. I've got a client with a server with 512GB memory in it. Max server memory is definitely over 200GB on that server.

    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
  • Grant Fritchey

    SSC Guru

    Points: 396714

    benjamin.reyes (11/23/2015)


    Grant Fritchey (11/23/2015)


    I'm pretty much with Gail. Just because it says it's using 98% of memory doesn't mean there's anything approaching a problem.

    If you do know that the batch is a problem, then you need to address the batch. Flipping memory settings is unlikely to do anything about the batch issues.

    Unless the setting is allocating 200+ more GB of ram. 😀

    Well, you got me there. That's a cool setting to have.

    ----------------------------------------------------
    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

  • rameelster

    Right there with Babe

    Points: 779

    How about the idea of partitioning the table on which the batch process is running

  • Gail Shaw

    SSC Guru

    Points: 1004484

    To achieve what (other than additional complexity)?

    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

Viewing 9 posts - 16 through 24 (of 24 total)

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