Chapter 40. Configuring, Tuning, and Optimizing SQL Server Options

by Paul Bertucci


  • SQL Server Instance Architecture

  • Configuration Options

  • Fixing an Incorrect Option Setting

  • Setting Configuration Options with SQL Enterprise Manager

  • Obsolete Configuration Options

  • Configuration Options and Performance

As was already discussed in Chapter 37, "Monitoring SQL Server Performance," many components of a system can be monitored and tuned to yield high performance. These addressable components can be server hardware, network configuration, memory allocation, disk systems usage, database design techniques, and so on. This chapter will delve a bit more into what can be done in the SQL Server configurable options?particularly, what can be improved that SQL Server isn't automatically tuning already. By setting the values of several key SQL Server configuration parameters, you can fine-tune SQL Server to provide excellent performance and throughput.

Note that with each release of SQL Server, less needs to be tuned from the SQL Server configuration point of view. With the advent of self tuning or self configuring options, it is only a matter of time before most of your server tuning time will be spent elsewhere, such as with the operating system, disk systems, and network interfaces?and only because SQL Server can't reach there, yet.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features