Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Intermediate
Nov 6, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 <Mydatabase >,'auto update statistics', <on | off>

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

sp_autostats <Mytable>, 'off'

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

sp_autostats, <Mytable>, 'off', <MyIndex >

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:


To update the statistics for a specific index:

Jai Bardhan
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date