EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

Hi All,
We are running a job for archiving old data. The job calls a stored
procedure. The data gets copied successfully to a different database but when
a query is run to delete the data from the source database, it does not get
completed.
What is it trying to do ? Why does it not move forward?
The profiles shows the following continuously...
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- sp_sqlagent_get_perf_counters
SET NOCOUNT ON
-- sp_sqlagent_get_perf_counters
CREATE TABLE #temp
(
performance_condition NVARCHAR(1024) COLLATE database_default NOT NULL
)
-- sp_sqlagent_get_perf_counters
INSERT INTO #temp VALUES (N'dummy')
IF (@.all_counters = 0)
INSERT INTO #temp
SELECT DISTINCT SUBSTRING(performance_condition, 1, CHARINDEX('|',
performance_condition, PATINDEX('%[_|_]%', performance_condition) + 1) - 1)
FROM msdb.dbo.sysalerts
WHERE (performance_condition IS NOT NULL)
AND (enabled = 1)
SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
'instance_name' = CASE spi1.instance_name
WHEN N'' THEN NULL
ELSE RTRIM(spi1.instance_name)
END,
'value' = CASE spi1.cntr_type
WHEN 537003008 -- A ratio
THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE
spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
FROM
master.dbo.sysperfinfo spi2
WHERE
(spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('%
Base%', spi2.counter_name)))
AND
(spi1.instance_name = spi2.instance_name)
AND
(spi2.cntr_type = 1073939459))
ELSE spi1.cntr_value
END
FROM master.dbo.sysperfinfo spi1,
#temp tmp
WHERE (spi1.cntr_type <> 1073939459) -- Divisors
AND ((@.all_counters = 1) OR
(tmp.performance_condition = RTRIM(spi1.object_name) + '|' +
RTRIM(spi1.counter_name)))
sp_verify_job_identifiers '@.job_name',
'@.job_id',
@.job_name OUTPUT,
@.job_id OUTPUT,
'NO_TEST'
sp_sqlagent_get_perf_counters is a system procedure which feeds alerts for
SQL Agent service. There might be an entry in the SQL Server registry under
the key PerformanceSamplingInterval in MSSQLServer\SQLServerAgent which sets
the sampling interval. You can reduce this default by changing the value
there.
Alternatively you can totally avoid them by removing all the alerts set in
SQL Server( some of them are demo alerts set by SQL Server install by
default). Goto EM, under management, SQL Server Agent, delete all the
alerts. If you have no alerts the procedure will not run.
Anith
|||Hi Anith,
Thanks for a quick reply Do these alerts affect the performance of any
other query running in parallel ? One of our queries to delete bulk records
is taking a helll lot of time and is not returning, profiler traces show that
the query has started and then a large number of these
sp_sqlagent_get_perf_counters are shown, can you give some more inputs?
Regards
Sachin
"Anith Sen" wrote:

> sp_sqlagent_get_perf_counters is a system procedure which feeds alerts for
> SQL Agent service. There might be an entry in the SQL Server registry under
> the key PerformanceSamplingInterval in MSSQLServer\SQLServerAgent which sets
> the sampling interval. You can reduce this default by changing the value
> there.
> Alternatively you can totally avoid them by removing all the alerts set in
> SQL Server( some of them are demo alerts set by SQL Server install by
> default). Goto EM, under management, SQL Server Agent, delete all the
> alerts. If you have no alerts the procedure will not run.
> --
> Anith
>
>
|||>> Do these alerts affect the performance of any other query running in[vbcol=seagreen]
Generally, it should be negligible. However, you can check the trace file to
see how often this procedure is being run. If it is being run every 5 sec,
or 10 sec depending on the polling interval, then in heavy transaction
oriented systems, it might have some impact.
[vbcol=seagreen]
I do not have a first hand experience of how it impacts huge deletes, but on
a related note, do you have a profiler trace running 24/7 on the production
machine? In transaction-heavy systems, that itself can have dampening effect
on the overall performance.
Anith
|||No we do not have profiler running on production. Its just on the test
environment.
Not sure what is it trying to do?
And yes, I checked for msdb.dbo.sp_sqlagent_get_perf_counters ? Its taking
around 15-16 ms
Please find below some other traces as well.
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- SELECT N'Testing Connection...'
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- SELECT N'Testing Connection...'
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
...
...
-- SET TEXTSIZE 64512
-- select @.@.microsoftversion
-- select convert(sysname, serverproperty(N'servername'))
-- SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
-- EXECUTE msdb.dbo.sp_help_jobstep @.job_id =
0x63314D2F34B5AC449E29CF142843F50F
-- EXECUTE @.retval = sp_verify_job_identifiers '@.job_name',
'@.job_id',
@.job_name OUTPUT,
@.job_id OUTPUT,
'NO_TEST'
-- EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72
-- exec @.retcode = dbo.sp_MSsubscription_cleanup @.cutoff_time
Thanks,
Sachin
"Anith Sen" wrote:

> Generally, it should be negligible. However, you can check the trace file to
> see how often this procedure is being run. If it is being run every 5 sec,
> or 10 sec depending on the polling interval, then in heavy transaction
> oriented systems, it might have some impact.
>
> I do not have a first hand experience of how it impacts huge deletes, but on
> a related note, do you have a profiler trace running 24/7 on the production
> machine? In transaction-heavy systems, that itself can have dampening effect
> on the overall performance.
> --
> Anith
>
>