SQL SERVER MEMORY CONSUMPTION PROBLEM

  • Hi All

    On one of my monitoring servers ( SQL SERVER 2000) , I am using 3.5GB RAM . Lot of Memmory consumption issues are comming on this server.

    Can anyone Please guide me what steps do i need to follow to recify the same. Under SQL SERVER memory settings i have checked default configuration of Dynamic Memory is

    0MB - MIN

    3583MB - MAXIMUM

    PLEASE SUGGEST

  • What do you mean by 'memory consumption issues'?

    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
  • By memory consumption i mean every time i am getting alerts that SQL SERVER IS CONSUMING 100% OS MEMORY..Very frequently i am getting such alerts.

  • You may want to set the max memory lower. Maybe 3 GB. SQL's been told that it may use up to 3.5 GB and it will do that, even if it means starving the OS.

    Just a check, 32 bit, 64 bit? Is /3GB enabled? Is AWE enabled?

    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
  • If you aren't using Enterprise Edition, set sql server max memory to 2GB and see how that goes. If still get alerts, go with 1.7GB. If using EE, start with 2.5 and go down to 2 if problems. Check for paging using perf mon. If getting that during steady state ops, keep backing down until it goes away. Note that you may need to bounce sql server to get it to release memory.

    What else is running on this server?

    Like Gail said, if 32 bit (and EE) you will need /PAE and AWE enabled to use more than 2GB.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 5 posts - 1 through 4 (of 4 total)

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