SQL Server 2000 and 2005 keep statistics on the tables and indexed views to enable it to optimize how the database engine runs the queries and improve performance. Theoretically these statistics are updated automatically. However, since we have found that manually updating the statistics can lead to dramatically better database performance, indicating that this isn’t always true. For more information see Microsoft’s Technical Articles.

To manually update the statistics for a single table the syntax is:
Update statistics ‘<table name>’

Example
UPDATE STATISTICS ‘VDATA’

The tables that require optimization are:

  • VDATA
  • VDAT_AUX
  • VNOTE
  • VMON
  • VMON_AUX
  • DDATA
  • DDAT_AUX
  • DNOTE

Possible tables

  • VSTDS
  • DSTDS

To update the statistics for an entire database run the stored procedure sp_updatestats

Example:

use SPC

sp_updatestats

The advantage of running the update statics query on individual tables is that you can only perform the operation on the relevant tables. If the Auto Update setting is not it should either be turned on or a stored procedure should setup to run once a week to update the statistics

To verify current status of the auto update statistics setting run the query:

sp_dboption ‘<database name>’, ‘UPDATE STATISTICS’

Microsoft SQL Server Management Studio

Microsoft SQL Server Management Studio

If the feature is not enabled you can enable it by running the query:
sp_dboption ‘<database name>’, ‘update statistics’, ‘on’