High CPU issues that are specific to SQL Server 2000 installations and few with SQL 2005 too!
Here comes another Frequently Asked Question (FAQ) on the forums and Frequently Posted Blog (FPB) here about high cpu issues on the SQL Server 2000 instances.
Looking at high cpu spiks and performace issues is a common problem in all SQL Server installations, handling this issue within SQL Server 2005 is not a problem as there are DMVs to extend helping hand and blogs that are straight to the issue and resolution I recommend from here -
SQL Server 2005 high CPU occurrence - why it is?
sys.dm_os_schedulers - useful to find on whether your SQL Server engine is weakening!
List of CPU based DMVs in SQL Server 2005 - best used for hardware analysis
Cached query plans and top stored procedures that are recompiled
Identify and troubleshoot slow running queries in SQL Server
When you get complaints about performance is degraded on the SQL Server applications and you observe high CPU means first thing to check for poor query plans, blocking and any scheduled jobs that runs DBCC statements during a busy period on the database, like the ones I have referred above. Not only this there is still another catch in the problem that unusal chain reaction within your environment a SQL Server 2000 service that is running on a computer that has a system time that is approaching a multiple of 49.7 days from the time when the operating system is restarted. Around this period, you back up a database at least one time or you restore a database at least one time (obviously).
KBA 930484 talks that In this scenario, CPU utilization may approach 100 percent after the backup operation is complete or after the restore operation is complete. After approximately 15 minutes or less, CPU utilization automatically decreases. If you back up the database or restore the database when CPU utilization approaches 100 percent, CPU utilization appears to decrease.
Note This problem appears to occur more frequently on Itanium-based computers than on x86-based computers. This problem is very noticeable on the SQL Server servers that perform backup operations or restore operations around this 49.7 day interval.
Not only above symptoms & solution, this 49.7 cycle of high cpu occurrence is due to GetTickCount API called within PERFMON counters. Within the instances of SQL Server 2005 and in SQL Server 2000, for @@CPU_COUNT function the SQL Server database engine uses the following two forms of timer to generate time information:
• |
High-resolution timer |
• |
Low-resolution timer |
The high-resolution timer resolution is based on the Read Time-Stamp Counter (RDTSC) instruction of the CPU. In the low-resolution timer, the timer resolution is based on the GetTickCount function in the Windows API. Due to the default settings of this API and other various timer-based background tasks and critical system components rely on these timers for their correct functioning will cause a spike in the CPU. Also as per another KBA for example, you perform tasks that involve the following external components and applications:
• |
SQL Server Profiler or SQL Profiler event columns such as the Start Time column, the End Time column, and the Duration column for various events |
• |
Time information that is reported in various logs such as the SQL Server Errorlog, event logs, and system tables |
• |
System functions such as the GetDate function and the GetUtcDate function |
So next time when you are called for this performance issue of HIGH CPU then ensure to collate information stated in above blogs & KBA that talks about HOTFIX to resolve this issue. So far I haven't seen similar issue within a SQL Server 2005 instance but there could be another reasons.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.