Still working on planning for capacity...
I would like to validate these affirmation and if you can, please telle me if they are true for Sql Server 2005 as well as 2008...
1a) say I have 8 gb of RAM and two instances (default and another one), each with two databases. I can reserve 4gb to Sql server. My OS will then have the remaining 4gb to do its own stuff.
1b) Further, I can limit the default instance to a maximum of 1gb and the second instance to 3gb. is this correct? Is the sp_configure use to acheive this? Which command to use?
2) I can also limit the amount of CPU used. Say I have a dual cpu each with 4 core (8 core total) I can specify that the default instance will take only 2 and the second instance will take 4 thus leaving 2 core for the OS. this means that even if one database query in the second instance take up 100% of CPU (100% of 4 core) my server will run at 50%, maybe more but at least not at 100%. Is this correct? Can i use proportion like this: default instance as 2 core (2/8) thus if a query in one of its database puts cpu at 100%, it is only 100% of 2/8 core, thus taskmanager will show around 25% overall activity... is this correct? is sp_configure the way to allocate core to one specific instance?
thanks for confirming these statements and command to use.