471,052 Members | 1,360 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,052 software developers and data experts.

Updating statistics

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
Jul 20 '05 #1
4 2604
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

Jul 20 '05 #2
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


Jul 20 '05 #3
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



Jul 20 '05 #4
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
>
>



Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Sky Fly | last post: by
3 posts views Thread by Metal Dave | last post: by
5 posts views Thread by Allan Ebdrup | last post: by
2 posts views Thread by cccompton via DBMonster.com | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.