| Author |
Topic |
|
adamdavi3s
Starting Member
5 Posts |
 Posted - 2011-03-29 : 06:16:28
|
| Hi All,Long time lurker first time user!Firstly, I am more of an application manager than a DBA, but I have adminsitration rights over the servers and an understanding of SQL Server.We have a number of environments running the Agresso application on VM ware Servers running Windows Server 2003 64 bit.I've noticed CPU spikes across two of our environments, Live and Test. Live is managing because it has four cores allocated, however out test server can't handle the spikes as it only has two cores.The spikes are on sqlserver.exeThese spikes are exactly 8 seconds apart and are usually 50% spikes with the odd one or two hitting 100%Strangely these happen with the application service STOPPED.I've traced RPC: Completed and SP:stmtCompleted and the only thing running is databasemail every 5 mins.The application support have re-indexed some tables after looking at the expensive queries, but obviously this hasn't helped as its still happening with the application stopped.I've looked at the batch requests, compilations and recompilations in perfmon and they are essentially doing nothing although there is a regular spike on batch requests/sec of 0.98 followed by one of 22 every 30 seconds.I also ran a query to look at the cpu time and executions per plan_handle but that's way beyond me!Although I noticed one with a very high CPU time and an execution count of 1select top 50 sum(qs.total_worker_time) as total_cpu_time, sum(qs.execution_count) as total_execution_count, count(*) as number_of_statements, qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time) descWe've also tried putting the server on its own host with 64 cored and 256gb of ram and it still did the same so there isn't anything else hogging the CPU!Can anyone give me any advice at all? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-03-29 : 15:50:38
|
| If the spikes are happening regularly every 8 seconds, I would think they are the checkpoint process. This is normal and shouldn't be of any concern.If this is causing performance issues, I would start looking at your IO subsystem to see if there are any issues there.Jeff |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
adamdavi3s
Starting Member
5 Posts |
Posted - 2011-03-30 : 03:59:46
|
| Thanks all, the spikes are causing performance issues with the server, or at least that is the excuse our server team are using for the poor performance!The issue does seem to be that the spikes can be larger, 80-100% CPU but I can't seem to find the logic behind this! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
adamdavi3s
Starting Member
5 Posts |
Posted - 2011-03-31 : 03:40:04
|
| No They are only a second for each spike.I am not at all convinced that they are causing the performance issues but it is the avenue that is being explored! |
|
|
|
adamdavi3s
Starting Member
5 Posts |
Posted - 2011-05-17 : 06:57:01
|
| Just by way of an update, we never actually found out WHY this was happening, however it was referred back from:Our server team to our DBAsFrom our DBAs to microsoft supportFrom microsoft support to the guys who wrote MS SQL Server.....who said they would need to write a patch specifically for us.At the end of the day we took the easy option and upgraded to 2008 R2! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
adamdavi3s
Starting Member
5 Posts |
|
|
|
CPU Spikes, out of ideas!
Thanks for the update!Tara Kizer