Does auto update stats do anything if auto create stats are turned off

I am using SQL 2000 and wondered if a database is created and auto create
stats would be turned off, but auto update stats are turned on, does auto
update stats have any stats to update ? I am kinda confused ? Assuming we
load some tables with indexes and have stored procedures,etc..and start
modifying data, will update stats have any stats to work with since auto
create stats is off ? Will query plans ever be recompiled ? Will the query
optimiser figure a different execution plan ?
Are statistics ever created ? Do indexes behave like statistics ?
Thank youHi Hassan,
There is a difference between 'Statistics' as an 'object' and 'Statistical
Information' which is the data stored in the row.
When 'Auto Create Stats' Is turned off, no new statistics will be generated
automatically , i.e. no new statistical 'objects' will be created.
A column that already has a 'statistics' created for it, will still have
it's statistics information 'refreshed' or recollected. this option is
controlled by the 'Auto Update Statistics' option.
Statistics are a seperate object from Indexes. Indexes 'have' Statistics
information that is collected by the query optimizer and stored in the index
row in sysindexes.
Statistics are created not only for indexes, but also for unindexed columns
(for example, columns that participate in join conditions) and are also
stored in sysindexes.
This is done automatically by the query optimizer when the 'Auto Create
Statistics' option is on or you could create it yourself by using the CREATE
STATISTICS statement. To update statistics yourself, you can use the UPDATE
STATISTICS statement or the sp_updatestats system SP.
If you disable either DB options and just leave it at that (not creating and
updating stats), the query optimizer will not be able to choose optimal
execution plans.
It will still produce some plan and your queries will be executed but it's
bound to have a negative effect on performance.
I hope it makes some sense :-)
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OQcYPi7YDHA.2032@.TK2MSFTNGP10.phx.gbl...
> I am using SQL 2000 and wondered if a database is created and auto create
> stats would be turned off, but auto update stats are turned on, does auto
> update stats have any stats to update ? I am kinda confused ? Assuming we
> load some tables with indexes and have stored procedures,etc..and start
> modifying data, will update stats have any stats to work with since auto
> create stats is off ? Will query plans ever be recompiled ? Will the query
> optimiser figure a different execution plan ?
> Are statistics ever created ? Do indexes behave like statistics ?
> Thank you
>|||So if I understand correctly, if Auto Create Stats are not turned on ,
'Statistics' will not be created and hence Auto Update Statistics will only
work with the index created 'Statistics' since only indexes are created
assuming we did not Create Statistics manually. Am I correct ?
"Amy" <l.a@.usa.com> wrote in message
news:eQbneiAZDHA.3232@.tk2msftngp13.phx.gbl...
> Hi Hassan,
> There is a difference between 'Statistics' as an 'object' and 'Statistical
> Information' which is the data stored in the row.
> When 'Auto Create Stats' Is turned off, no new statistics will be
generated
> automatically , i.e. no new statistical 'objects' will be created.
> A column that already has a 'statistics' created for it, will still have
> it's statistics information 'refreshed' or recollected. this option is
> controlled by the 'Auto Update Statistics' option.
> Statistics are a seperate object from Indexes. Indexes 'have' Statistics
> information that is collected by the query optimizer and stored in the
index
> row in sysindexes.
> Statistics are created not only for indexes, but also for unindexed
columns
> (for example, columns that participate in join conditions) and are also
> stored in sysindexes.
> This is done automatically by the query optimizer when the 'Auto Create
> Statistics' option is on or you could create it yourself by using the
CREATE
> STATISTICS statement. To update statistics yourself, you can use the
UPDATE
> STATISTICS statement or the sp_updatestats system SP.
> If you disable either DB options and just leave it at that (not creating
and
> updating stats), the query optimizer will not be able to choose optimal
> execution plans.
> It will still produce some plan and your queries will be executed but it's
> bound to have a negative effect on performance.
> I hope it makes some sense :-)
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OQcYPi7YDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > I am using SQL 2000 and wondered if a database is created and auto
create
> > stats would be turned off, but auto update stats are turned on, does
auto
> > update stats have any stats to update ? I am kinda confused ? Assuming
we
> > load some tables with indexes and have stored procedures,etc..and start
> > modifying data, will update stats have any stats to work with since auto
> > create stats is off ? Will query plans ever be recompiled ? Will the
query
> > optimiser figure a different execution plan ?
> >
> > Are statistics ever created ? Do indexes behave like statistics ?
> >
> > Thank you
> >
> >
>|||AFAIK, Auto update will continue to update all statistics that were created
before turning the option off, including indexes and column statistics.
If you did not execute any queries on the DB prior to turning the auto
create option off, probably no auto statistics were created for columns.
It is recommended that you leave both options 'on'.
The creation process will probaly have a quick 'run in' time. Initially,
there will be no column statistics. once you start issuing queries against
the DB, the query optimizer will create the proper statistics and once it
has 'seen' all your queries, there will be no need to create new ones unless
you change the schema or introduce new queries that use previously unused
columns for joining, searching etc.
The updating process is controlled by SQL server. It uses an algorithm that
takes into account the size of the table and the number of changed rows
since the last statistics collection. It will automatically initiate the
recollection of statistical data when it thinks the old data might be
invalid.
'The cost of this automatic statistical update is minimized by sampling the
data, rather than analyzing all of it.'
Unless your DB experinces very heavy modifications on very large tables and
you have verified for a fact that the statistical update process is causing
performance issues during peak usage and you make sure that the density and
distribution of data does not change in such a way that not updating the
statistics will not cause the optimizer to choose inappropriate plans and
have tested this thoroughly and have prepared some alternatives to creation
and update of statistics during off peak hours etc. etc. then there is
really no need to turn these option off.
Look in BOL under 'statistical information'.
Kalen's 'Inside SQL Server 2000' has an excellent chapter about the query
processor and the way SQL Server handles statistics.
HTH
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eoTHooBZDHA.2020@.TK2MSFTNGP10.phx.gbl...
> So if I understand correctly, if Auto Create Stats are not turned on ,
> 'Statistics' will not be created and hence Auto Update Statistics will
only
> work with the index created 'Statistics' since only indexes are created
> assuming we did not Create Statistics manually. Am I correct ?
> "Amy" <l.a@.usa.com> wrote in message
> news:eQbneiAZDHA.3232@.tk2msftngp13.phx.gbl...
> > Hi Hassan,
> >
> > There is a difference between 'Statistics' as an 'object' and
'Statistical
> > Information' which is the data stored in the row.
> > When 'Auto Create Stats' Is turned off, no new statistics will be
> generated
> > automatically , i.e. no new statistical 'objects' will be created.
> > A column that already has a 'statistics' created for it, will still have
> > it's statistics information 'refreshed' or recollected. this option is
> > controlled by the 'Auto Update Statistics' option.
> >
> > Statistics are a seperate object from Indexes. Indexes 'have' Statistics
> > information that is collected by the query optimizer and stored in the
> index
> > row in sysindexes.
> > Statistics are created not only for indexes, but also for unindexed
> columns
> > (for example, columns that participate in join conditions) and are also
> > stored in sysindexes.
> > This is done automatically by the query optimizer when the 'Auto Create
> > Statistics' option is on or you could create it yourself by using the
> CREATE
> > STATISTICS statement. To update statistics yourself, you can use the
> UPDATE
> > STATISTICS statement or the sp_updatestats system SP.
> >
> > If you disable either DB options and just leave it at that (not creating
> and
> > updating stats), the query optimizer will not be able to choose optimal
> > execution plans.
> > It will still produce some plan and your queries will be executed but
it's
> > bound to have a negative effect on performance.
> >
> > I hope it makes some sense :-)
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OQcYPi7YDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > > I am using SQL 2000 and wondered if a database is created and auto
> create
> > > stats would be turned off, but auto update stats are turned on, does
> auto
> > > update stats have any stats to update ? I am kinda confused ? Assuming
> we
> > > load some tables with indexes and have stored procedures,etc..and
start
> > > modifying data, will update stats have any stats to work with since
auto
> > > create stats is off ? Will query plans ever be recompiled ? Will the
> query
> > > optimiser figure a different execution plan ?
> > >
> > > Are statistics ever created ? Do indexes behave like statistics ?
> > >
> > > Thank you
> > >
> > >
> >
> >
>