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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chapmandewConnect With a Mentor Commented:
>>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
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
dba2dbaAuthor Commented:
can  know how to restrict no of processors
0
 
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
 
dba2dbaAuthor Commented:
thanks
0
All Courses

From novice to tech pro — start learning today.