Thư viện tri thức trực tuyến
Kho tài liệu với 50,000+ tài liệu học thuật
© 2023 Siêu thị PDF - Kho tài liệu học thuật hàng đầu Việt Nam
Tài liệu Professional SQL Server 2005 Performance Tuning doc
Nội dung xem thử
Mô tả chi tiết
Professional
SQL Server®
2005 Performance Tuning
Enhance Your Knowledge
Advance Your Career
Professional SQL Server 2005
Performance Tuning
978-0-470-17639-9
This book is for consultants, developers, DBAs, architects, or
anyone with an interest in SQL performance. A working knowledge of
T-SQL and knowledge of how to perform basic SQL Server and OS
administrative tasks is necessary.
Professional SQL Server 2005
Reporting Services
978-0-7645-8497-8
This book is for report designers, developers, administrators, and
business professionals interested in learning the advanced functionality, report, server administration, and security issues of SQL
Server 2005 Reporting Services.
Professional SQL Server 2005 CLR Programming:
with Stored Procedures, Functions, Triggers,
Aggregates, and Types
978-0-470-05403-1
This book is for developers and architects who are familiar with
.NET concepts as well as DBAs who, although developers in
their own right, may be slightly less up to date on .NET. A solid
grounding in T-SQL is necessary.
Professional SQL Server 2005
Integration Services
978-0-7645-8435-0
This book offers best practices in design and development,
architectural approaches for reusability, and connectivity to
various other popular systems using Integration Services.
Professional SQL Server 2005 Programming
978-0-7645-8434-3
This book shows experienced developers how to master the
substantially revamped feature set for SQL Server 2005. Covers
such advanced topics as methods for handling stored procedures,
scripting and error handling, XML and XQuery support, security
and performance tuning, and sophisticated database design.
Beginning SQL Server 2005 Programming
978-0-7645-8433-6
A comprehensive introduction to SQL Server 2005. Addresses
creating and changing tables, managing keys, database normalization, writing scripts, working with stored procedures, programming
with XML, and using SQL Server reporting and data transformation
services.
Get more Wrox
Programmer to Programmer TM
at Wrox.com!
Special Deals
Take advantage of special offers
every month
Free Chapter Excerpts
Be the fi rst to preview chapters from
the latest Wrox publications
Unlimited Access. . .
. . . to over 200 of our books in the
Wrox Reference Library. (see more
details online)
Forums, Forums, Forums
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
Join the community!
Sign up for our free monthly newsletter at
newsletter.wrox.com
BROWSE BOOKS P2P FORUM FREE NEWSLETTER ABOUT WROX
Browse Books
.NET
SQL Server
Java
XML
Visual Basic
C#/C++
spine=1.152"
www.it-ebooks.info
Wort ffirs.tex V3 - 12/19/2007 3:48pm Page i
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 System Monitor . . . . . . . . . . . . . . 15
Chapter 3: Monitoring SQL Server Resources with System Monitor . . . . . . . . . 39
Chapter 4: SQL Server Wait Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Chapter 5: Finding Problem Queries with SQL Profiler . . . . . . . . . . . . . . . . . . . . . . . 93
Part II: Removing Bottlenecks with Tuning....................... 141
Chapter 6: Choosing and Configuring Hardware . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Chapter 7: Tuning SQL Server Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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
SQL Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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
www.it-ebooks.info
Wort ffirs.tex V3 - 12/19/2007 3:48pm Page ii
www.it-ebooks.info
Wort ffirs.tex V3 - 12/19/2007 3:48pm Page iii
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.
www.it-ebooks.info
Wort ffirs.tex V3 - 12/19/2007 3:48pm Page iv
Professional SQL Server® 2005 Performance Tuning
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2008 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-17639-9
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
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 at 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 specifically
disclaim all warranties, including without limitation warranties of fitness 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 affiliates, 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.
www.it-ebooks.info
Wort fauth.tex V3 - 12/17/2007 1:57pm Page v
About the Authors
Steven Wort has been working with SQL Server for the past 14 years. He is currently a developer in the
Windows group at Microsoft where he works on performance and scalability issues on a large database
system. Steven has been at Microsoft for nearly 7 years, working in the Windows group for the past
2 years. Prior to this, Steven spent 2 years in the SQL Server group working on performance and scalability. His first job at Microsoft was 3 years spent working in what is now CSS as an escalation engineer on
the SIE team. During this time Steven was able to travel the world working with some of Microsoft’s customers on their performance and scalability problems. Before coming to Microsoft, Steven spent 20 years
working in the United Kingdom as a freelance consultant specializing in database application development. When Steven isn’t busy working, he can be found spending time with his family and enjoying
many fitness activities in the great outdoors of the Pacific Northwest.
Christian Bolton has been working with SQL Server since 1999 and in 2007 became a director and
database architect for Coeo Ltd, a Microsoft Certified Partner focused on large-scale and complex SQL
Server projects in the United Kingdom. Prior to this, Christian worked for 5 years as a senior premier
field engineer for Microsoft UK, working with some of Microsoft’s biggest customers across EMEA. His
specialist areas are high availability, scalability, and performance tuning. Christian works out of London
and lives in the south of England with his wife and daughter. He can be contacted at http://coeo.com
or through his blog at http://sqlblogcasts.com/blogs/christian.
Justin Langford has worked as a premier field engineer for Microsoft specializing in SQL Server for the
past 3 years. Much of this time has been focused on sharing best practices for operations and optimization
with some of the United Kingdom’s largest financial and government organizations. Justin previously
worked as a consultant for a Microsoft Partner focusing on upgrade, migration, and software deployment
projects for enterprise customers. Outside of work, Justin enjoys yacht racing, snowboarding, and has a
keen interest in classic British sports cars.
Michael Cape is a database developer with experience in a variety of industries. Those industries are
mortgage banking, pension administration, advertising, logistics, insurance, and labor management.
Michael holds a BSCS degree and got his start with database development with SQLBase from Gupta.
Michael also has 5 years experience with DB2, and has been working with SQL Server, starting with
version 7, for the last 7 years. Outside work, Michael spends time with his wife and two children. He also
enjoys golf, bicycling, fishing, and kite flying.
Joshua Jin works for the Intel Corporation. He is a certified SQL Server MCITP database administrator,
MCITP database developer, and MCITP business intelligence developer. He specializes in the
performance tuning of large-scale and high-volume SQL databases. Prior to working at Intel, he worked
on the largest Internet banking implementation in the United States, using SQL server as its database
engine. He can be reached at [email protected].
Douglas Hinson is an independent software and database consultant in the logistics and financial
industries, with an extensive SQL Server background. He has co-authored several Wrox books, including
Professional SQL Server 2005 Integration Services.
www.it-ebooks.info
Wort fauth.tex V3 - 12/17/2007 1:57pm Page vi
About the Authors
Haidong ‘‘Alex’’ Ji is a professional trainer and consultant specializing in SQL Server administration, performance tuning, high availability, and many other facets of SQL Server. In addition, he also
excels at database interoperability issues, having worked extensively with Oracle and MySQL on Unix
and Linux. Haidong enjoys learning and sharing his expertise through technical writing, speaking,
consulting, training, and mentoring. He co-authored Professional SQL Server 2005 Integration Services
(Wrox Press) and Professional SQL Server 2005 Administration (Wrox Press). Haidong maintains a blog at
www.haidongji.com/category/technology/. He can be contacted at [email protected].
Paul Mestemaker is a program manager at Microsoft on the SQL Server product team. During the
SQL Server 2005 product cycle, he worked closely with the new dynamic management views
on the SQL Server Engine team. Following the launch, Paul moved to the SQL Server Manageability
team to create tools on top of the new SQL platform technologies. He was influential in the release of
SQL Server 2005 Best Practices Analyzer, Performance Dashboard Reports, and SQL Server 2005 Service
Pack 2. He is now a member of the SQLCAT Best Practices team, where he works with subject matter experts across Microsoft and in the community to develop new rules for SQL BPA. Paul has been a
speaker at TechEd, PASS, Connections, and other Microsoft conferences. He blogs occasionally; you can
check it out here: http://blogs.msdn.com/sqlrem/.
Arindam Sen has worked with SQL Server for the past 8 years and has significant experience with
Siebel deployments using SQL Server databases. His interests lie in the area of high availability and
performance tuning. He is an MCSE, MCSA, MCAD, and MCDBA. He won the SQL Server innovator
award (SQL Server Magazine) in 2003 and 2004. He holds an engineering degree in electronics and an
MBA from Duke University.
vi
www.it-ebooks.info
Wort fcre.tex V3 - 12/17/2007 1:58pm Page vii
Credits
Executive Editor
Robert Elliott
Development Editor
Kelly Talbot
Technical Editors
Sunil Agarwal
Uttam Parui
Alan Doby
Stephen Olson
Production Editor
Dassi Zeidel
Copy Editor
Jack Peters
Editorial Manager
Mary Beth Wakefield
Production Manager
Tim Tate
Vice President and Executive Group Publisher
Richard Swadley
Vice President and Executive Publisher
Joseph B. Wikert
Project Coordinator, Cover
Lynsey Osborn
Proofreader
Ian Golder
Indexer
Robert Swanson
www.it-ebooks.info
Wort fack.tex V3 - 12/17/2007 1:58pm Page viii
Acknowledgments
We have to start by thanking our families. They have been infinitely patient and understanding while we
have spent many long hours working on this book. Thanks to all the team at Wiley Publishing. Robert
Elliot who got me started on this project, Kelly Talbot for his patience and guidance along the way, and
everyone else in the team who took the writings of the authors and turned them into this great book.
Thanks to all the technical editors. Thanks to everyone at Microsoft in the SQL Server group, on the
many discussion lists, and in CSS who were so willing to share their knowledge.
www.it-ebooks.info
Wort ftoc.tex V3 - 12/17/2007 2:00pm Page ix
Contents
Introduction xix
Part I: Finding Bottlenecks when Something’s Wrong
Chapter 1: Performance Tuning 3
Art or Science? 3
The Science of Performance Tuning 4
The Problem Statement 4
The Plan of Attack 6
Data Collection 6
Data Analysis 7
Performance Tuning Applied 7
Example 1 — Your Application Is Slow 7
Example 2 — Stored Procedure X Is Slow 9
Tools 10
Preventative Measures 13
Summary 14
Chapter 2: Monitoring Server Resources with System Monitor 15
Why Might I Need System Monitor? 15
When Should I Use System Monitor? 16
Performance Monitor Overview 16
Getting Started with System Monitor 18
Performance Logs and Alerts 22
What’s the Impact of Running System Monitor? 22
Managing Monitoring Impact 23
Capturing at the Right Time, for the Right Duration 23
How Much Data Will System Monitor Generate? 23
Resource Utilization 24
Kernel Mode versus Application Mode 25
Identifying Bottlenecks 25
Locating Memory Bottlenecks 25
Discovering Disk Bottlenecks 27
Identifying CPU Bottlenecks 28
www.it-ebooks.info
Wort ftoc.tex V3 - 12/17/2007 2:00pm Page x
Contents
Using System Monitor Proactively 29
Running System Monitor on 64-bit Systems 29
Combining System Monitor Logs and SQL Profiler Traces 30
Monitoring Remote Servers 31
Best Practices for System Monitor 31
Taking a Baseline 31
Retaining Performance Logs 32
Patterns and Trends 32
Servers Suffering Very Poor Performance 32
Tuning Performance 32
Being Proactive 32
My System Monitor Counters Are Missing — What Should I Do? 33
Built-in Log Management Tools 34
LogMan 35
Relog 35
Analyzing Log Data 35
LogParser 36
Performance Analysis of Logs 36
Summary 38
Chapter 3: Monitoring SQL Server Resources with System Monitor 39
The Beginning 40
Types of Performance Problems 40
Configuration-Based Performance Problems 41
Schema-Based Performance Problems 41
Types of Bottlenecks 42
Memory Bottlenecks 43
Types of Memory Bottlenecks 43
Confirming Memory Bottlenecks 44
Configuration-Based Memory Bottlenecks 45
Schema-Based Memory Bottlenecks 46
CPU Bottlenecks 46
Confirming CPU Bottlenecks 47
Configuration-Based CPU Bottlenecks 48
Schema-Based CPU Bottlenecks 48
Disk Bottlenecks 49
Confirming Disk Bottlenecks 50
Configuration-Based Disk Bottlenecks 50
Schema-Based Disk Bottlenecks 53
Monitoring Database Mirroring Performance 54
x
www.it-ebooks.info
Wort ftoc.tex V3 - 12/17/2007 2:00pm Page xi
Contents
Monitoring Wait Statistics 55
Typical Performance Problems 55
Typical Disk Problems 56
Memory 56
Typical CPU Problems 57
Using SQL Server to Analyze Performance Logs 59
Combining Performance Monitor Logs and SQL Profiler Trace 64
Summary 65
Chapter 4: SQL Server Wait Types 67
SQL Server Waits 68
Architecture 68
Common or Noteworthy Resource Wait Types 69
How to Track Waits 70
sys.dm−exec−requests–Session Level Information Only 70
sys.dm−os−waiting−tasks–All Waiting Tasks 71
sys.dm−os−wait−stats — Aggregated Times by Wait Type 71
DMVStats 72
Performance Dashboard 73
Wait Stats in Action 73
Locking and Blocking 79
Concurrency 79
Transactions 81
Isolation Levels 81
Locks and Locking 82
Row Versioning 85
Monitoring for Deadlocks 86
Monitoring for Blocking Locks 89
Summary 91
Chapter 5: Finding Problem Queries with SQL Profiler 93
Preparations for Setting Up a Trace 93
Checking for a Complete ‘‘Issue’’ Statement 94
Searching for Related Known Issues 94
Thinking in Terms of SQL Trace Terminologies 94
SQL Trace Options and Considerations 99
Capturing Blocking Events 104
Capturing Showplan XML Data 107
Capturing Deadlock Graphs 110
xi
www.it-ebooks.info
Wort ftoc.tex V3 - 12/17/2007 2:00pm Page xii
Contents
Identifying Long-Running Queries Using SQL Profiler 114
Simulating a Scenario and a Sample Database 115
Analyzing the Issue 115
Setup 115
Tracing 118
Analysis and Tuning 119
Case Summary 122
Tracing Costly Queries by Using Profiler to Generate Server-Side
Trace Code 122
Using Profiler to Generate Server-Side Trace Script 123
Executing Server-Side Scripts 126
Handling Trace Files and Analyzing Trace Data 126
Server-Side Trace Code Walk-Through 131
Case Summary 137
Correlating a Profiler Trace with System Monitor Performance Counter Data 137
Summary 139
Part II: Removing Bottlenecks with Tuning
Chapter 6: Choosing and Configuring Hardware 143
Server Bottlenecks 143
Memory 144
I/O 144
CPU 144
Configuring the Server 145
Memory 147
I/O 153
CPU 162
Summary 170
Chapter 7: Tuning SQL Server Configuration 171
Considerations before Adjusting Server Level Settings 171
Inspecting Current SQL Server Settings 172
Inspecting Server Settings with SQL Server Management Studio 172
Inspecting Server Settings with Scripts 173
Important Server Settings: CPU, Memory, and I/O 175
Memory 182
Network I/O 182
Summary 187
xii
www.it-ebooks.info
Wort ftoc.tex V3 - 12/17/2007 2:00pm Page xiii
Contents
Chapter 8: Tuning the Schema 189
Data Quality 189
Normalization 189
Denormalization 196
Defining Columns 197
Stored Procedures 214
Triggers 218
Data Performance 221
Pages 221
Partitioning 224
Concurrency and Locking (and Blocking) 227
Locks 229
Indexing 232
Summary 237
Chapter 9: Tuning T-SQL 239
Opening Move: The Optimizer’s Gameplan 239
Cost-Based Optimization 240
Reading Index Statistics 241
Include Actual Execution Plan Misconception 242
Use sp−helpindex to Examine Indexes 242
Middle Game: Gathering the Facts 243
Query Plans 243
Query Plan Essentials 244
Buffer Pool 263
End Game: Performance Tuning T-SQL 268
Essential Tools 269
Prerequisite Indexes for Examples 270
Tuning T-SQL with a New Approach 270
NOT IN and NOT EXISTS Rewrites are in the Past 270
Rewriting by Pushing Predicates Deeper into Plans 271
Using Temp Tables for Intermediary Results 273
User-Defined Functions in SELECT Statements 274
Reworking SELECT ∗ 276
Tuning the T-SQL Predicate 280
Removing Certain Implicit Conversions 280
Using Searchable Arguments 282
Tuning T-SQL to Use Indexes 285
Minimizing Bookmark Lookups 285
Ordering Results 288
xiii
www.it-ebooks.info
Wort ftoc.tex V3 - 12/17/2007 2:00pm Page xiv
Contents
Handling Indexed Nullable Columns 288
Eliminating Duplicates 290
Tuning T-SQL Common Patterns or Idioms 292
Singleton SELECT Statements 292
Aggregates and CTEs 295
Derived Tables and Correlated Subqueries 298
Paging and Sorting 302
Tuning the T-SQL Statement with Hints 309
Query Plan Caching Issues 309
Tuning for Deadlocking and Blocking 312
Blocking 312
Deadlocks 312
Preventative Measures 313
Simulated Stress Testing for Query Plans 316
Summary 318
Part III: Preventative Measures and Baselining Performance
with Tools
Chapter 10: Capturing, Measuring, and Replaying a Workload Using
SQL Profiler 321
Capturing Workloads for Replay 321
Characterizing a Workload for Replay 322
Meeting Requirements for Workload Replays 324
Capturing a Workload 326
Modifying a Workload in a Trace Table for Special Needs 327
Measuring Workload Performance 330
Preliminary Analysis of the Workload 330
New Performance Reference for a Workload Replay 334
Replaying a Workload 337
Workload Generation for Case Scenarios 338
Scenario 1: Validating Performance Improvement 340
Scenario 2: Replaying a Workload in Different Environments and Measuring Overall
Response Time 343
Scenario 3: Replaying Multiple Workloads on a Target Server 346
Summary 352
Chapter 11: Tuning Indexes 353
Sample Database 353
Section 1: Indexing Review 355
B-Trees 355
xiv
www.it-ebooks.info