how to limit ressources to instances (CPU/RAM)


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.

Who is Participating?
kenwagersConnect With a Mentor Commented:
The easiest way is to look at the Server Properties in Microsoft SQL Server Management Studio - right click on the server name, and select Properties.  You'll see info on both CPU and RAM, among other things.  You can make changes here also.

The data is all there in SQL - I ran a trace when I opened this form, and the data comes from a series of queries, all of which center around the sys.configurations view.

Try this if you want to see the raw data:

select * from sys.configurations
It's easiest to manage the memory and processors allocated directly inside Microsoft SQL Server Management Studio.

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.
aquila98Author Commented:
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?
aquila98Author Commented:
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 ?

aquila98Author Commented:
great, thanks I will play around with all this

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.