t–sql pl–sql
Starting with this article, we will present the most important CPU metrics, describe the information they show, and give recommended values and thresholds
从本文开始，我们将介绍最重要的CPU指标，描述它们显示的信息，并提供建议的值和阈值
Processor metrics show processor performance and influence of other components on processor
处理器指标显示了处理器性能以及其他组件对处理器的影响
(Processor: % Processor Time)
The % Processor Time counter shows the percentage of time that “the processor actually spends working on productive threads and how often it was busy servicing requests.”
处理器时间百分比”计数器显示“处理器实际在生产线程上花费的时间以及它忙于处理请求的频率”的时间百分比。
As soon as the computer is turned on, the processor is executing threads with instructions. The processor is always active, even when there are no user or system threads, it is not completely idle as it executes the “idle thread” then
一旦打开计算机，处理器就会执行带有指令的线程。 处理器始终处于活动状态，即使没有用户或系统线程，它也不会完全空闲，因为它会执行“空闲线程”，然后
By design, there can be only one idle thread per processor. It has the lowest priority among all processor threads. The basic priority classes are idle, normal, high, and real. This means that an idle process is running on a processor only when there are no other threads. The idle process isn’t a real process that “eats” processor resources. It only occupies the processor until a real productive thread appears. A high percentage of system idle processes shows that the processor is unused most of the time
按照设计，每个处理器只能有一个空闲线程。 在所有处理器线程中，它的优先级最低。 基本优先级类别是空闲，正常，高和实数。 这意味着仅当没有其他线程时，空闲进程才在处理器上运行。 空闲进程不是真正“消耗”处理器资源的进程。 它只占用处理器，直到出现真正的生产性线程。 高比例的系统空闲进程表明处理器大部分时间都未使用
The percentage of the processor time counter is calculated as the difference between the total processor time and the time the idle thread was running
处理器时间计数器的百分比计算为总处理器时间与空闲线程运行时间之间的差
On a multi-processor machine, an instance of the % Processor Time counter is shown for every processor on the server. On a four-processor machine, the % Processor Time
％Processor Time计数器的实例。 在四处理器计算机上， ％Processor Time实例将被枚举0到3。此外，每个处理器线程都显示一个实例。
On a virtual machine, % Processor Time
％Processor Time显示虚拟机（而非物理机）的值
The recommended value for % Processor Time is below 80%. Occasional peaks are acceptable, but it shouldn’t be constantly high. But even high values of the % Processor time
处理器时间百分比的建议值低于80％。 偶尔的峰值是可以接受的，但不应一直很高。 但是，即使％Processor time计数器的值很高，也不能清楚地表明瓶颈
If the % Processor Time value is constantly high, check the disk and network metrics first. If they are low, the processor might be under stress. To confirm this, check the average and current Processor Queue Length value. If these values are higher than the recommended, it clearly indicates a processor bottleneck. The final solution for this situation is adding more processors, as this will enable more requests to be executed simultaneously. If the Processor Queue Length
处理器时间百分比”值一直很高，请首先检查磁盘和网络指标。 如果它们很低，则处理器可能会承受压力。 要确认这一点，请检查平均和当前的“ 处理器队列长度”值。 如果这些值高于建议的值，则明确表明处理器瓶颈。 针对这种情况的最终解决方案是添加更多处理器，因为这将使更多请求可以同时执行。 如果“ 处理器队列长度”值较低（建议在下面给出），请考虑使用功能更强大的处理器
If the disk and network metrics are elevated, start the analysis and troubleshooting with these metrics first
如果磁盘和网络指标升高，请首先使用这些指标开始分析和故障排除
% Processor Time
“ Windows任务管理器”中也显示了“ 处理器时间百分比”
To be able to troubleshoot the processor issues, it’s necessary to know which processor is under stress and what SQL Server instances have issues. You can achieve this by monitoring additional parameters, such as ProcessID and then find the SQL Server instance that had such ProcessID. Another solution is to use a monitoring tool that shows the processor usage per SQL Server instance out-of-the-box
ProcessID） ，然后找到具有该ProcessIDSQL Server实例来实现此目的。 另一个解决方案是使用监视工具，该监视工具可以开箱即用地显示每个SQL Server实例的处理器使用情况。
(Process: % Processor Time)
Windows Performance Monitor offers two counters with similar names Processor: % Processor Time and Process : % Processor Time. It’s important to distinguish between these two metrics and understand the information they show
处理器：％Processor Time和Process：％Processor Time
As described above, % Processor Time
％Processor Time显示处理器在非空闲线程上工作的时间百分比。
The Process: % Processor Time
进程：处理器时间百分比”计数器按每个进程划分处理器时间百分比，因此每个进程在图中显示为单独的项目。 为了获得更有用的结果，请排除空闲线程和总值
The total value for the processes time can be misleading. If the value is 100%, it can mean that all processes are using an equal share of processor time, or that one is using 90%, while others are struggling. That’s why monitoring the processor time for each process is recommended for troubleshooting
处理时间的总价值可能会产生误导。 如果该值为100％，则可能意味着所有进程都使用相等的处理器时间份额，或者一个进程使用90％的进程，而其他进程却在挣扎。 这就是为什么建议监视每个进程的处理器时间以进行故障排除
(Processor Queue Length)
The Processor Queue Length counter shows “a measure of the instantaneous size of the queue for all processors at the moment that the measurement was taken. The resulting value is a measure of how many threads are in the Ready state waiting to be processed.”
处理器队列长度计数器显示“在进行测量时所有处理器的队列瞬时大小的度量。 结果值是多少个线程处于就绪状态等待处理的度量。”
Note that the threads currently running in the processor are not included. Even on a multi-processor machine, there is only one queue for all tasks that are waiting to be processed
请注意，不包括当前在处理器中运行的线程。 即使在多处理器计算机上，对于所有等待处理的任务也只有一个队列
The typical value for this counter is 0 or 1. The recommended value is under 5 per processor. Some DBAs consider the situation to be alarming even when Processor Queue Length is constantly higher than 2. Along with high % Processor Time, a high Processor Queue Length
处理器队列长度始终大于2，这种情况也令人担忧。再加上处理器时间百分比高， 处理器队列长度值高就清楚地表明处理器繁忙。
The Processor Queue Length
处理器队列长度值可能会由于除SQL Server之外的其他应用程序的活动，一台计算机上SQL Server实例数量超过最佳数量，大量的编译和重新编译等原因而增加
A high number of processes waiting to be processed and high CPU usage require immediate attention. Start with checking Compilations/sec and Re-Compilations/sec. There is no specific threshold for these metrics – monitor them for a while and set a baseline for typical behavior. A high number of compilations and recompilations usually indicates poor reuse of the query plans. This can be fixed by optimizing your queries and stored procedures
每秒编译次数”和“每秒 重新编译次数”
However, there are some specific actions (such as creating a compressed full database backup) that use a lot of processor resources and cause other tasks to be queued. These actions cause a temporary peak in the Processor Queue Length. Once you identify these actions, these peaks should not be alarming
处理器队列长度”中的临时峰值。 一旦确定了这些动作，这些峰值就不会令人震惊
Having applications other than SQL Server on your production server is a common reason for having high values for Processor Queue Length. The simplest way to determine if this is the case is to open Windows Task Manager, sort descending the processes in the Process tab by the CPU usage and monitor the top ones. If SQL Server is not among them, other applications are using more processor time than SQL Server and affecting its performance. The only solution is to remove them or use a dedicated machine for SQL Server only
处理器队列长度”值较高的常见原因。 确定是否为这种情况的最简单方法是打开Windows任务管理器，按CPU使用率对“进程”选项卡中的进程进行降序排序，并监视最前面的进程。 如果其中不包括SQL Server，则其他应用程序使用的处理器时间比SQL Server多，并影响其性能。 唯一的解决方案是删除它们或仅将专用计算机用于SQL Server
The Processor Queue Length
处理器队列长度”计数器显示相关值，因此可用于指示虚拟机及其承载SQL Server实例上的处理器资源不足
The % Processor Time and Processor Queue Length
处理器时间百分比和处理器队列长度计数器是最重要的处理器性能指标。 它们指示处理器使用的强度以及要处理的线程是否超过处理器可以处理的数量。 如果删除其他使用处理器资源的应用程序并优化SQL查询和存储过程不能解决这两个计数器指示的处理器瓶颈，请考虑使用功能更强大的处理器，或添加其他处理器
翻译自: https://www.sqlshack.com/sql-server-processor-performance-metrics-part-1-important-cpu-metrics/
t–sql pl–sql
