AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS are Null

What is the real net affect of these 2 parameters set to NULL?

How can I tell if stats have been generated for all tables in a db?

I am guessing that stats don't exist for the db and this accounts for the high degree of scans I am seeing on a 1.5gb db running on a 4 gb memory machine.

Any thoughts?
MikeThe net effect is that you have to create the stats your self and also update then on a timely basis.

Personnaly would turn them both on. Some people would argue to turn on the create but do the update on a scheduled basis, the thought is that you can get by with slightly out of date stats rather than take the performace hit to auto update.|||so the bottom line is the optimizer will not have any information on the tables and therefore do scans to find the data?

Is this an accurate description?

Mike|||basically, Yes.|||and what is the most expedient mthod of generating the stats for all objects?|||BTW, once you create statistics, you should recompile all views, triggers, stored procs and re-build all indexes starting with any clustered indexes.|||turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.|||Originally posted by Paul Young
turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.

I just found out each weekend the indexes get rebuilt. Dropped and recreated. I read that whenever an index is created on a table containing data the optimizer collects stats and stores them. Does that still occur when these options are not on?

Mike|||Still recommend to turn those two options on. The major purpose of rebuilding index is not for updating statistics. Even if rebuild index will
update statistics, but the statistics may not be updated during the week.

BTW, never recreate index by drop-and-create strategy. Try to use DBCC DBREINDEX, or CREATE INDEX with DROP_EXISTING, or DBCC INDEXDEFAG.