SQL Server keeps statistics on tables and indexed views to enable it to optimize how the database engine runs the queries and improve performance. By default, these statistics are updated automatically. To view the status of the “Auto Update Statistics” setting, from the Microsoft Management Studio right-click on the SPC database and select Properties from the menu. Then select the Options page and scroll to the Automatic section.
However, we have occasionally found that manually updating the statistics can lead to better database performance because the statistics aren’t continually updated. For more information see Microsoft’s Technical Articles.
To update the statistics for the entire database run the stored procedure sp_updatestats.
Example:
use SPC
sp_updatestats
This is faster and more efficient than running individual updates and provides useful feedback. Thus, it is the recommended method. If you prefer to update individual tables, follow the steps below.
To manually update the statistics for a single table the syntax is:
Update statistics <table name>
Example:
UPDATE STATISTICS VDATA
The tables that typically require optimization are:
- VDATA
- VDAT_AUX
- VNOTE
- VMON
- VMON_AUX
- DDATA
- DDAT_AUX
- DNOTE
Tables that might need optimization:
- VSTDS
- DSTDS
The advantage of running the update statics query on individual tables is that you can perform the operation on just the relevant tables, although the sp_updatestats stored procedure runs quickly and provides more useful feedback.
