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

Cover 1
Contents 6
Preface 12
Acknowledgments 14
1 Introducing Performance Tuning and Physical Database Design 16
What is performance tuning? 16
The physical database design process 18
Data volume analysis 19
Transaction analysis 21
Hardware environment considerations 25
Where to next? 25
2 SQL Server Storage Structures 26
Introduction 26
Databases and files 26
Creating databases 29
Increasing the size of a database 34
Decreasing the size of a database 36
The autoshrink database option 36
Shrinking a database in the SQL Server Enterprise Manager 37
Shrinking a database Using DBCC statements 39
Removing database files 40
Modifying filegroup properties 41
Setting database options 42
Displaying information about databases 45
System tables used in database configuration 47
Units of storage 50
Database pages 52
Looking into database pages 57
Pages for space management 60
The BankingDB database 64
3 Indexing 66
Introduction 66
Data retrieval with no indexes 66
Clustered indexes 67
Nonclustered indexes 72
The role of indexes in insertion and deletion 74
A note about updates 84
So how do you create indexes? 85
The Transact-SQL CREATE INDEX statement 86
The SQL Enterprise Manager 94
The Query Analyzer 96
The Create Index wizard 97
The SQL Distributed Management Framework ( SQL- DMF) 97
Dropping and renaming indexes 99
Displaying information about indexes 99
The SQL Server Enterprise Manager 100
The system stored procedure sp_helpindex 102
The system table Sysindexes 102
Using metadata functions to obtain information about indexes 105
The DBCC statement DBCC SHOWCONTIG 106
Creating indexes on views 110
Creating indexes with computed columns 112
Using indexes to retrieve data 113
Retrieving a single row 115
Retrieving a range of rows 117
Covered queries 119
Retrieving a single row with a clustered index on the table 120
Retrieving a range of rows with a clustered index on the table 122
Covered queries with a clustered index on the table 122
Retrieving a range of rows with multiple nonclustered indexes on the table 123
Choosing indexes 125
Why not create many indexes? 125
Online transaction processing versus decision support 126
Choosing sensible index columns 127
Choosing a clustered index or a nonclustered index 132
4 The Query Optimizer 134
Introduction 134
When is the query optimized? 135
Query optimization 136
Query analysis 136
Index selection 142
Join order selection 162
How joins are processed 163
Tools for investigating query strategy 170
Influencing the query optimizer 221
Stored procedures and the query optimizer 226
Non-stored procedure plans 238
The Syscacheobjects system table 241
5 SQL Server 2000 and Windows 2000 242
SQL Server 2000 and CPU 242
Introduction 242
An overview of Windows 2000 and CPU utilization 242
How SQL Server 2000 uses CPU 244
Investigating CPU bottlenecks 249
Solving problems with CPU 256
SQL Server 2000 and memory 258
Introduction 258
An overview of Windows 2000 virtual memory management 258
How SQL Server 2000 uses memory 260
Investigating memory bottlenecks 265
Solving problems with memory 271
SQL Server 2000 and disk I/O 272
Introduction 272
An overview of Windows 2000 and disk I/O 272
How SQL Server 2000 uses disk I/ O 275
Investigating disk I/O bottlenecks 283
Solving problems with disk I/O 288
6 Transactions and Locking 290
Introduction 290
Why a locking protocol? 291
Scenario 1 291
Scenario 2 292
The SQL server locking protocol 293
Shared and exclusive locks 293
Row-, page-, and table-level locking 295
Lock timeouts 299
Deadlocks 299
Update locks 300
Intent locks 302
Modifying the default locking behavior 303
Locking in system tables 308
Monitoring locks 309
SQL Server locking in action 329
Uncommitted data, repeatable reads, phantoms, and more 334
Reading uncommitted data 334
Nonrepeatable reads 335
Phantoms 337
More modified locking behavior 340
Application resource locks 342
A summary of lock compatibility 342
7 Monitoring Performance 344
Introduction 344
System stored procedures 344
System monitor, performance logs, and alerts 346
The SQL Profiler 350
What events can be traced? 350
What information is collected? 351
Filtering information 352
Creating a SQL profiler trace 352
Creating traces with stored procedures 360
Index Tuning wizard 364
Query analyzer 373
8 A Performance Tuning Checklist 376
System resource use 376
Choosing efficient indexes 377
Helping the Query Optimizer 379
Avoiding lock contention 379
Rule 1: Keep transactions as short as possible 380
Rule 2: Do not hold locks across user interactions 380
Rule 3: Try not to interleave updates and reads 381
Rule 4: Help the query optimizer to choose indexed access 381
Rule 5: Only lock as strictly as is necessary to meet your integrity requirements 381
Rule 6: Update tables in the same order throughout the application 381
Rule 7: Perform multiuser testing before the application goes live 382
Database integrity 382
Database administration activities 382
Archiving data 383
Read only report databases 383
Denormalization 384
Bibliography 386