Database server running out of memory

  • We have a SQL server 2005 machine that has 3 SQL instances for our DEV/STG/UAT environments. I know ;). Anyway, the actual machine has 4GB of memory, which 3GB are allocated for SQL server.

    At least twice a day, one of the instance starts gobbling up the memory and the box comes down to a crawl because it starts using the hard drive as RAM.

    1. Is there any way to limit the max amount of memory that each instance can use?

    2. Any other tips to setup the servers properly so that happens less often?

    I apologize in advance for the simplicity of the questions. I have been made the "de facto" DBA for the past few months since no one else is interested and even though I understand T-SQL quite well, I have a very long way to go on the Admin side.

    Thank you for your help.

    "Any fool can write code that a computer can understand. Good programmers write
    code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999

  • Run sp_configure or right-click on an instance in Management Studio and display the server properties. You can set min and max memory per instance.

  • If you are running a 64bit OS, you may want to consider adding some additional memory.

  • 3 Instances can be a lot, and you definitely want to limit memory. It might not be linearly, so not necessarily 1GB per instance, but you do need to leave some for the OS.

    The amount each instance needs depends on the load. you'll have to do some testing and maybe adjust the amount of memory (use advice above) across the instances.

    The other thing is that you might think about consolidating some of the databases together and removing an instance or two. Is there a reason you have 3 instances?

  • Thanks a lot for the advice. I will run the sp_configure and split the amount of memory so that it adds up to 3GB. Our UAT server will probably get a little more because it is client facing.

    Steve,

    The reason why we have 3 instances on one server is because we are a fairly small shop with a tight budget. We have our database servers on their own but our dev/stg/uat environments are clustered together for both our 2000/2005 instances.

    Even though I am pretty much the only guys keeping those servers alive, I have been asking for help on the admin side since I joined the company and the only reply I get is: "There is no need for it." We have about 20 developers in hours and only a couple of them (me included) are really interested in SQL development / database administration / establishing SQL standards / promoting tighter security...

    Anyway, thank you again for the post. Very much appreciated.

    "Any fool can write code that a computer can understand. Good programmers write
    code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999

  • Is it a BIG Deal to get more RAM. This can solve lot of your Problems, including the ones that will arise by limiting the RAM to Per instance.

    how many Databases you have per instance? and How many users?

  • More memory will probably only help if it is a 64bit OS. Much of the overhead of the multiple instances is going to be in memory that SQL Server cannot use AWE for.

    Adding memory and enabling AWE is going to take more physical memory away from the SQL Servers to manage the AWE memory map. This is one of those situations in which more memory may actually hurt.

    If you intend to add memory, Steve's suggestion about consolidating the instances is something that you may need to do to get any benefit.

  • Add memory to the physical box and set up virtual servers rather than separate instances.

  • David.Poole (2/1/2010)


    Add memory to the physical box and set up virtual servers rather than separate instances.

    Can you please explain how virtual servers will be more effective than separate instances on a single machine??

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

  • If you have separate instances then you can of course use sp_configure to specify a maximum amount of memory for each instance.

    You can also specify which CPUs get used by which instance.

    You can also specify the maximum size that the DB files can reach.

    I'll admit my knowledge of virtualisation is sketchy but I know that the CPUs in the virtual machine are themselves virtual. Ultimately they share the bare metal but there is nothing to stop you having a physical box and putting a number of virtual machines on it of varying CPU power. The machines can be ring-fenced from each other as far as the developer is concerned but borrow resources as and when they need it.

    It is even possible to over-commit resources so you have 2 virtual machines that think they have 4GB of RAM even though you only have 4GB in total. Dangerous thing to do but if you know your UAT box will be busy when the DEV box is quiet it can work.

    I believe that the virtual machines get their own IP address so you can achieve isolation of those machines.

  • David.Poole (2/2/2010)


    If you have separate instances then you can of course use sp_configure to specify a maximum amount of memory for each instance.

    You can also specify which CPUs get used by which instance.

    You can also specify the maximum size that the DB files can reach.

    I'll admit my knowledge of virtualisation is sketchy but I know that the CPUs in the virtual machine are themselves virtual. Ultimately they share the bare metal but there is nothing to stop you having a physical box and putting a number of virtual machines on it of varying CPU power. The machines can be ring-fenced from each other as far as the developer is concerned but borrow resources as and when they need it.

    It is even possible to over-commit resources so you have 2 virtual machines that think they have 4GB of RAM even though you only have 4GB in total. Dangerous thing to do but if you know your UAT box will be busy when the DEV box is quiet it can work.

    I believe that the virtual machines get their own IP address so you can achieve isolation of those machines.

    There are a lot of advantages to virtualization. But helping to alleviate having too little memory to go around certainly isn't one of them. Each VM has it's own OS running which takes up lots of the precious RAM commodity.

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

  • If they were to add some RAM, though, virtualisation might well make more efficient use of the extra RAM than three separate instances of SQL server do; for instance, on a machine with 8Gb of RAM you could comfortably have three separate virtual OSes, each using 2Gb of RAM, provided you had AWE enabled.

  • paul.knibbs (2/3/2010)


    If they were to add some RAM, though, virtualisation might well make more efficient use of the extra RAM than three separate instances of SQL server do; for instance, on a machine with 8Gb of RAM you could comfortably have three separate virtual OSes, each using 2Gb of RAM, provided you had AWE enabled.

    You seem to be forgetting the fact that each VM will have it's own operating system running which will eat up 3X as much RAM on the physical server if you have 3 VMs running. That RAM takes away from the buffer pool available to SQL instances running on that physical server.

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

  • TheSQLGuru (2/1/2010)


    David.Poole (2/1/2010)


    Add memory to the physical box and set up virtual servers rather than separate instances.

    Can you please explain how virtual servers will be more effective than separate instances on a single machine??

    assuming 64 bit is not an option:

    if you upgrade the ram to 12 gb (for example) and have to use 32 bit and want to avoid awe and certain licenses:

    you can have a host with 12 gb and put 3 virtuals on it with each virtual on 4 gb (not taking a little overhead into account)

    when memory is a limitation and not cpu etc it could be an option.

    there's also no (sql/guest os) memory pool sharing issues.

    however the cause of the memory increase would be worth investigating as it could be some agent with a memory leak etc. for example i had an monitoring agent (scom) which took 1.2 gb ram every 5 min's (updating w or cscript solved that). the box has 2 gb ... it's an heavy price.

  • Has anyone noticed that the old system stored procs are not that efficient?

    IF(SELECT COUNT(*) FROM ...) >0:w00t:

    Cursors all over the place etc.

Viewing 15 posts - 1 through 15 (of 16 total)

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