Stored procedure sp_sqlagent_get_perf_counters uses too much cpu t

15 Nov 2007 11:49 PM

Erick Korsten

On our development server I noticed that there is a SQL Agent process running
that executes the stored procedure sp_sqlagent_get_perf_counters every 20
seconds. This process is using a lot of cpu time.
I found out that this process is related to the SQL Agent alerts and that
removing all the alerts is a way to prevent this stored procedure from
executing.
In our case we use our own alerts for maintenance related processes, so we
can not delete the alerts.
Is there a way to prevent this stored procedure from using so much cpu time?

16 Nov 2007 10:32 AM

Tibor Karaszi

Are you using any performance condition alerts? If not, you can modify the registry. But default it
is every 20 seconds. You can change that. It is possible that the value 0 mean it doesn't run at
all, but you have to test it. Otherwise, you can increase the interval to your liking. But this will
of course affect if you do have any performance condition alerts.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quoteHide quote

"Erick Korsten" <ErickKors***@discussions.microsoft.com> wrote in message
news:DD3D87C2-F37B-4A1F-807D-1EEB232CAD79@microsoft.com...
> On our development server I noticed that there is a SQL Agent process running
> that executes the stored procedure sp_sqlagent_get_perf_counters every 20
> seconds. This process is using a lot of cpu time.
> I found out that this process is related to the SQL Agent alerts and that
> removing all the alerts is a way to prevent this stored procedure from
> executing.
> In our case we use our own alerts for maintenance related processes, so we
> can not delete the alerts.
> Is there a way to prevent this stored procedure from using so much cpu time?

16 Nov 2007 4:54 PM

Erick Korsten

Thanks Tibor.
I do not have any performance condition alerts, so modifying the registry
key fixes the problem. It took me some time to find the key since I have both
2000 and 2005 on my machine. The registry key for my SQL 2000 instance =
HKLM\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\PerformanceSamplingInterval.
The registry key for my SQL 2005 instance =
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\SQLServerAgent\PerformanceSamplingInterval.

Show quoteHide quote

"Tibor Karaszi" wrote:

> Are you using any performance condition alerts? If not, you can modify the registry. But default it
> is every 20 seconds. You can change that. It is possible that the value 0 mean it doesn't run at
> all, but you have to test it. Otherwise, you can increase the interval to your liking. But this will
> of course affect if you do have any performance condition alerts.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Erick Korsten" <ErickKors***@discussions.microsoft.com> wrote in message
> news:DD3D87C2-F37B-4A1F-807D-1EEB232CAD79@microsoft.com...
> > On our development server I noticed that there is a SQL Agent process running
> > that executes the stored procedure sp_sqlagent_get_perf_counters every 20
> > seconds. This process is using a lot of cpu time.
> > I found out that this process is related to the SQL Agent alerts and that
> > removing all the alerts is a way to prevent this stored procedure from
> > executing.
> > In our case we use our own alerts for maintenance related processes, so we
> > can not delete the alerts.
> > Is there a way to prevent this stored procedure from using so much cpu time?
>
>
>

Thread options