Optimizing Database Performance Overview
The goal of performance tuning is to minimize the response time for each query and to maximize the throughput of the entire database server by reducing network traffic, disk I/O, and CPU time. This goal is achieved through understanding application requirements, the logical and physical structure of the data, and tradeoffs between conflicting uses of the database, such as online transaction processing (OLTP) versus decision support.
Performance issues should be considered throughout the development cycle, not at the end when the system is implemented. Many performance issues that result in significant improvements are achieved by careful design from the outset. To most effectively optimize the performance of Microsoft® SQL Server™ 2000, you must identify the areas that will yield the largest performance increases over the widest variety of situations and focus analysis on those areas.
Although other system-level performance issues, such as memory, hardware, and so on, are certainly candidates for study, experience shows that the performance gain from these areas is often incremental. Generally, SQL Server automatically manages available hardware resources, reducing the need (and thus, the benefit) for extensive system-level manual tuning.
|Designing Federated Database Servers||Describes how to achieve high levels of performance, such as those required by large Web sites, by balancing the processing load across multiple servers.|
|Database Design||Describes how database design is the most effective way to improve overall performance. Database design includes the logical database schema (such as tables and constraints) and the physical attributes such as disk systems, object placement, and indexes.|
|Query_Tuning||Describes how the correct design of the queries used by an application can significantly improve performance.|
|Application Design||Describes how the correct design of the user application can significantly improve performance. Application design includes transaction boundaries, locking, and the use of batches.|
|Optimizing Utility and Tool Performance||Describes how some of the options available with the utilities and tools supplied with Microsoft SQL Server 2000 can highlight ways in which the performance of these tools can be improved, as well as the effect of running these tools and your application at the same time.|
|Optimizing Server Performance||Describes how settings in the operating system (Microsoft Windows NT®, Microsoft Windows® 98, or Microsoft Windows 2000) and SQL Server can be changed to improve overall performance.|