July 28, 2008 at 5:14 am
Hi all,
I have to install a SQL server 2005 with 5 instances.
Are there best practices determining how much memory should I implement for each instance?
Thank you.
Xavier.
July 28, 2008 at 6:34 am
Hi Xavier,
Depends very much upon the databases each instance will house. You may find one instance may require more memory than the others. Go back to the developers/3rd parties of the databases concerned and get detailed technical specifications. This should help you plan accordingly.
Cheers,
Mark
July 28, 2008 at 6:46 am
This is obviously dependent as well on the amount of RAM inside the SQL Server... if you only have 1 or 2GB you will need to increase this first!
July 28, 2008 at 6:57 am
The RAM size of sql server will depend of the instances.
Actually I planned to implement 15 GB...
On each Instance there will be 5 databases for a 90 Gb total size.
There will be about 100 users per instances.
Regards,
Xavier.
July 29, 2008 at 5:06 am
You have the memory available and assuming you not running any other applications on the server I would be tempted to assign a fixed memory amount of 2GB to each instance.
What disk storage do you have planned?
July 30, 2008 at 5:25 am
I tend to agree with ybol.
Are you using a 32 or 64 bit server btw?
If you use an average of 2GB per instance, that will allow 5GB left over for the op system - which should be plenty.
You did remember that you will need to allow some for the op system didn't you? You would be surprised at how many clients I have had who thought that they could give SQL server 100% of the memory and wondered why the system was unusable during heavy loading periods, and that they couldn't log on to in order to diagnose problems!
You may be able to get away with throttling some down to less (if they are not holding much and are light usage) and ramping others up (if they are OLTP or OLAP instances for example).
If you want to get REALLY sneeky, you can alter the memory used per instance with a scheduled job that reduces some instances and increases others according to the time of day - if, for example, you have a rebuild of your cubes over night, you might want to give that instance more, and your OLTP server might go quiet at the same time.
As has already been stated, it really depends on what the DBs are going to be used for. Try, if you can, to group your databases onto an instance by purpose. This will help you capacity manage them. Don't think that if you spread your databases across instances then you will be evening the load - unless they are all identical and will be used in exactly the same way. It may be the case that one instance with 3 databases on it may be a heavier load than another with 20!
I think that you will end up doing a month or so of monitoring and tweaking.
HTH
Best of luck
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
July 31, 2008 at 8:47 am
I would like to ask what you reasoning/justification is for having 5 instances on the same server. I can think of a number of reasons to NOT do this.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 31, 2008 at 8:58 am
Other than the obvious reasons not to have that number of instances, if you have the hardware to cope with it, and you maybe need different collations, or case sensitivity. Plus its cheaper to buy one BIG box than a number of smaller ones.
If you dont have the need for high end through put, or heavy processing, then it will cope.
One big reason that a number of my clients have used for doing this is the licensing cost. They believe that once you have paid for the CPU license, then you can put any number of instances on that box under the same licence. This can save SMEs many thousands of pounds/dollars - which in these days of watching the ROI is important.
Bets the main reason is money...
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
July 31, 2008 at 9:32 am
But again, unless there is a very specific reason to do so, performance is often better with all of the databases in one instance. SQL 2008 (EE anyway) will eliminate at least one reason for multi-instancing by having the resource governor able to ensure minimum resources to specific users/applications/databases.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply