Why is this an important SQL Server Performance Indicator?
Delta Bravo uses this metric to determine whether the processor problems originate from internal Windows processes, or are caused by a user application. If Delta Bravo identifies high CPU usage on a SQL Server instance, the next step is to narrow down the high CPU problem to the lowest possible level–the component which is causing high CPU.
The CPU % vs. Process Privileged Time (Total) counter helps Delta Bravo understand the time and energy the system spends on Windows kernel commands (SQL Server I/O requests). If the CPU % vs. Process Privileged Time value is high, kernel mode processes are using a lot of processor time, the machine is busy executing basic operating system tasks and cannot run user processes and other applications, such as SQL Server. The recommended values for CPU@ vs. Process Privilege Time are 5 to 10%, or maximum 20% of the % Total Processor Time.
Do you know what’s keeping your processor busy?
There are two different states to be aware of when talking about processors executing instructions: Privileged mode and User mode. Some operating system threads and interrupts (including all device driver functions) as well as Kernel-mode threads execute in privileged mode.
When dealing with Privileged mode operations, there are two modes to consider – Interrupt mode and Deferred Procedure Call (DPC) mode. Interrupt mode is reserved for interrupt service routines which are device driver functions. When looking at this in Performance Monitor, % Interrupt Time is the time the processor spends receiving and servicing hardware interrupts during sample intervals. This value is an indirect indicator of the activity of devices that generate interrupts, such as the system clock, the mouse, disk drivers, data communication lines, network interface cards and other peripheral devices. These devices normally interrupt the processor when they have completed a task or require attention. Normal thread execution is suspended during interrupts. Most system clocks interrupt the processor every 10 milliseconds, creating a background of interrupt activity. DPC mode is time spent in routines known as deferred procedure calls – these are routines scheduled by device drivers to complete interrupt processing. DPC’s are often referred to as soft interrupts. From the Performance Monitor perspective, the % DPC Time counter shows the percentage of the time that the system was executing in DPC mode. Measuring these counters separately can provide insight into whether there are issues with the interrupt service routine or its DPC.
Taking Action with Delta Bravo
If you noticed that the % Interrupt Time counter is much higher, you may have a problem with a device driver or piece of hardware. Comparing the Interrupts / sec counter between the baseline and your current performance log, if the current rate is proportional to the level in the baseline, then the device driver code is the most likely culprit. If the Interrupt rate is significantly higher, you are probably experiencing a hardware issue.