cpu is 100 percent utilized by sql server can i restrict sql server to use 80% cpu

some times my windows server shows cpu is 100 percent utilized when sql server has heavy transactions can i restrict sql server to use 80% cpu

help tips to maske cpu work better
LVL 8
dba2dbaAsked:
Who is Participating?
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.

chapmandewCommented:
Most likely...you're going to need to tune your queries to make your CPU usage go down.
0
Paul MacDonaldDirector, Information SystemsCommented:
I don't know of a way to do it by percentage, but it's possible to restrict the number of processors and the number of threads, as well as the amount of memory, available to SQL Server.
0
dba2dbaAuthor Commented:
can  know how to restrict no of processors
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Paul MacDonaldDirector, Information SystemsCommented:
In the SQL Manager, right-click on the server in question and select Properties.  I believe the Processor and Memory settings are the first couple things in the list in the left.
0
LMiller7Commented:
Controlling CPU usage is something that really needs to be done by the OS. Applications don't normally know how much CPU time they are using and can only control this in a rather crude way. Windows 2000 Datacentre Edition could do this but I don't know if later versions maintained it.  Normally there would be no benefit in this unless there are other applications that cannot get adequate CPU time.
0
David ToddSenior DBACommented:
Hi,

Of course this is going to have a negative effect on your querries.

So why do you want to make a possibly bad situation worse?

Are there other applications on this server that are struggling?

See this series for a take on how to audit server performance from a DBA point of view
http://www.sql-server-performance.com/articles/audit/perform_performance_audit_p1.aspx

As chapmandew said, you are going to need to tune your queries.

Cheers
  David
0
David ToddSenior DBACommented:
PS This isn't like the red line on your cars rev counter. The CPU stuck at 100% isn't a bad thing like it is with the car which translates to significant wear on the engine.

The only issue with the CPU at 100% for too long is that you are likely to have queries that are taking too long to return to the user, and/or queries that are timing out.
0
chapmandewCommented:
>>The only issue with the CPU at 100% for too long is that you are likely to have queries that are taking too long to return to the user, and/or queries that are timing out.

Also, if the CPU stays at 100% for too long, it likely means that a particular process has went parallel, is taking up CPU resources,  and locking everything else.

Tune your queries.
0

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
dba2dbaAuthor Commented:
thanks
0
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

From novice to tech pro — start learning today.