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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 ?

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aquila98Author Commented:
great, thanks I will play around with all this

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.