SQLSERVER2008-2012 Resources and performance monitoring-CPU usage monitoring detailed



This article goes on to explain how to monitor CPU usage.

Objective:

The CPU is the most important resource in the server. In the database server, CPU usage should always be monitored so that SQL Server is in optimal condition.

This article will use reliability and Performance Monitor to obtain CPU-related usage statistics

Reliability and Performance Monitor is an enhanced version of past Performance Monitor tools. It also has all the features of the Performance Monitor.

Performance counters provide statistical functionality for various system activities. There are hundreds of performance counters that can be found for the performance of Windows operating systems or third-party applications. SQL Server also has hundreds of performance counters for DBAs to use.

In this article, we will track the data for the following related CPU performance counters:

• Processor:%processor time
• System:processor Queue Length

Preparatory work:

Before you begin, you need to understand the processor:%processor time and system:processor Queue length-related knowledge.

Processor:%processortime: Provides the percentage of CPU time that is used by a thread. If there is more than one CPU or CPU core in the system. You will see that there are multiple counters on this instance.

System:processor Queue Length: Shows how many threads are waiting for a CPU to be used.

Steps:

1. Turn on reliability and Performance Monitor: Enter Perfmon.exe in run.

2. When open, select Performance Monitor.

3. Press DELETE in the toolbar or click the X button to delete all performance counters. Then click the + button to add the counter.

4. In the Add Counters dialog box, enter the name of the computer you want to monitor or use the local computer <local computer>.

5. Open Processor and select%processor time.

6. Then add the counter to the monitor:

7, repeat the above steps to add another counter, this counter in System→processorqueue Length:

Notice that there is no instance selected for this counter.

8. After adding, the result is as follows, then click OK:

9. Then you can see the results:

Analysis:

The performance counters in reliability and performance Monitor are very intuitive to use. Note the results of the graphical interface, which are displayed in seconds, in real time. In the middle part of the graph, there are the values of "newest", "Average", "maximum", "Minimum" and "duration".

If you find that the average of processor:%processor time persists at 80 and the System:processor Queue length continues to exceed 2, it may mean that the CPU is not fast enough to process the processes on the server, You need to upgrade the CPU or add the CPU at this time.