Auto update statistics

I've seen conflicting information about having auto-update statistics. On
view was that having this on would cause spikes in utilization whenever the
auto update kicks in during peak load time, and so this option should be
turned off and update statistics should be run via a job during off times.
The other view was that it didn't matter.
Any thoughts?
Thanks
Bob Castleman
SuccessWare Software
Bob Castleman wrote:
> I've seen conflicting information about having auto-update
> statistics. On view was that having this on would cause spikes in
> utilization whenever the auto update kicks in during peak load time,
> and so this option should be turned off and update statistics should
> be run via a job during off times. The other view was that it didn't
> matter.
> Any thoughts?
> Thanks
> Bob Castleman
> SuccessWare Software
You're right that there is no clear concensus. Some customers do perform
statistics updates after hours. They may be doing this because the
database was around during the SQL 7 days or because the amount of data
loaded into tables during peak hours does not generall affect the
statistical distribution of data in those tables.
If you have tables that are effected by large loads during work hours
and are worried that queries that access those tables are likely to use
inefficient plans because of outdated statistics, then keep the option
on. Certainly, running a bunch of inefficient queries will put more
stress on the server than allowing SQL Server to update statistics as it
sees fit. OTOH, if your tables are unaffected by large changes during
the day, you can update statistics at night (daily, weekly, or monthly
as you see fit).
It all depends on your data and the queries hitting your data.
What is you situation?
David Gugick
Imceda Software
www.imceda.com
|||Bob,
Yes, I come up against this a lot. I am a freelance consultant and I
hear both sides of the argument - the "correct" answer is that it
depends on your environment. If leaving auto-update stats on is
acceptable to the users -- ie they perceive no degradation in
performance -- then leave it on.
Unless your system is processing many transactions per second - say
10-100 then perhaps turn it off and schedule update stats manually in a
maintenance window.
Some places I go to have 24x7 shops where there simply isn't a
maintenance window, so they just leave auto-update stats on all the time
anyway. You need to base your decision on how it affects your users.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob Castleman wrote:
> I've seen conflicting information about having auto-update statistics. On
> view was that having this on would cause spikes in utilization whenever the
> auto update kicks in during peak load time, and so this option should be
> turned off and update statistics should be run via a job during off times.
> The other view was that it didn't matter.
> Any thoughts?
> Thanks
> Bob Castleman
> SuccessWare Software
>
|||Our production environment hosts about 125 of our clients in a Citrix farm
against two Active/Passive clusters. Probably 90% of the load occurs between
8:00 AM and 6:00 PM and we have a defined maintenance window of 12:00 AM to
5:00 AM. No single client has huge processing needs, but the aggregate load
can get pretty heavy. We recently solved a problem that was sucking up
proccessor cycles and limited the number of clients on a database server. We
want to increase the number of clients per database server, so I am trying
to identify things I can do before hand that might help keep things under
control. We expect to triple or quadruple the number of clients we are
hosting over the next 24 to 36 months so I am trying to get everything as
stable as possible now instead of waiting for fires to start.
We are also looking at tools for helping us profile usage patterns and
things like that so we can start optimizing the queries and indexing. The
application was not designed for a hosted environment and query optimization
has been ad hoc in the past. It is highly likely that there are numerous
inefficient queries.
Thx,
Bob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uxFc8EX4EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Bob Castleman wrote:
> You're right that there is no clear concensus. Some customers do perform
> statistics updates after hours. They may be doing this because the
> database was around during the SQL 7 days or because the amount of data
> loaded into tables during peak hours does not generall affect the
> statistical distribution of data in those tables.
> If you have tables that are effected by large loads during work hours and
> are worried that queries that access those tables are likely to use
> inefficient plans because of outdated statistics, then keep the option on.
> Certainly, running a bunch of inefficient queries will put more stress on
> the server than allowing SQL Server to update statistics as it sees fit.
> OTOH, if your tables are unaffected by large changes during the day, you
> can update statistics at night (daily, weekly, or monthly as you see fit).
> It all depends on your data and the queries hitting your data.
> What is you situation?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Our transaction rate runs 500-1000 per second. (see my response to Dave
Guigick for more info on our production environment)
It sounds like this is a peripheral issue, rather than one that will show
any significant change. I probably am better off digging deeper into
optimization of the queries and stuff.
Thx,
Bob
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:ejzUXsc4EHA.3820@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Bob,
> Yes, I come up against this a lot. I am a freelance consultant and I hear
> both sides of the argument - the "correct" answer is that it depends on
> your environment. If leaving auto-update stats on is acceptable to the
> users -- ie they perceive no degradation in performance -- then leave it
> on.
> Unless your system is processing many transactions per second - say 10-100
> then perhaps turn it off and schedule update stats manually in a
> maintenance window.
> Some places I go to have 24x7 shops where there simply isn't a maintenance
> window, so they just leave auto-update stats on all the time anyway. You
> need to base your decision on how it affects your users.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Bob Castleman wrote:
|||Bob,
OK, that's quite healthy! :-)
If these transactions are DML statements then you may find that
auto-update stats has an effect on performance. This effect could be
positive or negative! NOT switching on auto-update stats may actually be
negative in such a high transaction environment. Is there any way you
can take a profiler trace and replay it into a test environment and
benchmarking your system with and without auto-update stats? You will
know for sure this way how it is going to affect you.
Obviously you will need to have similar spec hardware - not sure how
feasible this is. You may also wish to connect a typical user
application to this test environment while the trace is playing to see
how well it "feels".
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob Castleman wrote:
> Our transaction rate runs 500-1000 per second. (see my response to Dave
> Guigick for more info on our production environment)
> It sounds like this is a peripheral issue, rather than one that will show
> any significant change. I probably am better off digging deeper into
> optimization of the queries and stuff.
|||Bob,
Is it possible for you to email me directly? I have a question for you but
don't know how to reach you.
Please remove the "nooospam" from my reply address.
Thanks
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:uTl8I7e4EHA.1260@.TK2MSFTNGP12.phx.gbl...
> Our production environment hosts about 125 of our clients in a Citrix farm
> against two Active/Passive clusters. Probably 90% of the load occurs
> between 8:00 AM and 6:00 PM and we have a defined maintenance window of
> 12:00 AM to 5:00 AM. No single client has huge processing needs, but the
> aggregate load can get pretty heavy. We recently solved a problem that was
> sucking up proccessor cycles and limited the number of clients on a
> database server. We want to increase the number of clients per database
> server, so I am trying to identify things I can do before hand that might
> help keep things under control. We expect to triple or quadruple the
> number of clients we are hosting over the next 24 to 36 months so I am
> trying to get everything as stable as possible now instead of waiting for
> fires to start.
> We are also looking at tools for helping us profile usage patterns and
> things like that so we can start optimizing the queries and indexing. The
> application was not designed for a hosted environment and query
> optimization has been ad hoc in the past. It is highly likely that there
> are numerous inefficient queries.
> Thx,
> Bob
>
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uxFc8EX4EHA.2012@.TK2MSFTNGP15.phx.gbl...
>
|||We are actually in the process of creating a test environment that includes
a mini Citrix farm and a clustered SQL setup for precisely this purpose.
It's still not complete. I'll probably put the auto-update issue on a task
list and try addressing it as you suggest. Turning it off just to see what
happens sounds too risky and it makes much more sense to work out a
reasonable test.
Thx,
Bob
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23U2Xyaf4EHA.2180@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Bob,
> OK, that's quite healthy! :-)
> If these transactions are DML statements then you may find that
> auto-update stats has an effect on performance. This effect could be
> positive or negative! NOT switching on auto-update stats may actually be
> negative in such a high transaction environment. Is there any way you can
> take a profiler trace and replay it into a test environment and
> benchmarking your system with and without auto-update stats? You will know
> for sure this way how it is going to affect you.
> Obviously you will need to have similar spec hardware - not sure how
> feasible this is. You may also wish to connect a typical user application
> to this test environment while the trace is playing to see how well it
> "feels".
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Bob Castleman wrote: