The OS Processor Time (Percent) alert provides the percentage of CPU time used by all processes on the computer hosting your SQL Server instance. The WMI object Win32_PerfRawData_PerfOS_Processor property PercentProcessorTime is measured to get percent processor time.

Reduce OS processor time

If this value regularly exceeds 75%, you should take action such as:

  • Reduce the number of SQL re-compilations since they are CPU intensive. There are many reasons that an object such as a stored procedure is recompiled and you can remove most of these reasons by careful coding. See the SQL Re-compilations counter for ways to dramatically reduce recompiles.
  • Make sure that all T-SQL statements (whether in a stored procedure, trigger, or ad hoc statement) that reference objects fully qualify the object referenced. For example: SELECT * FROM Northwind.dbo Employees is a fully-qualified object reference whereas SELECT * FROM Employees is a poorly-qualified object. You can reuse the execution plans of fully-qualified objects "as is," whereas plans where you either cannot reuse the not fully-qualified objects or, if they are reused, then they are subject to a highly restrictive COMPILE lock while SQL Server determines if all of the objects referenced in the T-SQL code have the same owners as the execution plan currently in cache. Both of these situations consume a significant amount of CPU time.
  • Stop unnecessary programs (such as the Print Spooler or activities such as the database server acting as either a BDC or PDC) from running.

Reasons why the CPU Usage metric has no value

SQL Diagnostic Manager is unable to collect the corresponding counter due to one of the following reasons:

  • It usually takes two refreshes to begin populating the CPU Usage metric, which occurs within a few seconds.
  • OS Metrics collection is disabled or not working properly. You must enable OS Metrics collection for SQL Diagnostic Manager to collect hardware performance metrics.
  • Lightweight Pooling is enabled. SQL Diagnostic Manager cannot collect OS metrics if Lightweight Pooling is enabled on the SQL Server instance.
  • Alerts are disabled. SQL Diagnostic Manager displays an alert status when at least one of the three alerts is enabled.
  • The counter does not exist on this SQL Server instance.

To enable alerting when this metric is outside its established baseline, click the Baseline Thresholds Enabled (as percentage of baseline) check box in the Alert Configuration window.



IDERA | Products | Purchase | Support | Community | Resources | About Us | Legal