Professional SQL Server 2005 Performance Tuning.pdf

Programmer to Programmer TM

BROWSE BOOKS

P2P FORUM

FREE NEWSLETTER

ABOUT WROX

at Wrox.com!

Special Deals

Free Chapter Excerpts

Take advantage of special offers

every month

Be the ﬁ rst to preview chapters from

the latest Wrox publications

Unlimited Access. . .

Forums, Forums, Forums

. . . to over 200 of our books in the

Wrox Reference Library. (see more

details online)

Take an active role in online

discussions with fellow programmers

Meet Wrox Authors!

Read running commentaries from authors on their programming experiences

and whatever else they want to talk about

Browse Books

.NET

SQL Server

Java

XML

Visual Basic

C#/ C++

Join the community!

newsletter.wrox.com

Get more Wrox

Professional

SQL Server ® 2005

Performance Tuning

Acknowledgments

viii

Introduction

xix

Part I: Finding Bottlenecks when Something’s Wrong ............ 1

Chapter 1: Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

Chapter 2: Monitoring Server Resources with SystemMonitor . . . . . . . . . . . . . . 15

Chapter 3: Monitoring SQL Server Resources with SystemMonitor . . . . . . . . . 39

Chapter 4: SQL Server Wait Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

Chapter 5: Finding ProblemQueries with SQL Proﬁler . . . . . . . . . . . . . . . . . . . . . . . 93

Part II: Removing Bottlenecks with Tuning ....................... 141

Chapter 6: Choosing and Conﬁguring Hardware . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

Chapter 7: Tuning SQL Server Conﬁguration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171

Chapter 8: Tuning the Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189

Chapter 9: Tuning T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239

Part III: Preventative Measures and Baselining Performance

with Tools ..................................................... 319

Chapter 10: Capturing, Measuring, and Replaying a Workload Using

SQLProﬁler .................................................321

Chapter 11: Tuning Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353

Chapter 12: How Fast and Robust Is Your Storage?. . . . . . . . . . . . . . . . . . . . . . . . 409

Chapter 13: SQL Server 2005 Performance Dashboard Reports . . . . . . . . . . . . 471

Part IV: Roadmap to Server Performance ........................ 495

Chapter 14: Best Practices for Designing for Performance from the Start . . . 497

Chapter 15: Successful Deployment Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . 523

Index ..................................................................... 539

Professional

SQL Server ® 2005

Performance Tuning

Steven Wort

Christian Bolton

Justin Langford

Michael Cape

Joshua J. Jin

Douglas Hinson

Haidong Ji

Paul A. Mestemaker

Arindam Sen

Wiley Publishing, Inc.

2008 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-17639-9

Manufactured in the United States of America

10987654321

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, scanning or otherwise, except as

permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior

written permission of the Publisher, or authorization through payment of the appropriate per-copy fee

to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978)

646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley

Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or

online a t http://www.wiley.com/go/permissions .

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or

warranties with respect to the accuracy or completeness of the contents of this work and speciﬁcally

disclaim all warranties, including without limitation warranties of ﬁtness for a particular purpose. No

warranty may be created or extended by sales or promotional materials. The advice and strategies

contained herein may not be suitable for every situation. This work is sold with the understanding that

the publisher is not engaged in rendering legal, accounting, or other professional services. If professional

assistance is required, the services of a competent professional person should be sought. Neither the

publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or

Website is referred to in this work as a citation and/or a potential source of further information does not

mean that the author or the publisher endorses the information the organization or Website may provide

or recommendations it may make. Further, readers should be aware that Internet Websites listed in this

work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services or to obtain technical support, please contact

our Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax

(317) 572-4002.

Library of Congress Cataloging-in-Publication Data

Professional SQL server 2005 : performance tuning / Steven Wort ... [et al.].

p. cm.

Includes bibliographical references and index.

ISBN 978-0-470-17639-9 (paper/website)

1. SQL server. 2. Client/server computing. I. Wort, Steven.

QA76.9.C55P752 2008

005.75’85--dc22

2007045707

Trademarks : Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related

trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its afﬁliates, in

the United States and other countries, and may not be used without written permission. SQL Server is

a registered trademark of Micosoft Corporation in the United States and/or other countries. All other

trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any

product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may

not be available in electronic books.

Professional SQL Server ® 2005 Performance Tuning

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: