Wednesday, August 29, 2007

Statistics Options

A couple of database level options regarding statistics -

AUTO_UPDATE_STATISTICS

On by default on a new database, it means index and column statistics are updated 'on the fly'.

AUTO_UPDATE_STATISTICS_ASYNC

Queries are run against the current statistics, rather than waiting fot them to be updated first.
The statistics are triggered to be updated asap, hopefully in time for the next query.

I havent (yet) found a system where index updates futher cause poor performance but am consious it could occur on a high volume system.

Setting index statistics options for all databases >
sp_msforeachdb @command1='ALTER DATABASE ? SET AUTO_UPDATE_STATISTICS ON'
sp_msforeachdb @command1='ALTER DATABASE ? SET AUTO_UPDATE_STATISTICS_ASYNC ON'

No comments: