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 whereasSELECT * 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 restrictiveCOMPILE
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
SQLdm 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 SQLdm to collect hardware performance metrics.
- Lightweight Pooling is enabled. SQLdm cannot collect OS metrics if Lightweight Pooling is enabled on the SQL Server instance.
- Alerts are disabled. SQLdm displays an alert status when at least one of the three alerts is enabled.
- The counter does not exist on this SQL Server instance.