The CPU Utilization Shoots 100%

Question

question

Amulya Dutta asked Jun 1, '10

The CPU Utilization Shoots 100%

On this SQL Server 2000 SP4 Machine (ServerPrincipal), the job pulls in data from Table1 and pushes the data to another SQL Server 2000 SP4 Machine (ServerTarget). The ServerTarget has Datanase Isolation Level as Serializable. There are constant updates on the ServerTarget.

I can see that the both the databases have proper indexes and are properly utilizaed. The statistics are updated two days back. I can see that ServerTarget accquires Page locks while updates are in process.

Since the last say 20 days the ServerPricipal exhibits nearly 100% CPU Utilization during morning when there is a huge data to be sent from ServerPrincipal to ServerTarget.

Need guidance to troubleshoot.

sql sql-server-2000 server cpu

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-06-01T08:39:15Z

Grant Fritchey answered Jun 1, '10

If it's a large process, with lots of data, you might just be bound on the cpu. In that case the answer is to get more and/or faster processors. However, I'd first look to the code. You've outlined that the stats are up to date and the indexes are in use, but you didn't specify how you know the indexes are in use. Have you looked at the execution plans for the queries? Are there index seek operations? What other operations are at work?

Just because you're maxing on cpu doesn't mean that's the bottleneck. Can you look at the wait states and identify where most of the slowdown on processing is occurring. You might want to look at the processes as they're running to see what they're waiting on. Again, it might not be CPU. It's possible that the CPU is the cause. It's also possible that the CPU use is a symptom. You just have to drill down to see for sure.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question