100 % cpu

Question

100 % cpu

ananthbhaskar

Right there with Babe

Points: 783
More actions
August 30, 2010 at 4:55 am

#225334

Hi All,
in one of my BCP boxes , i often get 100 % CPU utilization ticket, i check all the job's running and set a trace flag also but i not able to find perfect cause, can anyone guide me in finding the actual cause for this, and different ways i can handle thsi, thanks in advance.

Viewing 6 posts - 1 through 6 (of 6 total)

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

free_mascot One Orange Chip Points: 27164 More actions · Answer 1

If I guess correctly you are facing this problem after perfroming the BCP. I suggest you rebuild the indexes of respective table after doing BCP.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

It may or may not be indexes, who knows based on the information at hand. What I'd suggest is taking a look at sys.dm_exec_requests and see if there are processes that are waiting or blocked. If they are waiting on a resource, drill down and determine exactly what's causing the slow down. You can also look at sys.dm_os_wait_stats to get cumulative wait times to determine what things are causing slow downs on the system over all.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Check Whether the Server is experiencing any excessive Comilations and recompilations\sec and also see whether you are getting any CX_PACKET waits which indicates parallelism. Also check for any memory pressures and paging activity which may also result in CPU presure.

Thank You,

Best Regards,

SQLBuddy

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

Someone just touched on it...check your max degree of parallelism, and then check how many processes are waiting on CXPACKET. If you have a ton of cxpacket waits, lower your MAXDOP.

Carlton Leach SSCarpal Tunnel Points: 4202 More actions · Answer 5

Among other things mentioned in this post this may help: http://www.sql-server-performance.com/tips/performance_monitor_cpu_counter_p1.aspx

Cheers,

Carlton.