This post has been wikified, any user with enough reputation can edit it.
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
@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.
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/
+1 for sharing the link. Will try to use it. Powershell is the thing I want to learn but somehow never have enough time.
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
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.
+1. But use of Profiler at that time may not be the best choice ;) It could slow down things quite a bit.
13 People are following this question.
