Re: Auto update statistics

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 13:20:22 -0500

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...
>> 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
    ... Our production environment hosts about 125 of our clients in a Citrix farm ... No single client has huge processing needs, but the aggregate load ... >> and so this option should be turned off and update statistics should ... > the server than allowing SQL Server to update statistics as it sees fit. ...
    (microsoft.public.sqlserver.server)
  • Re: Reasons for query execution time difference ???
    ... These queries look like it is updating statistics. ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ... I restored the database from a backup and> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the> indexes & statistics are up to date... ...
    (microsoft.public.sqlserver.server)
  • Re: Auto update statistics
    ... Bob Castleman wrote: ... > and so this option should be turned off and update statistics should ... 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 capture SQL statement messages in DTS log file
    ... To capture the statistics date, look at the STATS_DATE function in the Books ... Then instead of PRINT in your DTS package, ... The execution of the following DTS Package succeeded: ... Update statistics for DYNAMICS User Tables ...
    (microsoft.public.sqlserver.dts)
  • 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)