Chat now with support
Chat with Support

Spotlight on SQL Server Enterprise 11.7 - Management Pack for SCOM User Guide

CPU - Processor Queue Length Alarm

The CPU - Processor Queue Length alarm becomes active when the number of Windows threads waiting for CPU resources exceeds a threshold. This value is taken over a specific number of background collections. Sustained high processor queue length is a good indicator that you have a CPU bottleneck.

Note that since this is a Windows-based alarm, it can include CPU that was consumed by non-SQL Server processes.

TIP: When monitoring the Spotlight Diagnostic Server host, the Processor Queue Length shown here can be artificially high, especially on single processor machines. Since Spotlight on SQL Server is a multi-threaded application, the process of running its data collection queries causes extra Spotlight client threads to be scheduled for execution, resulting in a higher than normal Processor Queue Length figure and the raising of this alarm.

When the alarm is raised

  • For the connection to the Windows server host, look at the Processor Queue Length chart on the Windows Summary page of the Windows Server | Activity Drilldown. This will show you how long the Processor Queue Length has been high. A long sustained queue length indicates a processor bottleneck - leading to overall system degradation.
  • For the connection to the Windows server host, look at the Processes page on the Windows Server | Processes Drilldown to see which Windows process is consuming the CPU. SQL Server is shown as the process called sqlservr.
  • Look at the Summary page on the SQL Server | SQL Activity Drilldown to determine how much of the CPU is being consumed by SQL Server.
  • If you determine from the steps above that SQL Server is consuming most of the CPU, look at the Sessions tab on the SQL Server | SQL Activity Drilldown to see which SQL Server users are currently executing SQL, and to determine the exact SQL that is running. If necessary, use the Session Trace sub-tab on active sessions.
  • On the Sessions page, select the sessions that have the highest CPU figure to see the exact SQL statement that the user is currently running. If necessary, use the Session Trace sub-tab to see further information about the active sessions.

Consider

  • Adding an additional CPU to the system – the waiting threads will be load balanced between all available processors.
  • Upgrading to a faster CPU. This will allow the CPU to process threads faster, but may not reap the same rewards as adding an additional CPU.
  • Moving some processing activities to another system.

If SQL Server is consuming most of the CPU and your system supports many concurrent users, you might benefit from SQL Server's lightweight pooling option, which causes SQL Server sessions to be scheduled as fibers rather than threads. On some heavily-loaded systems, this can save a small amount of CPU. Ensure you test this properly, because on some systems it can actually increase SQL Server's CPU demands.

This option can be changed by using the SQL Server | Configuration Drilldown. Changes to this option do not take effect until the SQL Server instance is stopped and restarted.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating