question

askmlx121 avatar image
askmlx121 asked

cpu usage high by Sqlserver? how to become normal??

HI I am a Junior DBA we are using SQL Server 2005, In our Server 32 GB of RAM CPU Processor EM64T Family 6 model 15 stepping 6 Genuineintel-1995 mhz windows server 2003 R2 **But last week we faced CPU usage very high by SQL SERVER.** see the figure below; Fig 1: ![alt text][1] Fig 2: ![alt text][2] I have checked for any long running queries/Transaction checked in day by day basis. but recently no long running quries are taken place then **why CPU process reaches 100% frequently? which steps to follow CPU become normal state or not using SQL SERVER more resource???** [1]: /storage/temp/509-sql+process+high.jpg [2]: /storage/temp/510-cpu+usage+high.jpg
sql-server-2005performancecpu
cpu usage high.jpg (100.2 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@askmlx Seeing your another question regarding large ldf file, there could probably be two major reasons 1. The auto-growth of log file may not be big enough And there could have been a constant increase in the log file. 2. Indexes rebuild. That could have hit the tempdb as well as resulted in a huge log file. A long running transaction could have been the culprit as well.
4 Likes 4 ·
sp_lock avatar image
sp_lock answered
You could try and identify what is causing the high CPU. I tried to find the video from SQL skills (April 2012), but stumbled across a blog that does a similar thing in PoSH.. Once you have found the thread, run the TSQL to find what code is taking up all the of the CPU. This method has saved me a few times. [ http://sqlscope.wordpress.com/2012/05/07/identifying-high-cpu-sql-server-thread/][1] [1]: http://sqlscope.wordpress.com/2012/05/07/identifying-high-cpu-sql-server-thread/
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for sharing the link. Will try to use it. Powershell is the thing I want to learn but somehow never have enough time.
0 Likes 0 ·
Failing PoSH, just use process explorer from sysinternals to get the thread id.
0 Likes 0 ·
I have tried the mentioned link( http://sqlscope.wordpress.com/2012/05/07/identifying-high-cpu-sql-server-thread/) powershell script but didn't get any output but it's not failing. Can you guide me on the same. I have tried the following script to keep cpu busy but didn't get the output. Script: USE AdventureWorks GO DECLARE @Flag INT SET @Flag = 1 WHILE(@Flag < 1000) BEGIN ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetail] REBUILD SET @Flag = @Flag + 1 END GO
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
How long did this last? What is normal CPU usage for this server?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hi jonathan alien, thankx for reply normal CPU usage is 50-80%
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
You should capture the counters to find what makes your CPU spikes high. It could be because of memory or disk bottleneck or it could be because of Aggregates, sort or string manipulation in your queries. These are basic reasons, there are plenty of other reasons also. So better start running a performance trace by using SQL Server Profiler trace and Windows performance monitor, Start capturing the basic counters in Windows Monitor, and the query and the query stats in profiler, correlate the result of perfmon and the profiler, these shows what in SQL Server made these spikes go high. I advice you to no to capture these info in your production.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1. But use of Profiler at that time may not be the best choice ;) It could slow down things quite a bit.
2 Likes 2 ·
I personally prefer extended events, they are very light weight.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.