Microsoft SQL Server 2000 Performance Tuning Technical Reference provides all the information that developers and database administrators need to configure Microsoft SQL Server 2000 for optimum performance and scalability. Unlike other SQL Server 2000 tuning books on the market, it teaches not just about the SQL Server relational database management system, but also about how to get the most out of the underlying operating system and hardware. The advanced information it presents is easy to grasp because the authors start with the basics and build upon information in previous chapters to teach the mechanics of performance tuning and how they affect the whole system. This title has been thoroughly revised since the last edition and includes new information throughout.

Positioning Statement:

The in-depth, practical guide to optimizing and tuning performance and capacity planning for Microsoft SQL Server 2000.

Unique Selling Proposition:

Written by the insiders who helped establish the phenomenal Microsoft SQL Server 2000 benchmarks in performance, scalability, and transaction processing.

Key Book Benefits:

• Contains information not only about tuning Microsoft SQL Server 2000, but also about how to get the most out of the underlying operating system and hardware.
• Covers the how and the why of performance tuning by explaining how it affects the entire database management system. Includes information about performance tuning and optimization from Microsoft and third-party SQL Server specialists.

Contents

Tables ..... xiv

Acknowledgments ..... xv

Introduction ..... xvii

Part I: Basic Concepts

1: Performance Tuning, Capacity Planning, and Sizing Overview ..... 3

2: SQL Server 2000 Architecture Fundamentals ..... 13

3: Understanding the I/O Subsystem and RAID ..... 39

4: System Tuning ..... 65

5: New Features and Performance Enhancements ..... 89

6: Monitoring Performance with System Monitor ..... 103

7: Using SQL Profiler ..... 121

Part II: Sizing and Capacity Planning

8: Modeling for Sizing and Capacity Planning ..... 153

9: Sizing the Database and Application Servers ..... 167

10: Methods for Sizing a System ..... 181

11: Capacity Planning ..... 199

Part III: Configuring and Tuning the System

12: Online Transaction Processing Systems ..... 217

13: Data Warehouses ..... 233

14; Tuning Replicated Systems ..... 249

15; High-Performance Backup and Recovery ..... 275

Part IV: Tuning SQL Statements

16: Using SQL Query Analyzer ..... 295

17: Tuning SQL Statements and Stored Procedures ..... 317

18: Using and Tuning Indexes ..... 339

19: Using Hints in SQL Server ..... 359

Part V: Appendixes

A: SQL Server Configuration Options ..... 373

B: SQL Server Monitoring ..... 389

Glossary ..... 399

Index ..... 413