SQL Server Performance Tuning

SQL Server keeps 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 automatically updated. However, manually updating the statistics can lead to dramatically better database performance.

• More information from the Microsoft Developer Network website

Manually update the statistics for a single table

Syntax

Update statistics '<table name>'

Example

UPDATE STATISTICS 'VDATA'

Tables requiring optimization

• VDATA

• VDAT_AUX

• VNOTE

• VMON

• VMON_AUX

• DDATA

• DDAT_AUX

• DNOTE

Other tables that may require optimization

• VSTDS

• DSTDS

Updating statistics for a database

Ensure that the Auto Update setting is set to "on" or that a stored procedure is setup to automatically run to update the statistics on a regular schedule. The advantage of running the update statistics query on individual tables is that you can only perform the operation on the relevant tables.

To update the statistics for an entire database:

• Run the stored procedure sp_updatestats

Example:

use SPC

sp_updatestats

Verifying current status of the auto update statistics setting

Run the query:

sp_dboption '<database name>', 'UPDATE STATISTICS'

If the feature is not enabled you can enable it by running the query:

sp_dboption '<database name>', 'update statistics', 'on'