hi,
In one of our sql server instance, we see sporadic shoot up of the CPU. In ideal scenario CPU utilization is around 50-60 percent, but sometimes it goes to 90 percent. What i've observed is we have a one particular task that runs every 5 mins and sometimes is runs longer for more than 5-6 hours (output from sp_whoisactive) thats when cpu utilization goes beyond 90 percent.
however when i run sp_whoisactive procedure to sort processes by CPU i dont see these processes takes lot of cpu, there are other processes which takes more cpu, but these top cpu processes do not run longer may be for 5-10 mins. To bring down CPU utilization i kill these long running sessions and cpu utilization comes down to normal.
Can someone explain why these long running processes , even if not taking lot of cpu time could be causing cpu usage to spike up so high?
