High Memory Usage

• WhiteLotus

  SSChampion

  Points: 12120

  More actions

  February 12, 2017 at 5:31 pm

  #1928026

  GilaMonster - Thursday, February 9, 2017 12:50 AM

  WhiteLotus - Wednesday, February 8, 2017 8:12 PM

  Apparently only 2 or 3 GB would be added

  So do you want my laptop to run your production DB? It's got more memory. Hell, my gaming and development desktop is running 32GB.

  These days, I wouldn't go for less than 32GB for a new production DB server, mostly because memory is so incredibly cheap that there's no reason to skimp.

  hahahha I dont know why it's so hard to add more memory

• richlion2

  SSCrazy

  Points: 2484

  More actions

  February 13, 2017 at 4:52 am

  #1928067

  WhiteLotus - Thursday, February 2, 2017 8:38 PM

  In our production server , memory usage is around 95% even tho it has been capped (8 GB ) . Total memory 12 GB

  When I run a script I got a db let’s say Db 1 which has the highest catched_pages_count (574.198)  

  And I run another script , I got a table let’s say table 1  which has the highest catched_pages_count (514.201)

  I really have no idea what to do with that table

  Should I just lower down the memory setting ?

  cheers

  Everything depends. Some people here say "plan to put your whole database into memory". I would say that depends:
  1) Is it an OLTP database?
  2) Do you run big queries?
  3) Can you optimize your SQL?

  I once had a third party Oracle database. The company claimed that my TEMP tablespace should be 3x size of DB. My DB was 100GB, I need 300Gb in TEMP, what? It turned out this was a case of 1 (I say it again - ONE) SQL that was executed once a month that would expand my TEMP from 10GB to the max allowed on the Win32 server to the size of 32GB. Every tool that I would use to optimize that query would show 3 full scans on massive tables - no optimization at all. Simply because the third party would not bother to listen to my recommendation to "optimize that query".

  I have another SQL Server database. Dusing regular OLTP activity it uses 18GB out of 32GB. Only one user who runs 1-2 ad-hoc queries and gobbles up the rest a few days after a reboot.

  Buying  more memory is the only option if you cannot opimize your query.