Introduction

The success of nearly every application is determined by its reliability and performance. Squeezing every millisecond out of a solution is an ongoing task for most application designers and administrators. With SQL Server 2000, Microsoft continues to add performance enhancements to SQL Server with features such as auto-tuning capabilities that allow SQL Server to monitor activity and perform self-tuning tasks. Smaller organizations without dedicated administrators can enjoy greater out-of-the-box performance than with previous versions of SQL Server. For advanced tuning, SQL Server has enhanced the SQL Profiler utility to include additional trace methods and events for recording and analyzing database activity. Tools such as the Index Tuning Wizard can analyze and alter indexes for optimal query performance.

A significant enhancement in SQL Server 2000 is its support for federated database servers. New to SQL Server 2000, federated servers allow for data partitioning across multiple independent servers, so processing is distributed among the federated members. This scale-out technique was used to achieve new performance records for SQL Server and record top scores in Transaction Processing Council (TPC, www.tpc.org) testing, the leading indicator of database performance.

Optimizing SQL Server performance is not limited to SQL Server alone. As the first database system designed for Windows 2000, SQL Server s optimal performance means tuning Windows 2000 to support the tasks of SQL Server. With every application, performance starts with efficient logic, and SQL Query Analyzer includes graphical execution plan analysis for creating optimal T-SQL code.

This chapter reviews the performance enhancements in SQL...