Microsoft SQL Server 7.0 introduces and enhances methods and tools to tune SQL Server for optimum performance. Keep these principles in mind when you are tuning SQL Server:
Microsoft SQL Server has been enhanced to create an auto-configuring and self-tuning database server. Take advantage of the auto-tuning settings available with SQL Server. These settings help SQL Server run at peak performance even as user load and queries change over time.
An integral feature of the database server environment is the management of RAM buffer cache. Access to data in RAM cache is much faster than access to the same information from disk, but RAM is a limited resource. If database I/O can be reduced to the minimum required set of data and index pages, these pages will stay in RAM longer. Too much unneeded data and index information flowing into buffer cache quickly push out valuable pages. The focus of performance tuning is to reduce I/O so that buffer cache is best utilized.
A key factor in maintaining minimum I/O for all database queries is to ensure that good indexes are created and maintained.
The physical disk subsystem must provide a database server with sufficient I/O processing power for the database server to run without disk queuing. Disk queuing results in poor performance. For more information, see "Disk I/O Performance" later in this chapter.
Tuning your applications and queries becomes especially important when a database server will service requests from hundreds or thousands of connections by way of a given application. Because applications typically determine the SQL queries that will be executed on a database server, application developers must understand SQL Server architectural basics and how to take full advantage of SQL Server indexes to minimize I/O.
SQL Server Profiler can be used to monitor and log a SQL Server workload, which can then be submitted to the Index Tuning Wizard to tune indexes for better performance. Regular use of SQL Server Profiler and the Index Tuning Wizard helps you optimize the indexes, allowing SQL Server to perform well with changing query workloads.
SQL Server 7.0 provides a revised set of Performance Monitor objects and counters, which are designed to provide helpful information for monitoring and analyzing the operations of SQL Server. For more information, see "Key Performance Monitor Counters" later in this chapter.
SQL Server 7.0 Query Analyzer introduces Graphical Showplan, an enhancement to help analyze problematic Transact-SQL queries. SQL Server Query Analyzer also includes STATISTICS IO, another important tool option for tuning queries.