auto update statistics

auto update statistics

Post by Joe » Fri, 17 Sep 1999 04:00:00



Hi
Has anyone had experience with needing to shut off auto updating
of index statistics etc because it got in the way.  I have a growing
database and sometimes wonder if I'd be better off doing the statistic,
recompiles manually when I know the tables are going to experience
heavy amounts of modifications because of long running load processes.
Thanks
Joe
 
 
 

auto update statistics

Post by Mark Alliso » Sat, 18 Sep 1999 04:00:00


Go to http://msdn.microsoft.com and search on this: Q195565. This describes
everything you're looking for. Remember to switch on all the search flags
first, or it won't find it.

Mark.

 
 
 

auto update statistics

Post by Joe » Sat, 18 Sep 1999 04:00:00


Thanks for the response.
Joe

>Go to http://msdn.microsoft.com and search on this: Q195565. This describes
>everything you're looking for. Remember to switch on all the search flags
>first, or it won't find it.

>Mark.

 
 
 

auto update statistics

Post by Chris Woo » Sat, 18 Sep 1999 04:00:00


Joe,

We have some big users of tempdb and we are setting auto stats off on our
databases and tempdb. Kinda like running in 6.5 mode but with the new
features of 7 available. So far, in testing, this gives us 6.5 speed
performance. We are on SP1.

Chris Wood
Alberta Department of Resource Development
CANADA


> Thanks for the response.
> Joe

> >Go to http://msdn.microsoft.com and search on this: Q195565. This
describes
> >everything you're looking for. Remember to switch on all the search flags
> >first, or it won't find it.

> >Mark.

 
 
 

auto update statistics

Post by Joe » Sat, 18 Sep 1999 04:00:00


Chris
Would the need to shut them off in temp db involve the use of temp tables
created from
a stored procedure or would it also effect the way sqlserver handles it's
use of tempdb
(static cursors, sorts etc)?

Thanks
Joe


>Joe,

>We have some big users of tempdb and we are setting auto stats off on our
>databases and tempdb. Kinda like running in 6.5 mode but with the new
>features of 7 available. So far, in testing, this gives us 6.5 speed
>performance. We are on SP1.

>Chris Wood
>Alberta Department of Resource Development
>CANADA

 
 
 

auto update statistics

Post by Joshua Turne » Tue, 21 Sep 1999 04:00:00


Joe,

It is my understanding that it can be done manually by issuing UPDATE
STATISTIC {file|filegroup}. You can also run sp_updatestats to manually
update the entire database. If you want to schedule it less frequently, turn
AUTO UPDATE STATISTICS off and create a job to run one of these commands on
an interval of your choice.

Josh


Quote:> Hi
> Has anyone had experience with needing to shut off auto updating
> of index statistics etc because it got in the way.  I have a growing
> database and sometimes wonder if I'd be better off doing the statistic,
> recompiles manually when I know the tables are going to experience
> heavy amounts of modifications because of long running load processes.
> Thanks
> Joe

 
 
 

auto update statistics

Post by Joe » Wed, 22 Sep 1999 04:00:00


Thanks

>Joe,

>It is my understanding that it can be done manually by issuing UPDATE
>STATISTIC {file|filegroup}. You can also run sp_updatestats to manually
>update the entire database. If you want to schedule it less frequently,
turn
>AUTO UPDATE STATISTICS off and create a job to run one of these commands on
>an interval of your choice.

>Josh



>> Hi
>> Has anyone had experience with needing to shut off auto updating
>> of index statistics etc because it got in the way.  I have a growing
>> database and sometimes wonder if I'd be better off doing the statistic,
>> recompiles manually when I know the tables are going to experience
>> heavy amounts of modifications because of long running load processes.
>> Thanks
>> Joe

 
 
 

1. Auto create/ auto update statistics, server 7.0

        I was inspired by another posting to this NG and checked the statistics
on a few tables in my DB. I am having problems for quite a long time
that SQL server sometimes chooses very stupid execution plans. I found
out that statistics on one of the very frequented tables was updated
last time at the end of March. But auto create/update statistics is
turned on. Is this normal? At what moments SQL server decides to update
the statistics? Should I rather schedule the job that would force an
update of the statistics at that moment?
        I could hardcode the execution plan into my SQL queries, but there are
those rare moments when SQL server comes up with really good plans (even
better than mine :) and I also do not fancy the idea of writing hints
into the code.

Ivan

2. Find Replace How to?

3. auto update statistics

4. Stored Procs

5. Indexes created when auto update statistics is set to On

6. test

7. Auto Update Statistics

8. When are Stored Procs Recompiled if using Auto update Statistics

9. Auto Update Statistics.

10. Is auto-update statistics really necessary?