August 30, 2010 at 4:55 am
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.
August 30, 2010 at 5:10 am
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."
August 30, 2010 at 7:47 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 30, 2010 at 8:44 am
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
August 30, 2010 at 12:26 pm
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.
September 1, 2010 at 3:00 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply