aquila98
asked on
how to limit ressources to instances (CPU/RAM)
Hello
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.
cheers
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.
cheers
ASKER
It is strange but MS talks about processor core afinity... I think they are careful not to imply that it is truly a dedicated core and that no process will "straddle" to another core during its life...
does any one here know if it is truly possible to tell one instance not to use more thant one core for instance? I always thought that only mainframe offered this kind of possibilitty?
does any one here know if it is truly possible to tell one instance not to use more thant one core for instance? I always thought that only mainframe offered this kind of possibilitty?
ASKER
by the way, being newby to SQL server...
Do you know the command to use to display how many core of cpu are allocated to one instance?
how many ram &c
sp-configure does not seems to know ?
thanks
Do you know the command to use to display how many core of cpu are allocated to one instance?
how many ram &c
sp-configure does not seems to know ?
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
great, thanks I will play around with all this
Right click on the Server name, and select Properties. There are tabs for memory and processor.
I think you're correct on your assumptions related to how the cores are used - but it will depend on how well MS did the parallel processing.