high memory consumption sql server.

• Sharon Kumar

  Ten Centuries

  Points: 1193

  More actions

  September 18, 2010 at 4:26 pm

  #226579

  one of my production sql server memory is high consumption usage,

  ram upto is 36 gb, we setup min & max memory upto 30 gb,

  But here in the situation sql server is using more than 30gb of memory,

  Can i know the reason, why its using alot memory, is there any possible options to check for the reason,

  please provide me trouble shooting steps,

  please help me out on this issue..

  thanks

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 20, 2010 at 4:56 am

  #1223921

  Sharon Kumar (9/18/2010)

  ---

  please provide me trouble shooting steps,

  please help me out on this issue..

  thanks

  we can spend years on this topic. So refer this article it will give you some high level idea http://technet.microsoft.com/en-us/library/cc966540.aspx

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 20, 2010 at 11:57 am

  #1224257

  1) do not set max and min memory to same value.

  2) 6GB likely isn't enough free for a 36GB box

  3) max memory is only limiting BUFFER POOL memory. There are MANY other buckets of memory not limited by that setting that get allocated outside of the buffer pool.

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

• Gail Wanabee

  SSCrazy Eights

  Points: 8471

  More actions

  September 21, 2010 at 4:00 pm

  #1225279

  There may be something I don't know about this subject but I'm going to add my comments anyway.

  Over the years, it has been my experience working on several different SQL Server database servers that monitoring and keeping the Memory:Available MBytes metric (in Performance Monitor) near 2 GB has worked well for servers with 16 GB or more of RAM. Windows page file usage on these servers rarely exceeded 12% at its peak and tended to generally remain below 5%. The range of RAM on the servers I've worked on varied from 16 GB to 72 GB. The servers performed well in high speed, real-time, data acquisition and reporting environments.

  I've also used servers with less RAM, as low as 2 GB. I've always tried to maintain a minimum of 500 KB of Memory:Available MBytes on the ones with the smallest amounts of RAM, and appropriately more free memory for those servers with larger amounts of memory.

  I experimented with a 2 GB server and made the unfortunate mistake of setting SQL Server's memory settings such that Memory:Available MBytes one day went to zero. The server effectively locked up as Windows and SQL Server competed in a no-free-memory condition. That was a painful lesson. I had to reboot the server to fix the problem.

  Hope this post helps.

  Sincerely,

  LC