No announcement yet.

recommend sql memory size

  • Filter
  • Time
  • Show
Clear All
new posts

  • recommend sql memory size

    We have an 8Gb SBS 2008 (fresh) install with about 15 users and 40 PCs.

    Had the common runaway memory consumption by sqlservr.exe that appears to be used mostly for WSUS. I had it at a 2gb limit (actually there are two instances running one 64 bit and one 32 bit) but between the two just way to much memory usage and with everything else the system is a 95% about a day after reboot.

    I've found many sites on how to cap the usage and the example they have is this recommend for a system my size or will there so much swap that it will kill the process which is already getting a 60-80% utilization all day (quad core dell 2850 3.0ghz)

    I've got 4gb more on order for a total of 12gb which is the limit of our budget allthough I think this hardware will allow 24gb.

  • #2
    Re: recommend sql memory size

    Run some Perfmon measurements to see if you actually are short of RAM. You see, RAM not allocated is usually RAM wasted. It is far better to have it as buffer cache instead of having all those semiconductors being idle. Mind you, HD access is terribly slow when compared against RAM.

    Look at page fault rate. It tells you how often the OS has to read swap file for paged memory. Sql Server buffer cache hit rate tells you how often the SS is able to get the answer directly from RAM instead of going to the disk. Disk queue tells you how many disk transactions there are waiting for IO to happen.

    512 kb memory limit does not make any sense for Sql Server. If you must try and experiment, set the limit to 1 or 2 gigabytes per instance.



    • #3
      Re: recommend sql memory size

      great info, exactly what I was hoping for!

      I'm a bit weak on Permon but I'll figure it out for sure.

      I do understand you comment on memory wasted but was unsure if when swapping occurs it also hits the processor as well as the drives, I guess the permon will help figure that out.

      Thanks again and once that memory arrives we'll get the sqlservr memory up to spec!

      I found it a bit curious that even knowing i set the limit of 512 the 64 bit is still using 590K and the 32 bit is using 600K!


      • #4
        Re: recommend sql memory size

        Again I'm weak at getting the best data out of perfmon but am getting the following data:

        MSSQ$ Buffet Manager Buffer Cache Hit Ratio: 99.851%
        Memory Page Faults/Sec cyles betwenb 20-7000 every few secs
        Logical Disk Av disk Write Queue Length low with spikes of 40-60 every few secs

        not sure what this data all means (no reference point) or even I got the right data points...perfmon is robust!


        • #5
          Re: recommend sql memory size

          Originally posted by carboncow View Post
          not sure what this data all means (no reference point) or even I got the right data points
          Buffer cache hit ratio means that Sql Server can answer a lot of queries without having to load data from disk. The closer to 100% the ratio is the better, so you are likely doing good.

          Page fault means that a process is executing and some part of process' memory has been swapped to the hard disk. The higher the page fault rate, the more often OS has to load stuff from hard disk. This usually means you could use more RAM, or you could reduce Sql Server memory. The trade-off is likely a decrease in SS buffer hit ratio, but by running perfmon you easily can see when cache hit ratio takes too hard an hit.

          Average disk write queue length is not such an interesting counter. Look for instant values instead. That is, current disk read queue and current disk write queue. How to make any sense of the disk queue values depends on your hardware. Basically, divide the value by number of spindles your server got. If queue/spindle ratio > 2, you might have disk IO as bottleneck. If disk is a problem, the usual tell-tale sign is that CPU load stays low, then it bursts up and goes down again as disks are too busy.



          • #6
            Re: recommend sql memory size

            Thanks again, this is very useful and spot on information. Sounds like I'm in good shape with the perfmon output I'm getting.

            I appreciate your clear, concise and non-judgmental response to non-expert inquiries.

            EDIT: I'm now watching the page fault and see it go between 9-70 and then spike to is 1000+ page faults per sec high?!?!? (see screenshot)

            Last edited by carboncow; 1st October 2010, 20:18.


            • #7
              Re: recommend sql memory size

              see thread for additional screenshot added.


              • #8
                Re: recommend sql memory size

                Take a look at a blog post for more explaining about page faults. And pick up an OS theory book too; Stalling's Opertaing Systems is a good read (and likely a course book in about any CS curriculum too.)

                Anyway, do not focus too much on single a counter. Try and see the big picture. Data should be gathered for hours to see trends. Most of the cases, a 5 - 15 sec sample rate is good enough. In most corporate networks, it is easy to see the morning peak (=people coming to work, logging on), lunch break and backup peak during non-business hours and whatnot.

                A car analogy might work here: your car engine is running at 3500 RPM. What good will such an value tell you, if anything? Does it mean you are moving fast? Are you getting a good MPG? Should you shift up or down? To answer the questions you need to know more. Like, are you running a diesel or petrol engine? What gear is the car running on? Are you speeding up or slowing down? Towing a trailer?

                Likewise is the situation with page faults. They will happen as long as you have less RAM than virtual memory. Are they a problem? It depends. To make more sense, you need to combine PF rate, disk IO, network IO, CPU load and likely a bunch of other counters too. The relevant counters depend on your setup.

                A rule of thumb: page fault rate is high, disk IO is high and network & CPU loads are low, your CPU is waiting for the OS to read stuff from the disk. As a consequence, there is little network traffic, as server applications are waiting for the CPU. Add up some RAM and CPU load should increase, as swapping is not needed so often.