MS SQL Server Clients - EXECUTE msdb.dbo.sp_sqlagent_get_perf

Author

EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

Sachin Surana

2005-05-12, 11:23 am

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_conditio
n 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(performanc
e_condition, 1, CHARINDEX('|',
performance_conditio
n, PATINDEX('%[_|_]%', performance_conditio
n) + 1) - 1)
FROM msdb.dbo.sysalerts
WHERE (performance_conditi
on 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_conditio
n = RTRIM(spi1.object_name) + '|' +
RTRIM(spi1.counter_name)))
----------------------------------------------------------------------
sp_verify_job_identi
fiers '@job_name',
'@job_id',
@job_name OUTPUT,
@job_id OUTPUT,
'NO_TEST'

Anith Sen

2005-05-12, 11:23 am

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 PerformanceSamplingI
nterval in MSSQLServer\SQLServe
rAgent 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

Sachin Surana

2005-05-12, 11:23 am

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 PerformanceSamplingI
nterval in MSSQLServer\SQLServe
rAgent 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
>
>
>

Anith Sen

2005-05-12, 1:23 pm

>> Do these alerts affect the performance of any other query running in[color=darkred]

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.
[color=darkred]

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

Sachin Surana

2005-05-13, 3:23 am

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'ser
vername'))
-- SELECT ISNULL(SUSER_SNAME()
, SUSER_NAME())
-- EXECUTE msdb.dbo.sp_help_jobstep @job_id =
0x63314D2F34B5AC449E
29CF142843F50F
-- EXECUTE @retval = sp_verify_job_identi
fiers '@job_name',
'@job_id',
@job_name OUTPUT,
@job_id OUTPUT,
'NO_TEST'
-- EXEC dbo. sp_MSdistribution_cl
eanup @min_distretention = 0,
@max_distretention = 72
-- exec @retcode = dbo. sp_MSsubscription_cl
eanup @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
>
>
>

Sponsored Links