Digital Press - MS SQL Server 2000 Performance Optimization And Tuning Handbook (2001).pdf - MS SQL 2000 Server - darekisap

Microsoft SQL Server 2000 Performance

Optimization and Tuning Handbook

Ken England

Digital Press An imprint of Butterworth-Heinemann

Boston * Oxford * Auckland * Johannesburg * Melbourne * New Delhi

A member of the Reed Elsevier group

Digital Press™ is an imprint of Butterworth-Heinemann.

All trademarks found herein are property of their respective owners.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in

any form or by any means, electronic, mechanical, photocopying, recording, or otherwise,

without the prior written permission of the publisher.

Recognizing the importance of preserving what has been written, Butterworth-Heinemann

prints its books on acid-free paper whenever possible.

Library of Congress Cataloging-in-Publication Data

England, Ken, 1955-

Microsoft SQL server 2000 performance optimization and tuning handbook / Ken England.

p. cm.

Includes index.

ISBN 1-55558-241-9 (pbk.: alk. paper)

1. Client/server computing. 2. SQL server. 3. Relational databases. I. Title.

QA76.9.C55 E635 2001

005.75'85-dc21

2001017498

British Library Cataloging-in-Publication Data

A catalogue record for this book is available from the British Library.

The publisher offers special discounts on bulk orders of this book. For information, please

contact:

Manager of Special Sales

Butterworth-Heinemann

225 Wildwood Avenue

Woburn, MA 01801-2041

Tel: 781-904-2500

Fax: 781-904-2620

For information on all Butterworth-Heinemann publications available, contact our World

Wide Web home page at: http://www.bh.com .

10 9 8 7 6 5 4 3 2 1

Printed in the United States of America

Related Titles From Digital Press

Tony Redmond, Microsoft® Exchange Server for Windows 2000: Planning, Design and

Implementation ,

ISBN 1-55558-224-9, 1072pp, 2000

Jerry Cochran, Mission-Critical Microsoft® Exchange 2000: Building Highly Available

Messaging and Knowledge Management Systems ,

ISBN 1-55558-233-8, 320pp, 2001

Kieran McCorry, Connecting Microsoft® Exchange Server ,

ISBN 1-55558-204-4, 416pp, October 1999

John Rhoton, Programmer's Guide to Internet Mail: SMTP, POP, IMAP, and LDAP ,

ISBN 1-55558-212-5, 312pp, October 1999

Mike Daugherty, Monitoring and Managing Microsoft® Exchange 2000 Server ,

ISBN 1-55558-232-X, 440pp, 2001

For more information or to order these and other Digital Press titles please visit our

website at www.bhusa.com/digitalpress !

At www.bhusa.com/digitalpress you can:

• Join the Digital Press Email Service and have news about our books delivered right to

your desktop

• Read the latest news on titles

• Sample chapters on featured titles for free

• Question our expert authors and editors

• Download free software to accompany select texts

To Margaret, Michael, and Katy

Ken England is President and Founder of Database Technologies, a database consultancy,

product evaluation, and training firm. He is also a Microsoft Certified Systems Engineer and a

Microsoft Certified Trainer. His previous books for Digital Press have helped thousands of

professionals make the best possible use of their SQL databases.

Acknowledgments

Most of all, I would like to thank Margaret, Michael, and Katy England for their long

suffering while I was locked in my study writing this text. Writing about databases is,

unfortunately, not an activity in which most of the family can join in. Because of this, writing

and being sociable are usually mutually exclusive!

Margaret had to spend many a weekend anchored to the house. Michael missed out on

computer game time, kicking a ball around, and tinkering with our old Series II Land Rover.

He was very patient while his dad kept disappearing in front of a PC for protracted periods of

time. Katy missed out on company while she watched cartoons.

Also an apology to Holly, my German Shepherd, who missed out on many walks. It's best not

to annoy German Shepherds too much!

As well as the friends and colleagues who encouraged me with the book, I would like to give

an extra special thanks to the following people.

A very special thank you to Keith Burns, who always has a bubbling enthusiasm for SQL

Server; Nigel Stanley and the folk at ICS Solutions for helping to put SQL Server on the map;

Dave Gay from Microsoft (UK), an old friend, who stimulates my grey matter through many

deep discussions; Chris Atkinson from Microsoft, another old friend, who has helped me out

on many occasions and has also stimulated my grey matter; and also, Doctor Lilian Hobbs, a

database comrade-in-arms, and Doctor Jeff Middleton for debating many SQL Server and

related topics while on 20 mile hikes!

I would also like to thank Karl Dehmer, Lori Oviatt, and Adam Shapiro from Microsoft

Training Development, who came all the way over to the United Kingdom to teach an

absolutely superb SQL Server 6.5 performance tuning and optimization course a few years

ago. Their enthusiasm then for SQL Server performance tuning rubbed off on me and gave

me a much-needed boost to complete the SQL Server 6.5 book and now this one. Another

special thanks goes to friends at Butterworth-Heinemann. Many thanks to our other friends in

Microsoft, without whose skill and hard work SQL Server 2000 would not be the excellent

product it is today.

Ken England

January 2001

Preface

My last SQL Server performance book was aimed at SQL Server 6.5. When Microsoft

released SQL Server 7.0 it was almost as if it were a new product. Although it was backward

compatible in many areas with SQL Server 6.5, the architecture was very different. For

starters, the on-disk structure was completely changed. The usage of files was much improved

