sudden spike in cpu cycles

I have dell server E5- 2609  with 32GB RAM oflate I am getting sudden spikes (50 to 60%) in cpu usage. and the application access from this server is getting slow. we need to restart the SQL Server Service  and it starts working fine.

Can some expert tell possible reasons for the same
vensaliAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brett DanneyIT ArchitectCommented:
How much memory have you assigned to SQL?
When the CPU is spiking what process is causing the spike?
vensaliAuthor Commented:
It is sql server 2008r2 64bit and OS is win Server 2008 Enterprise. Memory is 32GB.   8 core processor.

Memory assigned to sql is default. (Min - 16384 Max - 2147483647).  

When cpu  is spiking -  sql server process goes to 50 to 60 cycles.
Brett DanneyIT ArchitectCommented:
All sounds good on the face of it. My next step would be to take a look at what SQL is doing. Have a look at Recent Expensive Queries in the SQL activity monitor. This will isolate what is happening at a SQL level. Sounds like you need to do some SQL tuning at this point. Most times it will be tracked down to developers doing something silly in the queries.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

ZberteocCommented:
There is no need to restart the SQL server for this. Of course it will make the spike go as it is resetting all connections and all processes but that is not a solution. You will have to find the reason for spike but 56-60% is nothing to worry about, it is merely a normal level.

In order to find the reason you will have to put in places some monitoring tools. I recommend you to start with the easy ones like sp_whoisactive stored procedure from Adam Machanik:

http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

This can be setup to save data in a table when certain conditions are met.

Another recommendation is the free version of Solaris Monitoring Tool. For 14 days you will have the full version to try after that you can still use it in the limited version, 1 hr of history only, but which is still very useful. I am using it for couple of years now:

http://www.solarwinds.com/database-performance-analyzer-sql-server.aspx

These tools will help you identify what processes/queries are causing the spikes and then take action to remove the, It could be a missing index some inefficient query but you need to identify them first.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vensaliAuthor Commented:
Just installed solarwinds sqlperformance analyser.  It is showind red alert in  OS Mem utilization  & Proceudre cache hit ratio.

I checked up in the server.  sql server is taking up around 30GB memory.  But I presume, in 64bit sql server, memeory gets allocated to sql server dynamically  and it occupies almost full available memory and releases to OS dynamically.

When I restart the service entire memory is freed and application starts working fast and as it approaches to max memory , it starts slowing down,  Is there an way I can address this.
Brett DanneyIT ArchitectCommented:
You can set the memory limit that SQL can consume, on 32GB total set it to 24GB - 28GB. This will leave memory for the OS to run and will cause SQL to flush more. Hitting procedure cache issues suggests there may not be enough memory in the server, so if you are still having issues I would suggest a memory upgrade to at least 64GB.

You limit SQL memory in the same you found this info:

Memory assigned to sql is default. (Min - 16384 Max - 2147483647).
ZberteocCommented:
Vensali, In the  solarwinds sqlperformance analyzer look into the window that shows you the wait statistics in the form of bars for every day and than click to the current one and drill down to see every 10 min. Look for the bottom bricks in the bar that are the thickest and hover the mouse on it and see what query it will show you. The bottom chunks are the queries that cummulate the most waits and those need to be addressed first. It will look something like this:

 Capture.JPG
ZberteocCommented:
Also if you click on the bar it will bring you to another screen that will show you the wait types that cummlated the most time, which in turn will tel you what is your bottleneck:
 
Capture2.JPGIn the picture you see that the Memeory/CPU time is the higest.

Further if you click on the horizontal bar it will tell you the query/procedure details that caused the wait time. You have to address those queries and make them more efficient.
ZberteocCommented:
The procedure cache hit ratio by default the critical level is set to 90% and when it goes below that it will show in red. However that is not a real concern unless is much lower than that. If the line is around 90-95 zone it is not a big deal even though ideally should be over 95. Here is how it looks like:

Capture3.JPG
You can actually change the settings to other thresholds if you want(Settings link under the graph), but defaults are fine. You can hover the mouse over the graph to see more detail or click on Information link under it to see general recommendations.
ZberteocCommented:
As a general idea the bars should keep a relatively constant trend(height) over time within some variations based on activity fluctuations. If there is something that causes anomaly will be noticed by a spike of the bars or a constant growing trend. That is what you have to look after and also you will have a better image after you ran the monitoring at least 1-2 days.

Beside the bar you have all sorts of data under Resources link in form of graphs about different components like CPU, Memory, Disk, Network, etc. The full version has a lot more useful data but the free is not bad either.

One thing to keep in mind is that is recommendable to install the tool on a different box and its database should not be on a server that you monitor. It will work anyway but if the hosting server is affected then it will have problems in saving data. The application however keeps a quite low profile and will not impact itself any server that is monitored.

Just to clarify, I am not making any advertisement here but in my experience this was by far the best tool I have used and the fact that it looks into the waits time and not just in query duration is actually the best way. It will point you very quickly to the performance problem cause and bottleneck. It takes a bit to get familiar with but after that is quite easy to use.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.