The OS Disk Time (Percent) alert provides the percentage of elapsed time that all disks are busy servicing read and write requests on the SQL Server computer.
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
Employeesis a fully-qualified object reference whereas
SELECT * FROM Employeesis 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
COMPILElock 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.
Enable baseline thresholds
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.
Create an alert reponse bundle
Create an alert response bundle with the OS Disk Time (Percent) alert and related alerts. For additional information, see Create alert response bundles.