over SQL Server 6.5, and SQL Server 7.0 now had an 8 Kb database page size. The query

optimizer was greatly enhanced with many new query plans possible, in particular in the use

of multiple indexes and table joins. The query processor could also now execute complex

queries in parallel. As well as all these changes and many more, Windows 2000 was

beginning to slowly appear on the horizon.

For these reasons, I decided that upgrading a SQL Server 6.5 performance and tuning book to

SQL Server 7.0 was not going to be a trivial task and would be much more than an editing

exercise. I decided that my goal would be to work with SQL Server 7.0 through its lifetime in

my usual performance-tuning-consultancy capacity and not rewrite the book until I felt

confident with the way the new architecture behaved. Of course, nothing stays still for long

with software, especially Microsoft software, and so the actual book-writing goal was to write

a SQL Server 2000 version.

SQL Server 2000 has added many useful enhancements to SQL Server 7.0, but it is still the

SQL Server 7.0 architecture and, therefore, behaves pretty much in the same way. I say to my

students that if you know SQL Server 7.0, you pretty much know SQL Server 2000.

So here goes-the follow-up to the SQL Server 6.5 performance and tuning book. I hope you

like this updated SQL Server 2000 version.

The chapters are written to follow one another in a logical fashion, building on some of the

topics introduced in previous chapters. The structure of the chapters is as follows:

• Chapter 1 introduces the goals of performance tuning and the elements of the physical

database design process including data volume analysis and transaction analysis. It

also introduces the example BankingDB database.

• Chapter 2 describes the SQL Server storage structures including database files,

databases, database pages, and extents.

• Chapter 3 introduces clustered indexes and nonclustered indexes. How data is inserted

and retrieved and choosing the appropriate index for a given situation are discussed.

• Chapter 4 introduces the query optimizer and steps in the query optimization process.

This chapter also discusses the special approach to query optimization used by stored

procedures.

• Chapter 5 looks at the interaction between SQL Server and Windows 2000 in the areas

of CPU, memory, and disk I/O. How to track down and remove bottlenecks is

explored.

• Chapter 6 introduces SQL Server locking mechanisms and strategies and the methods

and tools available for monitoring locks.

• Chapter 7 looks at performance monitoring and the tools available to assist the

database administrator.

• Chapter 8 provides a performance tuning aide-mémoire.

I really enjoy tuning databases and making them run fast. Even more, I really enjoy taking an

elusive performance problem, tracking it down, and fixing it. I hope you, too, find the same

level of enjoyment that I do and that this book kick-starts your interest in performance tuning

SQL Server.

Chapter 1: Introducing Performance

Tuning and Physical Database Design

1.1 What is performance tuning?

What is the goal of tuning a SQL Server database? The goal is to improve performance until

acceptable levels are reached. Acceptable levels can be defined in a number of ways. For a

large online transaction processing (OLTP) application the performance goal might be to

provide subsecond response time for critical transactions and to provide a response time of

less than two seconds for 95 percent of the other main transactions. For some systems,

typically batch systems, acceptable performance might be measured in throughput. For

example, a settlement system may define acceptable performance in terms of the number of

trades settled per hour. For an overnight batch suite acceptable performance might be that it

must finish before the business day starts.

Whatever the system, designing for performance should start early in the design process and

continue after the application has gone live. Performance tuning is not a one-off process but

an iterative process during which response time is measured, tuning performed, and response

time measured again.

There is no right way to design a database; there are a number of possible approaches and all

these may be perfectly valid. It is sometimes said that performance tuning is an art, not a

science. This may be true, but it is important to undertake performance tuning experiments

with the same kind of rigorous, controlled conditions under which scientific experiments are

performed. Measurements should be taken before and after any modification, and these

should be made one at a time so it can be established which modification, if any, resulted in

an improvement or degradation.

What areas should the database designer concentrate on? The simple answer to this question

is that the database designer should concentrate on those areas that will return the most

benefit. In my experience, for most database designs I have worked with, large gains are

typically made in the area of query and index design. As we shall see later in this book,

inappropriate indexes and badly written queries, as well as some other contributing factors,

can negatively influence the query optimizer such that it chooses an inefficient strategy.

To give you some idea of the gains to be made in this area I once was asked to look at a query

that joined a number of large tables together. The query was abandoned after it had not

completed within 12 hours. The addition of an index in conjunction with a modification to the

query meant the query now completed in less than eight minutes! This magnitude of gain

cannot be achieved just by purchasing more hardware or by twiddling with some arcane SQL

Server configuration option. A database designer or administrator's time is always limited, so

make the best use of it! The other main area where gains can be dramatic is lock contention.

Removing lock bottlenecks in a system with a large number of users can have a huge impact

on response times.

Now, some words of caution when chasing performance problems. If users phone up to tell

you that they are getting poor response times, do not immediately jump to conclusions about

what is causing the problem. Circle at a high altitude first. Having made sure that you are

about to monitor the correct server use the System Monitor to look at the CPU, disk

subsystem, and memory use. Are there any obvious bottlenecks? If there are, then look for the

culprit. Everyone blames the database, but it could just as easily be someone running his or

her favorite game! If there are no obvious bottlenecks, and the CPU, disk, and memory

counters in the System Monitor are lower than usual, then that might tell you something.

Digital Press - MS SQL Server 2000 Performance Optimization And Tuning Handbook (2001).pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: