Monitor an instance of Microsoft SQL Server periodically to determine whether CPU usage rates are within normal ranges. A continually high rate of CPU usage can indicate the need to upgrade the CPU or add multiple processors. A high CPU usage rate can also indicate a poorly tuned or designed application.
Optimising the application can lower CPU usage.
The counters used to determine whether CPU usage rates for SQL Server are within normal ranges are Processor: % Privileged Time & Processor: % User Time
- Processor: % Privileged Time – corresponds to the percentage of time the processor spends on execution of Microsoft Windows kernel commands, such as processing SQL Server I/O requests. If this counter is consistently high when the Physical Disk counters are high, consider installing a faster or more efficient disk subsystem.
- Processor: % User Time - corresponds to the percentage of time that the processor spends on executing user processes such as SQL Server.
The Buffer Manager object provides counters to monitor how SQLServer uses the buffer pool such as:
- Page Reads/sec - measures the number of physical database page reads that are issued per second.
- Page Writes/sec - measures the number of physical database page writes that are issued per second.
PhysicalDisk: % Disk Time – measures the percentage of time that the disk is busy with read/write activity
Page writes/sec indicates the number of physical database page writes that are issued per second.
Procedure cache pages - Number of pages used to store compiled queries.
LogicalDisk|PhysicalDisk\% Idle Time reports the percentage of time that the disk system was not processing requests and no work was queued.
Processor Queue Length corresponds to the number of threads waiting for processor time. A processor bottleneck develops when threads of a process require more processor cycles than are available. If more than a few processes attempt to use the processor’s time, we might need to install a faster processor. Alternatively, if we have a multiprocessor system, we could add a processor.