 --> (Word) |  --> (PDF) |  --> (Epub) |  --> (Text) |
 --> (XML) |  --> (OpenOffice) |  --> (XPS) | |
| This article has been published [fromdate] | |||
| [readtime] | |||
SCENARIO
Yesterday one of the servers which hosts 5 instances of SQL Server 2005, the CPU constantly hitting 100% utilization. The Application team using the [gs database] on one of the 5 instances started complaining of poor performance.
The DBA connected to that instance to find out the session(s) causing the CPU to spike. But none of the processes on that instance were utilizing more CPU time. The Task Manager looked like this.
The PID column in Task Manager is not displayed by default. It needs to be selected via Task Manager –> View –> Select Columns menu item.
In my case Process ID 2652 was using more CPU. How to find out to which instance does this Process belong to? I find the following two options to track down the Instance Name.
Using SQL Server Configuration Manager:
On clicking on the SQL Server Services in SQL Server Configuration Manager, the details of SQL Server and related services along with the Process ID of each service is listed. The SQL Server Instance name is listed as SQL Server (InstanceName).
Using the SQL Server Error Log
In SQL Server 2000, SQL Server Configuration Manager is not available. The information regarding the Process ID can be obtained through the SQL Server Error Log.
Once the SQL Server Instance was located yesterday, the high CPU utilization was fixed relatively quickly. One of the scheduled job for UPDATE STATISTICS had run outside the Maintenance Window. Stopping this job brought down the CPU utilization drastically.
SOURCE | LINK (Sqldbadiaries.com)
| LANGUAGE | ENGLISH |
 |  | ||