While troubleshooting High CPU conditions on a SQL Server instance, many of us would have stumbled upon a session which was executing sp_sqlagent_get_perf_counters stored procedure. As the stored procedure name suggests this is executed approximately every 20 seconds to get the Performance Counters’ values defined in SQL Server. Based on the values of the counters, the defined Alerts will be triggered.
But at times even though no Alerts are defined on the instance, this particular stored procedure hogs CPU. Why? Because when SQL Server is installed, Demo Alerts are created by default.
These alerts are which demonstrate how SQL Server Alerts work and are enabled by default.
Since SQL Server polls the Performance Counters at regular intervals, these may cause High CPU conditions. What is the fix? Just delete those Demo Alerts. Why delete them when there is an option to Disable these alerts? As per this feedback on Connect, even when the alerts are disabled, SQL Server continues to poll them!
The next time you see this stored procedure consuming more CPU, check if there are any unwanted alerts configured on the instance and delete them.