Microsoft SQL Server 2005 Performance Optimization and Tuning Handbook (eBook)

Front Cover 1
Microsoft® SQL Server™ 2005 Performance Optimization and Tuning Handbook 4
Copyright Page 5
Contents 8
Introduction 16
Chapter 1. Performance and SQL Server 2005 18
1.1 Partitioning tables and indexes 18
1.2 Building indexes online 19
1.3 Transact SQL improvements 19
1.4 Adding the .NET Framework 20
1.5 Trace and replay objects 21
1.6 Monitoring resource consumption with SQL OS 21
1.7 Establishing baseline metrics 21
1.8 Start using the GUI tools 24
1.9 Availability and scalability 32
1.10 Other useful stuff 33
1.11 Where to begin? 34
Chapter 2. Logical Database Design for Performance 36
2.1 Introducing logical database design for performance 36
2.2 Commercial normalization techniques 38
2.3 Denormalization for performance 46
2.4 Extreme denormalization in data warehouses 65
Chapter 3. Physical Database Design 82
3.1 Introducing physical database design 82
3.2 Data volume analysis 84
3.3 Transaction analysis 86
3.4 Hardware environment considerations 90
Chapter 4. SQL Server Storage Structures 92
4.1 Databases and files 92
4.2 Creating databases 96
4.3 Increasing the size of a database 100
4.4 Decreasing the size of a database 101
4.5 Modifying filegroup properties 107
4.6 Setting database options 109
4.7 Displaying information about databases 112
4.8 System tables used in database configuration 115
4.9 Units of storage 119
4.10 Database pages 121
4.11 Looking into database pages 125
4.12 Pages for space management 129
4.13 Partitioning tables into physical chunks 132
4.14 The BankingDB database 136
Chapter 5. Indexing 138
5.1 Data retrieval with no indexes 138
5.2 Clustered indexes 139
5.3 Non-clustered indexes 144
5.4 Online indexes 146
5.5 The more exotic indexing forms 146
5.6 The role of indexes in insertion and deletion 148
5.7 A note with regard to updates 158
5.8 So how do you create indexes? 159
5.9 Dropping and renaming indexes 174
5.10 Displaying information about indexes 175
5.11 Creating indexes on views 184
5.12 Creating indexes with computed columns 187
5.13 Using indexes to retrieve data 188
5.14 Choosing indexes 199
Chapter 6. Basic Query Tuning 210
6.1 The SELECT statement 211
6.2 Using functions 217
6.3 Comparison conditions 218
6.4 Joins 221
6.5 Using subqueries for efficiency 227
6.6 Specialized metadata objects 230
6.7 Procedures in Transact SQL 231
Chapter 7. What Is Query Optimization? 234
7.1 When is a query optimized? 235
7.2 The steps in query optimization 235
7.3 Query analysis 236
7.4 Index selection 242
7.5 Join order selection 263
7.6 How joins are processed 264
Chapter 8. Investigating and Influencing the Optimizer 274
8.1 Text-based query plans and statistics 276
8.2 Query plans in Management Studio 287
8.3 Hinting to the optimizer 299
8.4 Stored procedures and the query optimizer 306
8.5 Non-stored procedure plans 318
8.6 The syscacheobjects system table 321
Chapter 9. SQL Server and Windows 324
9.1 SQL Server and CPU 324
9.2 SQL Server and memory 340
9.3 SQL Server and disk I/O 352
Chapter 10. Transactions and Locking 372
10.1 Why a locking protocol? 373
10.2 The SQL Server locking protocol 375
10.3 SQL Server locking in action 410
10.4 Uncommitted data, non-repeatable reads, phantoms, and more 415
10.5 Application resource locks 423
10.6 A summary of lock compatibility 424
Chapter 11. Architectural Performance Options and Choices 426
11.1 The Management Studio and the .NET Framework 427
11.2 Striping and mirroring 427
11.3 Workflow management 428
11.4 Analysis Services and data warehousing 429
11.5 Distribution and replication 431
11.6 Standby failover (hot spare) 434
11.7 Flashback snapshot databases 436
Chapter 12. Monitoring Performance 438
12.1 System stored procedures 439
12.2 System monitor, performance logs, and alerts 441
12.3 SQL Server 2005 Management Studio 444
12.4 SQL OS and resource consumption 460
A Syntax Conventions 462
B Database Scripts 464
C Performance Strategies and Tuning Checklist 494
Index 504