Re: Auto update statistics

From: Bob Castleman (nomail_at_here)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 10:16:07 -0500

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:
>> 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

Relevant Pages

  • Re: Auto update statistics
    ... We recently solved a problem that was> sucking up proccessor cycles and limited the number of clients on a> database server. ... It is highly likely that there> are numerous inefficient queries. ... 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. ... running a bunch of inefficient queries will put more>> stress on the server than allowing SQL Server to update statistics as it ...
    (microsoft.public.sqlserver.server)
  • Re: How to determine if a number is statistically meaningful
    ... I have the total number of clients over a year, ... There is not any magic in "statistics" to determine a threshold. ... movie, then look for information. ... No score is reported if the Movie/ person has fewer votes ...
    (sci.stat.math)
  • [Patch] statistics infrastructure - update 6
    ... statistics infrastructure with sampled data for the statistics maintained by the ... -used by exploiters for other purposes. ... +used by clients for other purposes. ... * @stat: struct statistic array ...
    (Linux-Kernel)
  • Re: login scripting
    ... unless you're using Windows 98 or something equally ... horrid on the clients. ... > applications that are in the startup group load with no problems. ... > Once the login script completes, the apps load fine, however the users are ...
    (microsoft.public.win2000.general)
  • Re: Design question
    ... > my design may be just simply counting active CAOs to determine work loads. ... there would be no need for all the computation of load and communication to ... The clients are hard-coded against the external IP (or a host name that maps ... router so all traffic is routed to the new server. ...
    (microsoft.public.dotnet.framework.remoting)