Presents your 
SQL SERVER E-NEWSLETTER for November 5, 2002

<------------------------------------------->

OPTIMIZE WITH SYSTEM-GENERATED STATISTICS

The statistics returned by the function _WA_sys are system 
generated-statistics that SQL Server maintains as a default option 
on databases. If queries are issued against a table that doesn't have 
statistics, then the statistics will be generated for the optimizer 
based on that query. Statistics are maintained for indexes as well.

Another type of statistic is labeled with the prefix hind_, which the
index wizard creates. These are hypothetical indexes that are put on
columns to assist the optimizer in picking indexes for queries.

Thresholds for updating all statistics are set dependent upon the type
of table. When the number of modifications to a table reaches the
thresholds, SQL Server will initiate the updating of statistics for 
that table.

Inserts, deletes, and updates to indexes affect the counters for these
thresholds. However, the TRUNCATE TABLE command does not affect the
threshold counter for autogenerated statistics. The threshold is also 
dependent upon the number of rows in a table and if the table is in tempdb 
or not.

The execution plan of a stored procedure is affected by statistics. If
the threshold is met, the stored procedure will be tagged for 
recompiling a new plan.

Performance can be affected by the statistics being calculated,
especially in a very active database. When the thresholds are met, then
statistics will be recomputed and all stored procedures' execution 
plans will be recompiled. At this point, the operations involved in maintaining
automatic statistics are in direct contention with the active database for
resources. In fact, two locks will take place during these operations:

* Schema stability lock--which makes sure that generation of the
statistics does not create any database modifications.

* Schema modification lock--which makes sure that only one update
statistics operation is being performed for a given table.

An option to alleviate the performance hit of automated statistics, is
to create a scheduled system for maintaining statistics that can be
scheduled during less active times. The automation of maintaining 
statistics can be carried out by the UPDATE STATISTICS command and/or by 
executing the sp_autostats procedure.

You will need to either turn off the auto statistics that are maintained
at the database level or issue the UPDATE STATISTICS command with the
argument, NORECOMPUTE. The NORECOMPUTE argument of the UPDATE STATISTICS
command will turn off the auto statistics until the next UPDATE STATISTICS
command or sp_autostats procedure is executed.

----------------------------------------