The Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook

Cover

Contents

Preface

Acknowledgments

1 Introducing Performance Tuning and Physical Database Design

What is performance tuning?

The physical database design process

Data volume analysis

Transaction analysis

Hardware environment considerations

Where to next?

2 SQL Server Storage Structures

Introduction

Databases and files

Creating databases

Increasing the size of a database

Decreasing the size of a database

The autoshrink database option

Shrinking a database in the SQL Server Enterprise Manager

Shrinking a database Using DBCC statements

Removing database files

Modifying filegroup properties

Setting database options

Displaying information about databases

System tables used in database configuration

Units of storage

Database pages

Looking into database pages

Pages for space management

The BankingDB database

3 Indexing

Introduction

Data retrieval with no indexes

Clustered indexes

Nonclustered indexes

The role of indexes in insertion and deletion

A note about updates

So how do you create indexes?

The Transact-SQL CREATE INDEX statement

The SQL Enterprise Manager

The Query Analyzer

The Create Index wizard

The SQL Distributed Management Framework ( SQL- DMF)

Dropping and renaming indexes

Displaying information about indexes

The SQL Server Enterprise Manager

The system stored procedure sp_helpindex

The system table Sysindexes

Using metadata functions to obtain information about indexes

The DBCC statement DBCC SHOWCONTIG

Creating indexes on views

Creating indexes with computed columns

Using indexes to retrieve data

Retrieving a single row

Retrieving a range of rows

Covered queries

Retrieving a single row with a clustered index on the table

Retrieving a range of rows with a clustered index on the table

Covered queries with a clustered index on the table

Retrieving a range of rows with multiple nonclustered indexes on the table

Choosing indexes

Why not create many indexes?

Online transaction processing versus decision support

Choosing sensible index columns

Choosing a clustered index or a nonclustered index

4 The Query Optimizer

Introduction

When is the query optimized?

Query optimization

Query analysis

Index selection

Join order selection

How joins are processed

Tools for investigating query strategy

Influencing the query optimizer

Stored procedures and the query optimizer

Non-stored procedure plans

The Syscacheobjects system table

5 SQL Server 2000 and Windows 2000

SQL Server 2000 and CPU

Introduction

An overview of Windows 2000 and CPU utilization

How SQL Server 2000 uses CPU

Investigating CPU bottlenecks

Solving problems with CPU

SQL Server 2000 and memory

Introduction

An overview of Windows 2000 virtual memory management

How SQL Server 2000 uses memory

Investigating memory bottlenecks

Solving problems with memory

SQL Server 2000 and disk I/O

Introduction

An overview of Windows 2000 and disk I/O

How SQL Server 2000 uses disk I/ O

Investigating disk I/O bottlenecks

Solving problems with disk I/O

6 Transactions and Locking

Introduction

Why a locking protocol?

Scenario 1

Scenario 2

The SQL server locking protocol

Shared and exclusive locks

Row-, page-, and table-level locking

Lock timeouts

Deadlocks

Update locks

Intent locks

Modifying the default locking behavior

Locking in system tables

Monitoring locks

SQL Server locking in action

Uncommitted data, repeatable reads, phantoms, and more

Reading uncommitted data

Nonrepeatable reads

Phantoms

More modified locking behavior

Application resource locks

A summary of lock compatibility

7 Monitoring Performance

Introduction

System stored procedures

System monitor, performance logs, and alerts

The SQL Profiler

What events can be traced?

What information is collected?

Filtering information

Creating a SQL profiler trace

Creating traces with stored procedures

Index Tuning wizard

Query analyzer

8 A Performance Tuning Checklist

System resource use

Choosing efficient indexes

Helping the Query Optimizer

Avoiding lock contention

Rule 1: Keep transactions as short as possible

Rule 2: Do not hold locks across user interactions

Rule 3: Try not to interleave updates and reads

Rule 4: Help the query optimizer to choose indexed access

Rule 5: Only lock as strictly as is necessary to meet your integrity requirements

Rule 6: Update tables in the same order throughout the application

Rule 7: Perform multiuser testing before the application goes live

Database integrity

Database administration activities

Archiving data

Read only report databases

Denormalization

Bibliography