I am experiencing a problem with one server. It has 32 cores and seems to be well-configured in all aspects (memory, hard disk drives…). Performance Dashboard is not showing blocks or deadlocks and no waiting tasks. All queries seem to be performing well.
From time to time (every 5-6 minutes) one of the cores gets up to 100% utilization and the system's performance slows down. It lasts for some seconds.
What should I check to know what is causing this misbehaviour?
It is a SQL Server 2005 standard edition with Service Packs installed.
Thank you so much.
Best Answer
What process is using that CPU? You need to monitor the
Processobject and figure out which instance (ie.process) is driving CPU up. Also is important to distinguish between privileged (kernel) vs. user time. Also important to see if is notInterrupt Timeby any chance.The purpose of the exercise it o establish if is a hardware resource interrupt driving the CPU (I've seen this with some many network cards for instance), a user process (and which process). If you established that is SQL Server process then you can switch investigation to SQL Server specific means, at which moment it turns into SQL performance investigation well suited to Waits and Queues methodology. But if the problem is outside SQL then perhaps the best methodology to deploy is USE. And Kernrate is your friend.