| Tables | xiv |
| Acknowledgments | xv |
| Introduction | xvii |
| PART I BASIC CONCEPTS | |
| 1 Performance Tuning, Capacity Planning, and Sizing Overview | 3 |
| Performance Tuning and Optimization | 3 |
| Application Tuning | 4 |
| SQL Server Tuning | 5 |
| Hardware Tuning | 5 |
| Sizing and Capacity Planning | 5 |
| Server Tuning Methodology | 6 |
| Tuning Steps | 6 |
| Tuning Tips and Recommendations | 9 |
| Summary | 11 |
| 2 SQL Server 2000 Architecture Fundamentals | 13 |
| Memory Management | 13 |
| Concepts: Physical and Virtual Memory | 13 |
| SQL Server Memory Architecture | 14 |
| Dynamic and Manual Memory Configuration | 17 |
| Memory Sizes Supported | 19 |
| Data Storage | 20 |
| Data Files and Log Files | 20 |
| Filegroups and File Placement | 22 |
| Automatic File Growth | 25 |
| Pages, Extents, and Rows | 25 |
| Lock Management | 26 |
| What is a Lock? | 26 |
| Lock Granularity and Lock Modes | 27 |
| The locks Option | 28 |
| Thread Management | 28 |
| Fiber Mode Scheduling and Worker Threads | 29 |
| Transaction Log | 30 |
| Transaction Log Characteristics | 31 |
| Transaction Log Architecture | 32 |
| Truncating the Transaction Log | 34 |
| What is a Checkpoint? | 35 |
| Summary | 38 |
| 3 Understanding the I/O Subsystem and RAID | 39 |
| Performance Characteristics of Disk Drives | 39 |
| Disk Drive Description | 40 |
| Disk Drive Behaviors | 41 |
| Disk Drive Specifications | 43 |
| Disk Drive Reliability | 44 |
| Disk Drive Performance | 44 |
| Solving the Disk Performance Capacity Problem | 47 |
| Introduction to RAID | 47 |
| Controller Caches | 48 |
| Internal vs. External RAID | 48 |
| Storage Area Networks | 49 |
| Common RAID Levels | 50 |
| Striping | 50 |
| RAID 0 | 51 |
| RAID 1 | 52 |
| RAID 5 | 54 |
| RAID 10 | 56 |
| Performance Comparison of RAID Levels | 58 |
| Read Performance | 58 |
| Write Performance | 59 |
| Disk Calculations | 59 |
| Choosing the Right RAID Level | 60 |
| I/O Latencies and SQL Server | 61 |
| Guidelines for Configuring I/O Subsystems | 62 |
| Summary | 63 |
| 4 System Tuning | 65 |
| How to Measure Performance | 65 |
| Tuning the Server with Hardware | 67 |
| Processor Architecture | 67 |
| Tuning the Processor | 69 |
| Tuning the Disk Subsystem | 70 |
| Tuning Memory | 75 |
| Optimizing Database Layout | 78 |
| Guidelines for Database Layout | 78 |
| Examples Using Files and Filegroups | 80 |
| SQL Server Configuration Options | 83 |
| AWE Enabled | 84 |
| Lightweight Pooling | 84 |
| Locks | 85 |
| Max Server Memory | 85 |
| Min Server Memory | 86 |
| Set Working Set Size | 86 |
| Recovery Interval | 86 |
| User Connections | 87 |
| Summary | 87 |
| 5 New Features and Performance Enhancements | 89 |
| Extended Memory Support | 89 |
| Named Instances of SQL Server | 90 |
| Federated Servers with Distributed Partitioned Views | 90 |
| SQL Server 2000 Failover Clustering | 95 |
| XML Support | 96 |
| Database Maintenance Operations | 97 |
| Referential Integrity Enhancements | 97 |
| Full-Text Search | 97 |
| New Data Types | 98 |
| Index Enhancements | 98 |
| Administration Enhancements | 98 |
| Log Shipping | 98 |
| SQL Profiler | 98 |
| SQL Query Analyzer | 99 |
| Replication Enhancements | 100 |
| Additional Enhancements | 101 |
| Summary | 101 |
| 6 Monitoring Performance with System Monitor | 103 |
| System Monitor | 103 |
| Viewing Performance Data in Real-Time Mode | 104 |
| Logging Performance Data | 104 |
| System Monitor Objects, Counters, and Instances | 110 |
| Processor Object | 110 |
| System Object | 111 |
| SQLServer: Buffer Manager Object | 112 |
| SQLServer: Databases Object | 113 |
| SQLServer: General Statistics Object | 114 |
| SQLServer: Latches Object | 115 |
| SQLServer: Locks Object | 115 |
| SQLServer: Memory Manager Object | 117 |
| SQLServer: SQL Statistics Object | 117 |
| LogicalDisk and PhysicalDisk Object | 118 |
| Memory | 119 |
| Summary | 120 |
| 7 Using SQL Profiler | 121 |
| SQL Profiler Overview | 122 |
| Using SQL Profiler | 123 |
| Setting Trace Options | 124 |
| Running Traces | 124 |
| Trace Templates | 125 |
| Modifying Trace Properties | 132 |
| Creating Trace Templates | 140 |
| Analyzing Profile Data | 141 |
| Using SQL Statements to Analyze Trace Data | 143 |
| Sample Scenarios | 144 |
| Looking for Long-Running SQL Statements | 144 |
| Looking for Resource Consumers | 145 |
| Using SQL Profiler to Detect Deadlocks | 145 |
| Using the Profiler as a System Log | 145 |
| Looking for Performance Problems | 146 |
| Debugging SQL Statements | 146 |
| Capture and Replay | 147 |
| Single-Stepping | 147 |
| Summary | 149 |
| PART II SIZING AND CAPACITY PLANNING | |
| 8 Modeling for Sizing and Capacity Planning | 153 |
| Introduction to Sizing and Capacity Planning | 153 |
| Preconfiguration Capacity Planning | 153 |
| Postconfiguration Capacity Planning | 154 |
| History of Capacity Planning, Benchmarking, and Simulations | 154 |
| Modeling Principles for Sizing and Capacity Planning | 156 |
| Queuing Theory and the Knee of the Curve | 156 |
| Atomic Demand Modeling and Queue Modeling | 159 |
| Service Chains | 159 |
| Mathematics for Modeling | 160 |
| Understanding the Basic Model Set | 160 |
| The Utilization Law | 161 |
| Arrival Rates and Queues | 162 |
| End-to-End Response Time | 163 |
| Developing a Conceptual Model | 164 |
| Summary | 165 |
| 9 Sizing the Database and Application Servers | 167 |
| Preconfiguration Capacity Planning | 167 |
| Transaction Processing Types | 167 |
| Peak Utilization vs. Steady-State Processing | 169 |
| Single-Tier vs. Multitier Systems | 170 |
| Page Faulting | 171 |
| Disk Requirements of the Database Server | 172 |
| RAID Fault Tolerance | 172 |
| Rules for Database Disk Drives | 174 |
| File Structures, Queries, and the Resulting I/Os | 176 |
| Sizing the Database Disk Farm | 178 |
| Sizing the Application Server | 179 |
| Single-Tier Model | 179 |
| Multitier Model | 180 |
| Summary | 180 |
| 10 Methods for Sizing a System | 181 |
| Sizing the Processor | 182 |
| The Interview Process | 182 |
| Calculating CPU Utilization | 183 |
| Calculating System Statistics | 187 |
| I/O Throughput | 187 |
| System Capacity | 187 |
| Queue Length and I/O Response Time | 188 |
| Transaction Response Times | 189 |
| Sizing Memory | 190 |
| Sizing the Disk I/O Subsystem | 193 |
| Operating System Disks | 193 |
| Log File Disks | 193 |
| Database Disks | 194 |
| Sizing Example | 196 |
| Summary | 197 |
| 11 Capacity Planning | 199 |
| Performance Tuning vs. Capacity Planning | 200 |
| Data Collection | 202 |
| Counters for All Machines | 204 |
| Counters for Database Servers | 205 |
| Counters for Web Servers | 207 |
| Counters for Application Servers | 207 |
| Service Chains | 207 |
| Data Analysis | 208 |
| Historic Data Reporting | 209 |
| Predictive Analysis | 210 |
| Correlative Analysis | 213 |
| Service Chain Reporting | 214 |
| Summary | 214 |
| PART III CONFIGURING AND TUNING THE SYSTEM | |
| 12 Online Transaction Processing Systems | 217 |
| What is an OLTP System? | 217 |
| System Configuration Options for OLTP Systems | 219 |
| Two-Tiered Architecture | 220 |
| Three-Tiered Architecture | 222 |
| Guidelines for Designing OLTP Systems | 223 |
| OLTP and Decision Support Workloads | 223 |
| Data Placement and Filegroups | 223 |
| Tuning OLTP Transactions | 227 |
| Controlling Data Content | 227 |
| Database Backup | 228 |
| Indexes | 228 |
| OLTP System Tuning | 229 |
| Processor Subsystem | 229 |
| Memory Subsystem | 229 |
| Pinning Tables in Memory | 230 |
| Page-Level and Row-Level Locking | 230 |
| I/O Subsystem | 231 |
| Summary | 232 |
| 13 Data Warehouses | 233 |
| Comparing OLTP and Data Warehouse Systems | 233 |
| OLTP | 233 |
| Data Warehouses | 234 |
| Analyzing Data Warehouse Data | 235 |
| OLAP | 235 |
| Data Warehousing Tools | 235 |
| Designing a Data Warehouse | 236 |
| Tables and Schemas | 236 |
| Table Design Issues | 239 |
| Storage Design Issues | 242 |
| Cube Design Issues | 243 |
| Configuring a Data Warehouse for Performance | 245 |
| Hardware | 245 |
| Optimizing the Query Log | 247 |
| Summary | 247 |
| 14 Tuning Replicated Systems | 249 |
| Replication Overview | 249 |
| Types of Replication | 249 |
| Replication Tuning Basics | 250 |
| The Distributor | 251 |
| Configuring the Distributor | 252 |
| Monitoring the Distributor | 255 |
| Tuning the Distributor | 256 |
| Tuning for Snapshot Replication | 256 |
| Attributes of Snapshot Replication | 257 |
| Configuring for Snapshot Replication | 257 |
| Monitoring the Snapshot System | 262 |
| Tuning the Snapshot System | 263 |
| Tuning for Transactional Replication | 263 |
| Attributes of Transactional Replication | 263 |
| Configuring for Transactional Replication | 264 |
| Monitoring the Transactional Replication System | 269 |
| Tuning the Transactional Replication System | 269 |
| Tuning for Merge Replication | 270 |
| Attributes of Merge Replication | 270 |
| Configuring for Merge Replication | 270 |
| Monitoring the Merge Replication System | 273 |
| Tuning the Merge Replication System | 273 |
| Summary | 274 |
| 15 High-Performance Backup and Recovery | 275 |
| Backup and Recovery Concepts | 275 |
| Backup and Restore | 275 |
| Recovery | 276 |
| Non-Logged Operations | 276 |
| Types of Backups | 278 |
| Factors Affecting Performance of Backup and Recovery | 279 |
| The Backup Process | 280 |
| I/O Subsystem | 280 |
| Network | 282 |
| SQL Server | 282 |
| Configuring the System for Maximum Backup and Recovery Performance | 283 |
| Analyzing Performance Needs of a Backup | 283 |
| Configuring the Network | 284 |
| Configuring the I/O Subsystem | 286 |
| Backup Scenarios | 287 |
| Local Backups | 287 |
| Network Backups | 288 |
| Storage Area Networks | 289 |
| Backup Tips and Recommendations | 289 |
| SQL Server System | 289 |
| Network | 289 |
| Network Backup Server | 290 |
| General Tips and Guidelines | 290 |
| Review of Component Capacities | 291 |
| Summary | 292 |
| PART IV TUNING SQL STATEMENTS | |
| 16 Using SQL Query Analyzer | 295 |
| SQL Query Analyzer Features | 295 |
| Running SQL Query Analyzer | 296 |
| The Connection Process | 296 |
| The Query Window | 297 |
| Analyzing Queries | 299 |
| Execution Plans | 299 |
| Logical and Physical Operators | 300 |
| Interpreting Graphical Execution Plans | 307 |
| Example of Using SQL Query Analyzer | 309 |
| Index Tuning Wizard | 313 |
| Features of the Index Tuning Wizard | 313 |
| Limitations of the Index Tuning Wizard | 314 |
| Using the Index Tuning Wizard | 315 |
| Summary | 315 |
| 17 Tuning SQL Statements and Stored Procedures | 317 |
| Application Design | 317 |
| Recommendations | 318 |
| Critical-Path Transactions | 321 |
| Using Stored Procedures | 322 |
| The Effect of Variables | 327 |
| Caching Execution Plans | 328 |
| Coding SQL Statements as Transactions | 329 |
| Efficient Retrieval of Data | 330 |
| Tuning SQL Queries | 332 |
| General Recommendations | 335 |
| Queries Using Joins | 336 |
| Prepared Execution | 336 |
| Summary | 338 |
| 18 Using and Tuning Indexes | 339 |
| Index Fundamentals | 339 |
| Index Keys | 341 |
| Types of Indexes | 341 |
| Tuning Indexes | 346 |
| Choosing the Index Key Columns | 348 |
| Fill Factor | 349 |
| Using SQL Query Analyzer | 350 |
| Summary | 358 |
| 19 Using Hints in SQL Server | 359 |
| Isolation Levels | 359 |
| Session-Level Isolation | 361 |
| SQL Hints | 362 |
| Join Hints | 363 |
| Table Hints | 364 |
| Query Hints | 366 |
| Bulk Copy Hints | 368 |
| Summary | 369 |
| PART V APPENDIXES AND GLOSSARY | |
| A SQL Server Configuration Options | 373 |
| Configuration Options | 373 |
| Show Advanced Options | 377 |
| Option Specification | 377 |
| Changing Options with the Enterprise Manager | 378 |
| B SQL Server Monitoring | 389 |
| SQLServer:Access Methods | 389 |
| SQL Server:Backup Device | 390 |
| SQL Server:Buffer Manager | 390 |
| SQL Server:Buffer Partition | 392 |
| SQL Server:Cache Manager | 392 |
| SQLServer:Databases | 393 |
| SQLServer:General Statistics | 394 |
| SQLServer:Latches | 394 |
| SQL Server:Locks | 394 |
| SQL Server:Memory Manager | 395 |
| SQL Server:Replication Agents | 396 |
| SQL Server:Replication Dist. | 396 |
| SQL Server:Replication Logreader | 396 |
| SQL Server:Replication Merge | 397 |
| SQL Server:Replication Snapshot | 397 |
| SQL Server:SQL Statistics | 397 |
| SQLServer:User Settable | 398 |
| GLOSSARY | 399 |
| INDEX | 413 |
