February 6, 2002 at 3:26 pm
I am purchasing a new server soon that will have:
1. Four Processors
2. 8GB Ram
3. Windows 2000 Advanced Server
4. SQL Server 2000 Enterprise Edition
I am doing only the one install of SQL Server Enterprise Edition. How much memory will that SQL Server Enterprise be able to use / access ?
If I install say 4 Instances, how much Memory will they be able to use ?
February 6, 2002 at 3:43 pm
Memory management in SQL Server is by default dynamic, so it can in fact use as much as it wants. Note: SQL loves memory. With 4 instance you may seem improper division between them as they will try to take and not give if they can. Personally unless you have a real need for 4 instance of the same server of the same box you really only need the one.
February 6, 2002 at 3:46 pm
Not sure why you'd use multiple instances on a server. It sounds good for dekstops,but servers? why not different databases? I guess if you had a hard coded db name you could use it.
You should see different memory usage depending on load for each instance.
Steve Jones
February 6, 2002 at 4:08 pm
If you add the /3GB switch AND the /PAE switch to the boot.ini (requires a reboot) AND enable AWE in SQL you can address almost all of it. I typically leave 512m for the OS to be on the safe side, everything else to SQL.
Andy
February 6, 2002 at 8:44 pm
In addition to what Andy has said, there is some additional guidance in Books Online. Though I'm going to agree with Steve that unless there's a hard and fast reason to have multiple instances (except in the case of active/active clusters), a single instance is probably best. Here's the Books Online link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 7:54 am
The reason for 4 Instances is we are going from 4 Servers to 1.
Development/Testing/Staging/Training will now reside on the one BIG Server. Thus I have to create 4 Instances for this.
What is the best way to set this up ? (we only have one Server now, but huge one.)
February 7, 2002 at 8:18 am
If that is your ultimate goal, have to decide if you really need 4 instances to match the four "uses". I'd have concerns about doing testing on a production box no matter how its configured. Steve's point about just having different db names is valid, after all you have to script the changes to the next step anyway.
Andy
February 7, 2002 at 9:57 am
Since development and testing have the ability to have "untested" code, which could potentially peg the CPU, I'd avoid hacing them on the same server. I have seen queries that can use all the CPU and bring a server to it's knees. Hopefully these are caught before moving to production, so I wouldn't worry too much about training and production together.
Separate instances are licensed separately, so you still need to buy the software. Since that's the case, why not just use 2 or more boxes? You already ahve one, this would be hte 2nd, so I'd at least use 2. the hardware cost can easily be lost with downtime.
Steve Jones
February 7, 2002 at 12:56 pm
I need to clarify here.
I will have 2 Servers:
Server 1 :
Production Server (4 procs, 8GB RAM)
- will have one install of SQL Server 2000 Enterprise.
Servers 2 : Development/Testing/Staging/Training (4 procs, 8GB RAM)
- Thus, need 4 Instances since the SAME Database will reside on 4 Instances. Same Database, just in a different "Environment".
Comments ?
Memory Management on the 2 Servers ?
February 7, 2002 at 1:24 pm
I understand now. But you could still get away with 1 instance but 4 databases. Like this, MyDBDev, MyDBTest, MyDBStage, MyDBTrain and just apply your changes to all, or backup one and restore to the other. Then you get the bennifit of a single server not competing for resource time. As far as memory management, the more memory you can allocate for SQL the better it will behave. On Production I would pretty much let it take care of itself, if you go with the other with 4 instances I would give training and development the proportionate most memory. And test the least (the reason is to get the best overview of the database in times of distress and to highlite the worst of the processes and they will tend to show up better under less than ideal conditions.)
February 7, 2002 at 1:37 pm
If you have the four instances, you could still have them stepping all over each other. For instance, a query run in development pegs out the processors in the middle of a training session. You can go so far as to dedicate a processor or two to a particular environment, and you can certainly statically specify memory to ensure they aren't fighting over it.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 1:46 pm
In addition. Even though memory is a big concern it is not the biggest. As stated anything can peg out resources. If the CPU hits 100% because of what is going on in the developement instance it will completely stop the others too until it frees this resource. Also you may see higher drive contention and in general the increased threads will be potential for longer delays in access because of the nature of the way threads are processed by the OS. You really are heading for a nightmare. Most admins and developers create aditional instances to only be used as temporary staging for databases when upgrading between SQL versions.
February 7, 2002 at 3:02 pm
The reason for 4 Instances :
InstanceDEV
- BudgetBD
InstanceTESTING
- BudgetDB
InstanceTRAINING
- BudgetDB
InstanceSTAGING
- BudgetDB
I don't want to be renaming DB's all the time. This will be the case if I use one SQL Server install.
Databases from Staging will be promoted into Production.
So, keeping the same DB name through its whole Life Cycle makes things easier.
See my point ?
I am really concerned about Resource Management between the Instances.
The Server will have 4 Processors + 8GB of RAM.
How much RAM can I dedicate to each Instance ?
CPU ?
(Its on a SAN - should take care of I/O throughput)
February 7, 2002 at 4:55 pm
I see your point and you can go into SQL instance and split the memory by not doing dynamic allocation in SQL and setting it under the servers properties to a maximum. As for CPU you can even set each 1 to use a seperate CPU but I would just leave it using all 4 on each this is also available under properties (sorry I forget which tab but you will know it when you see it).
February 7, 2002 at 7:58 pm
So, the short answer to the original question is: 8GB of memory with PAE, /3GB and AWE enabled, depending on the server type. Many servers will only use 4GB of RAM, even though W2K A/S can use up to 8GB. A Compaq PL8500 server, or a similar product from one of the other major vendors that uses crossbar technology and memory interleaving can usually optimize 8GB RAM.
Baseline your system when you initially deploy it so you can monitor performance once it's in production. Continue to use dynamic memory management until you have performance issues, which may never occur, since SQL2K does an excellent job. Later on, if you do have performance issues, compare to your original baseline, and tune (optimize) your server's performance. It's the old 90/10 rule. 90 percent of your effort to acheive the last 10 percent of performance.
cabby2583
Always Learn!
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply