Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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:

 
UPDATE STATISTICS <Mytable>

To update the statistics for a specific index:

 
UPDATE STATISTICS <Mytable> <MyIndex>
Jai Bardhan
 
Comment and Contribute

 

 

 

 

 


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

 

 

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