Dynamic VS. Fixed Memory...

  • I'm looking for a good debate on why not to go with a fixed memory setting...my background is from Sybase and Oracle, which both use a fixed memory size.

    We have a dedicated SQL server box with 4 GB of ram. The only thing running on the box is SQL Server.

    I would like to run with a fixed memory size of 3.5 GB, leaving 500 MB for OS, terminal services, etc.

    Seems like all the books recomend letting SQL Server manage the memory dynamically.

    I would think that a fixed memory size would take away all the overhead of dynamically allocating/deallocating memeory.

    Thoughts, ideas, experiences...

    Thanks in advance.

    - Brendan

  • Not sure how much overhead there is to letting SQL manage the memory. I'll look to see what I can find. As far as your proposed solution SQL will still only use a max of 3G, even if you're using the /3GB switch in the boot.ini. For SQL to use more you'll have to add the /PAE switch AND enable awe memory - this will only work if you have more than 4G in the machine (and you do have to define a fixed amount in this mode).


  • Not much experience here. I've usually tried to go overboard with memory to handle the loads. I'd doubt that there is much overhead, but there is definitely the possibility for bugs. Hopefully this is a well reviewed area.

    Steve Jones


  • I've got 3 Servers which are win2k adv, sql2k ent and fixed mem seem to act different on all 3. I still cant work this one out, can anyone shed some light?



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

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