MsSql 2005 Db using 100% (8 itanium) Cpu's without doing anything
I have this DynamicsAx 3 tier system that had a bad hickup this morning - some processes locking up Db and using tons of i/o After killing those processes and removing the source the system went back to normal in my monitoring system - except for using all CPU power for aparantly nothing ! I'm using Perf. Analysis for Sql and Spotlight from Quest software and of course the standard Sql tools delivered by Ms and nothing tells me what's eating my system ! (Sql server process on the server using between 85 and 95 % of the total CPu available)
You can start with this: SELECT * FROM sys.dm_exec_sessions WHERE cpu_time > 0 ORDER BY cpu_time DESC You may want to reduce the columns returned, but it should help see what has been consuming a lot of CPU. You may have to run it a few times to get some deltas.
Try to update statisics, I had a similar problem with a server. It turned out to be a query that used to work fine until to much data was updated and the execution plan was completely wrong. After the update stats it worked fine again. I cannot guarantee that it will solve your problem, but it may be worth a try.