SQLSERVER2008-2012 Resources and performance monitoring-CPU usage monitoring specific explanations

Preface:

The CPU is the most important resource in the server. In 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. All features of Performance Monitor at the same time.

Performance counters provide statistical functionality for various system activities. Can find hundreds of performance counters for the performance of Windows operating systems or third-party applications. SQL Server has the same hundreds of performance counters for DBA 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 start, you need to know 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. Assume that there are multiple CPUs or CPU cores 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 "execute".

2. When open, select Performance Monitor.

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

4. In the Join Counter dialog box, enter the name of the computer you need to monitor or use the local computer <local computer>.

5. Open Processor and select%processor time.

6. Then see the counter added 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 the completion of the join, the results such as the following, and then click OK:

9. Then you can see the results:

Analysis:

Using performance counters in reliability and Performance monitor is straightforward. 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".

Suppose you find that the average of processor:%processor time is consistently at 80 and system:processor Queue length continues to exceed 2, which could mean that the CPU is not fast enough to process processes on the server, You need to upgrade the CPU or join the CPU at this time.

SQLSERVER2008-2012 Resources and performance monitoring-CPU usage monitoring specific explanations