Updating statistics

[Sky Fly]

Hello all,

I've written a stored procedure which runs OK for the
first few thousand records it processes, then around
about the 10,000th record it suffers a sudden and dramatic
drop in performance (from about 40 records per second to
about 1 per second).

I've found that when this happens, if I run an UPDATE STATISTICS
query on the affected tables, performance picks up again,
which is good. However, this is a query that will be
running unattended, so I thought that I good idea would
be to put the UPDATE STATISTICS statement in the stored
procedure and have it run after about eight thousand records
have been processed.

However, I find that even though the statement is run
after the 8,000th record, the performance drop *still*
occurs after the 10,000th record. I find this odd because
the statistics have just recently been updated. Is there
anything else I should be looking at?

TIA,

--
Akin

aknak at aksoto dot idps dot co dot uk

[Uri Dimant]

Sky
Have you looked at execution plan of the query? Does the query optimizer
available to use indexes which defined on the table?
"Sky Fly" <no****@blackhole.com> wrote in message
news:c6************@ID-18325.news.uni-berlin.de...

Hello all,

I've written a stored procedure which runs OK for the
first few thousand records it processes, then around
about the 10,000th record it suffers a sudden and dramatic
drop in performance (from about 40 records per second to
about 1 per second).

I've found that when this happens, if I run an UPDATE STATISTICS
query on the affected tables, performance picks up again,
which is good. However, this is a query that will be
running unattended, so I thought that I good idea would
be to put the UPDATE STATISTICS statement in the stored
procedure and have it run after about eight thousand records
have been processed.

However, I find that even though the statement is run
after the 8,000th record, the performance drop *still*
occurs after the 10,000th record. I find this odd because
the statistics have just recently been updated. Is there
anything else I should be looking at?

TIA,

--
Akin

aknak at aksoto dot idps dot co dot uk

[Sky Fly]

OK,

I think I've found out what's causing the problem. After examining
a trace I ran on the db, I found out that at the times when the
performance drops, a system stored procedure - sp_sqlagent_get_perf_counters
- is running.

What I would like to know is: what does this sp do? Why does it run?
Is it safe to stop it or prevent it from running on occasion?
How do I do this?

TIA,

--
Akin

aknak at aksoto dot idps dot co dot uk

"Uri Dimant" <ur**@iscar.co.il> wrote in message
news:u9**************@tk2msftngp13.phx.gbl...

Sky
Have you looked at execution plan of the query? Does the query optimizer
available to use indexes which defined on the table?
"Sky Fly" <no****@blackhole.com> wrote in message
news:c6************@ID-18325.news.uni-berlin.de...

Hello all,

I've written a stored procedure which runs OK for the
first few thousand records it processes, then around
about the 10,000th record it suffers a sudden and dramatic
drop in performance (from about 40 records per second to
about 1 per second).

I've found that when this happens, if I run an UPDATE STATISTICS
query on the affected tables, performance picks up again,
which is good. However, this is a query that will be
running unattended, so I thought that I good idea would
be to put the UPDATE STATISTICS statement in the stored
procedure and have it run after about eight thousand records
have been processed.

However, I find that even though the statement is run
after the 8,000th record, the performance drop *still*
occurs after the 10,000th record. I find this odd because
the statistics have just recently been updated. Is there
anything else I should be looking at?

TIA,

--
Akin

aknak at aksoto dot idps dot co dot uk

[Uri Dimant]

Sky
By running this stored procedure SQLAgent is determined if there are any
thresholds changed for
the alerting system. However you can change the interval by editing registry
key. ( May be danger)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\Server_Name\SQLServerAgent\PerformanceSampl ingInterval
"Sky Fly" <no****@blackhole.com> wrote in message
news:c6************@ID-18325.news.uni-berlin.de...

OK,

I think I've found out what's causing the problem. After examining
a trace I ran on the db, I found out that at the times when the
performance drops, a system stored procedure - sp_sqlagent_get_perf_counters - is running.

What I would like to know is: what does this sp do? Why does it run?
Is it safe to stop it or prevent it from running on occasion?
How do I do this?

TIA,

--
Akin

aknak at aksoto dot idps dot co dot uk

"Uri Dimant" <ur**@iscar.co.il> wrote in message
news:u9**************@tk2msftngp13.phx.gbl...

Sky
Have you looked at execution plan of the query? Does the query optimizer
available to use indexes which defined on the table?
"Sky Fly" <no****@blackhole.com> wrote in message
news:c6************@ID-18325.news.uni-berlin.de...

Hello all,

I've written a stored procedure which runs OK for the
first few thousand records it processes, then around
about the 10,000th record it suffers a sudden and dramatic
drop in performance (from about 40 records per second to
about 1 per second).

I've found that when this happens, if I run an UPDATE STATISTICS
query on the affected tables, performance picks up again,
which is good. However, this is a query that will be
running unattended, so I thought that I good idea would
be to put the UPDATE STATISTICS statement in the stored
procedure and have it run after about eight thousand records
have been processed.

However, I find that even though the statement is run
after the 8,000th record, the performance drop *still*
occurs after the 10,000th record. I find this odd because
the statistics have just recently been updated. Is there
anything else I should be looking at?

TIA,

--
Akin

aknak at aksoto dot idps dot co dot uk

[Sky Fly]

Hello Uri,

I tried changing this setting in the registry to 0; I tried changing
it to a very large value; I tried deleting the alerts in EM;
all made no difference.

So I've decided that I will call update statistics every 10 seconds
or so. This appears to minimise the original problem, but I don't
know if it will have any effect on the performance of the database.
Can you advise?

"Uri Dimant" <ur**@iscar.co.il> wrote in message
news:uA*************@tk2msftngp13.phx.gbl...

Sky
By running this stored procedure SQLAgent is determined if there are any
thresholds changed for
the alerting system. However you can change the interval by editing registry key. ( May be danger)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\Server_Name\SQLServerAgent\PerformanceSampl ingInterval
"Sky Fly" <no****@blackhole.com> wrote in message
news:c6************@ID-18325.news.uni-berlin.de...

OK,

I think I've found out what's causing the problem. After examining
a trace I ran on the db, I found out that at the times when the
performance drops, a system stored procedure -

sp_sqlagent_get_perf_counters

- is running.

What I would like to know is: what does this sp do? Why does it run?
Is it safe to stop it or prevent it from running on occasion?
How do I do this?

TIA,

--
Akin

aknak at aksoto dot idps dot co dot uk

"Uri Dimant" <ur**@iscar.co.il> wrote in message
news:u9**************@tk2msftngp13.phx.gbl...

Sky
Have you looked at execution plan of the query? Does the query optimizer available to use indexes which defined on the table?
"Sky Fly" <no****@blackhole.com> wrote in message
news:c6************@ID-18325.news.uni-berlin.de...
> Hello all,
>
> I've written a stored procedure which runs OK for the
> first few thousand records it processes, then around
> about the 10,000th record it suffers a sudden and dramatic
> drop in performance (from about 40 records per second to
> about 1 per second).
>
> I've found that when this happens, if I run an UPDATE STATISTICS
> query on the affected tables, performance picks up again,
> which is good. However, this is a query that will be
> running unattended, so I thought that I good idea would
> be to put the UPDATE STATISTICS statement in the stored
> procedure and have it run after about eight thousand records
> have been processed.
>
> However, I find that even though the statement is run
> after the 8,000th record, the performance drop *still*
> occurs after the 10,000th record. I find this odd because
> the statistics have just recently been updated. Is there
> anything else I should be looking at?
>
> TIA,
>
> --
> Akin
>
> aknak at aksoto dot idps dot co dot uk
>
>