Configuration and Tuning

The system administrator or database owner may elect to use the server to optimize performance or to allow use by a required number of clients. Configuration choices might involve being able to review total numbers of reads and writes for a given SQL command.

Once an application is running, the system administrator should monitor performance and may choose to customize and fine-tune the system. The server provides tools for these purposes, including:

Changing system parameters with the sp_configure procedure
Using update statistics to ensure that Component Integration Services makes the best use of existing indexes
Monitoring server activity with the dbcc command
Setting trace flags
Executing ddlgen and related backup and recovery issues
Determining database size requirements

Using sp_configure
The configuration parameters in sp_configure control resource allocation and performance.
Using Global Variables to Determine Status
Component Integration Services adds a number of global variables.

Parent topic: Component Integration Services Concepts