Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database server running out of memory Expand / Collapse
Author
Message
Posted Monday, June 30, 2008 12:35 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 02, 2012 2:29 PM
Points: 633, Visits: 256
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
Post #526153
Posted Monday, June 30, 2008 12:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #526157
Posted Monday, June 30, 2008 12:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
If you are running a 64bit OS, you may want to consider adding some additional memory.
Post #526160
Posted Monday, June 30, 2008 12:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:54 PM
Points: 32,819, Visits: 14,964
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?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #526162
Posted Monday, June 30, 2008 1:34 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 02, 2012 2:29 PM
Points: 633, Visits: 256
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
Post #526190
Posted Monday, June 30, 2008 1:40 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 02, 2014 10:30 AM
Points: 646, Visits: 731
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?


Maninder
www.dbanation.com
Post #526198
Posted Monday, June 30, 2008 1:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #526201
Posted Monday, February 01, 2010 5:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 19, 2014 3:49 PM
Points: 2,866, Visits: 1,708
Add memory to the physical box and set up virtual servers rather than separate instances.

LinkedIn Profile
Newbie on www.simple-talk.com
Post #857074
Posted Monday, February 01, 2010 11:58 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 4,133, Visits: 5,851
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 at GMail
Post #857741
Posted Tuesday, February 02, 2010 3:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 19, 2014 3:49 PM
Points: 2,866, Visits: 1,708
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.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #858284
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse