Limit the CPU usage of sql server 20008

Question

Post reply

Limit the CPU usage of sql server 20008

a.raju

Old Hand

Points: 316
More actions
April 20, 2010 at 3:23 am

#386742

Hi Team,
Limit the CPU usage of sql server 2008, when we running the sql jobs CPU performance using 90% at that time my website is down. So, I need to limit the sql job using the CPU performance 40% only. If we use 40% of CPU performance, It will take more time at our end it is OK.
I need to control the CPU performance usage of SQL server.
Please any body facing these problem give me the instructions.
Thanks & regards,
Raju

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ananda-292708 Ten Centuries Points: 1339 More actions · Answer 1

is your database on separate server or it host everything ?

a.raju Old Hand Points: 316 More actions · Answer 2

Hi Ananda,

Thanks for quickly replay, my database server and application hosting server are different.

Thanks & Regards,

Raju

r.dragoi-1095738 SSCrazy Points: 2022 More actions · Answer 3

There is an interesting documentation here http://technet.microsoft.com/en-us/library/cc280384.aspx, maybe it helps.

shen-dest SSCrazy Points: 2498 More actions · Answer 4

Resource Governer is what you are looking for.

Regards,
MShenel

a.raju Old Hand Points: 316 More actions · Answer 5

Hi,

I need to use, to control the CPU performance with in the stored procedure.

Regards,

Raju

Ananda-292708 Ten Centuries Points: 1339 More actions · Answer 6

i dont' thing there is a way to control cpu through stored proc and more over i would not suggest doing this. At the most you can check the cpu affinity mask setting and can play a bit with it .

Try to find out the query whose cpu is high (there are lot of dmv's to help you out) and then try to find the exact problem. It could also be because of less memory SQL is performing harder and as a result high CPU.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 7

a.raju (4/20/2010)
Hi,
I need to use, to control the CPU performance with in the stored procedure.
Regards,
Raju

How many CPU's are there in the server?

You can limit the number of CPU's used by the query using MAXDOP.

a.raju Old Hand Points: 316 More actions · Answer 8

Hi Suresh,

I am using one CPU for Database.

Application is hosting in another CPU.

Regards,

Raju

Suresh B. SSC-Insane Points: 22986 More actions · Answer 9

Your stored procedure has only one task or many?

Use WAITFOR between tasks inside the stored proc.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 10

Did you have a look at ongoing process locks during your jobs ?

Maybe your website is just being forced out because of the locks your sql jobs cause on your table(s).

I would first have a look at the locking behavior before searching an other way of tweaking your jobs.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jayakumar Krishnan SSCarpal Tunnel Points: 4710 More actions · Answer 11

If I understand correctly, this would help you...

Are you trying to restrict the CPU usage by SQL, I mean for Eg. you have 8CPUs in the SQL box by default SQL will use all 8 CPUs and if you want to restrict the SQL to use only 6CPUs then you can do it by changing the SQL server properties.

SSMS-->Properties-->Processors-->Uncheck no of processors you want to use for OS.

Thanks
Jay
http://www.sqldbops.com