SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database server running out of memory


Database server running out of memory

Author
Message
Eichpeel
Eichpeel
SSChasing Mays
SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)

Group: General Forum Members
Points: 655 Visits: 257
We have a SQL server 2005 machine that has 3 SQL instances for our DEV/STG/UAT environments. I know Wink. 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
Michael Earl-395764
Michael Earl-395764
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4891 Visits: 23078
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.
Michael Earl-395764
Michael Earl-395764
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4891 Visits: 23078
If you are running a 64bit OS, you may want to consider adding some additional memory.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62350 Visits: 19102
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
My Blog: www.voiceofthedba.com
Eichpeel
Eichpeel
SSChasing Mays
SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)

Group: General Forum Members
Points: 655 Visits: 257
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
MannySingh
MannySingh
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1129 Visits: 787
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
Michael Earl-395764
Michael Earl-395764
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4891 Visits: 23078
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.
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7560 Visits: 3284
Add memory to the physical box and set up virtual servers rather than separate instances.

LinkedIn Profile

Newbie on www.simple-talk.com
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12375 Visits: 8548
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
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7560 Visits: 3284
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search