devxlogo

Switch off the Automatic Distribution Statistics Management

Switch off the Automatic Distribution Statistics Management

The SQL Server 7.0 automatically recalculates the table’s distribution statistics when the number of changes reaches some predefined values. This automatic recalculation of the distribution statistics can cause problems such as decreased performance on the server. However, to avoid this you can turn off this recalculation by turning off automatic updates of statistics for a database. Running this command can do this at the database level:

 sp_dboption ,'auto update statistics', 

You can also turn off automatic statistics recalculation for a specific table by running the command:

 sp_autostats , 'off'

For turning off automatic statistics for a specific index for a table use this command:

 sp_autostats, , 'off', 

Remember that if you turn the automatic recalculation off, then you will need to force SQL Server to update its statistics by using the UPDATE STATISTICS SQL statement. To update statistics for an entire table:

 UPDATE STATISTICS 

To update the statistics for a specific index:

 UPDATE STATISTICS  

devx-admin

Share the Post: