Professional SQL server 2008 internals and troubleshooting

599 Pages·2010·13.078 MB·English

by  Christian Bolton, et al

PROFESSIONAL
SQL Server® 2008 Internals
and Troubleshooting
Christian Bolton
Justin Langford
Brent Ozar
James Rowland-Jones
Jonathan Kehayias
Cindy Gross
Steven Wort
8844228899ffffiirrss..iinndddd vv 1111//2233//0099 11::3355::4422 PPMM
Professional SQL Server® 2008 Internals and Troubleshooting
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-48428-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, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the
Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax (201)
748-6008, 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 specifi cally disclaim all warranties, including
without limitation warranties of fi tness for a particular purpose. No warranty may be created or extended by sales or pro-
motional 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 pub-
lisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site 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 Web site may provide or recommendations it may make. Further, readers
should be aware that Internet Web sites 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 please contact our Customer Care Department within the
United States at [phone], outside the United States at [phone] or fax [phone].
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available
in electronic books.
Library of Congress Control Number: 2009941346
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 affi liates, in the United States and other coun-
tries, and may not be used without written permission. SQL Server is a registered trademark of Microsoft 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.
8844228899ffffiirrss..iinndddd vvii 1111//2233//0099 11::3355::4422 PPMM
ABOUT THE AUTHORS
CHRISTIAN BOLTON is the Technical Director for Coeo Ltd., a leading provider of
SQL Server consulting and managed support services in the UK and Europe. Prior to
this Christian worked for 5 years at Microsoft, leading the SQL Server Premier Field
Engineering team in the UK. He is a Microsoft Certifi ed Architect, Master and MVP
for SQL Server, and co-author of Professional SQL Server 2005 Performance Tuning.
He works out of London and lives in the south of England with his wife and children. He can be
contacted at [email protected]. Christian authored chapters 1, 2, 7, 13 and the online chapter 16
in addition to lending his authoring expertise where needed on other chapters and functioned as the
lead author for the entire project.
JUSTIN LANGFORD leads the Managed Support team for Coeo Ltd, delivering out-
sourced 24x7 operations for mission-critical SQL Server platforms. Before join-
ing Coeo, Justin worked for Microsoft in the Premier Field Engineering team and
has worked with some of Microsoft’s largest fi nance and government customers in
Europe. Justin co-authored Wrox Professional SQL Server 2005 Performance Tuning
and lives in London with his girlfriend, Claire. Outside work he enjoys sailing and has a keen inter-
est in classic British sports cars. Justin can be contacted at [email protected]. Justin authored chap-
ters 9, 11, 12, and 15.
BRENT OZAR is a SQL Server Expert for Quest Software. He has over a decade of
broad IT experience, performing SQL Server database administration, systems admin-
istration, SAN administration, virtualization administration, and project management.
In his current role, Brent trains DBAs on performance tuning, disaster recovery, and
virtualization. He has spoken around the globe at events for PASS, SQLBits, SSWUG,
and other organizations. Brent founded the Virtualization Virtual Chapter for the Professional
Association for SQL Server (PASS), and serves as the Editor-in-Chief at SQLServerPedia.com.
Brent blogs at www.BrentOzar.com and discusses diverse topics at http://twitter.com/BrentO. When
he’s not talking SQL Server, he enjoys traveling, working with social media, snorkeling, and sam-
pling new restaurants. He is the author of chapters 4 and 14.
JAMES ROWLAND-JONES works for EMC Consulting EMEA as an Advisory Con-
sultant. His principle focus is the delivery of large, scalable, data warehousing, and
business intelligence projects. Within this fi eld James specializes in data integration,
database architecture, and performance tuning. He is very active in the technical
community and is one of the organizers for SQLBits, Europe’s largest SQL Server
community conference. James has received the Microsoft MVP award for 2009 and 2010. You can
fi nd him online at http://consultingblogs.emc.com/jamesrowlandjones, twitter (@jrowlandjones),
linkedin http://www.linkedin.com/in/jrowlandjones, or even using an old fashioned email,
[email protected]. James authored chapters 6 and 10.
8844228899ffffiirrss..iinndddd iixx 1111//2233//0099 11::3355::4433 PPMM
JONATHAN KEHAYIAS is a SQL Server MVP, MCITP Database Administrator and
Developer, who got started in SQL Server in 2004 as a database developer and report
writer in the natural gas industry. After spending two and a half years working in
T-SQL, in late 2006, he transitioned to the role of Database Administrator. He has
experience in upgrading and consolidating SQL environments, and has experience in
running SQL Server in Virtual Environments on VMWare ESX 3.5+. He is a member of the Tampa
SQL Server User Group and a regular speaker about SQL Server at events. Jonathan authored
chapter 3.
CINDY GROSS started her SQL Server life as a DBA with a hospital and health plan company in
1993, and moved to Microsoft in 2000 where she has worked ever since. Her roles at Microsoft
have included PSS Product Support Engineer, SQL Content Lead, Yukon Readiness Lead, and most
recently Dedicated Support Engineer (DSE), all for SQL Server. Cindy enjoys delivering training
throughout the United States as well as in Europe and India, including presentations at SQL PASS.
In 2008 she started the Boise SQL Server User Group, an affi liate of the SQLCommunity.org site
(where she is a worldwide SQL Server Community Leader) to share SQL Server wisdom in the Idaho
SQL Server community. Most recently she obtained the Microsoft Certifi ed Master: SQL Server
Qualifi cation, which was a hard-fought prize. Over the years Cindy has learned from many won-
derful friends and colleagues and they all deserve a word of thanks for contributing to her success.
Cindy authored chapter 8.
STEVEN WORT has been working with SQL Server since the early days of SQL
Server way back in 1992-93. He is currently a developer in the Windows Division at
Microsoft, where he works on performance and scalability issues on large database
systems for the Windows Telemetry team. Steven has been at Microsoft since 2000.
Prior to working in the Windows Division, Steven spent 2 years working in the SQL
Server group, working on performance and scalability. Steven’s fi rst 3 years at Microsoft were spent
working in support 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 scal-
ability issues. 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 fi tness activities in the
outdoors of the Pacifi c Northwest. Steven authored chapter 5.
8844228899ffffiirrss..iinndddd xx 1111//2233//0099 11::3355::4433 PPMM
CONTENTS
INTRODUCTION XXV
CHAPTER 1: SQL SERVER ARCHITECTURE 1
Database Transactions 2
ACID Properties 2
SQL Server Transactions 3
The Life Cycle of a Query 3
The Relational and Storage Engines 4
The Buff er Pool 4
A Basic Select Query 5
A Simple Update Query 14
Recovery 16
The SQLOS (SQL Operating System) 20
Summary 22
CHAPTER 2: UNDERSTANDING MEMORY 23
Windows and Memory 24
Physical Memory 24
Virtual Address Space 27
Virtual Memory Manager 29
Tuning 32-Bit Systems 31
Tuning 64-Bit Systems 45
SQL Server Memory 47
Memory Nodes 47
Memory Clerks, Caches, and the Buff er Pool 47
Summary 58
CHAPTER 3: SQL SERVER WAITS AND EXTENDED EVENTS 59
Waits 60
SQL Server Execution Model 60
Understanding Wait Statistics 61
Wait Types 64
8844228899ffttoocc..iinndddd xxvv 1111//2233//0099 22::5500::0088 PPMM
CONTENTS
Extended Events 70
Architecture 71
Event Sessions 81
Using the Extended Events Manager 87
Examining Waits with Extended Events 90
Summary 94
CHAPTER 4: WORKING WITH STORAGE 95
Types of Storage 95
Understanding Individual Drives 96
Protecting Data with RAID 98
Direct Attached Storage 105
Storage Area Networks 105
Storage Performance Testing 112
Choosing a Storage Testing Tool 113
Interpreting Storage Test Results 119
Confi guring Software for Storage 123
Confi guring Windows Server 123
Confi guring SQL Server 2008 128
Corruption 131
Summary 135
CHAPTER 5: CPU AND QUERY PROCESSING 137
The CPU 138
The CPU and SQL Server 139
Processor Speed 139
Hyper-Threading 140
Licensing with Multicore and Hyper-Threading 141
Cache 141
Multicore Processors 143
System Architecture 144
Symmetric Multiprocessing 145
NonUniform Memory Access 145
Query Processing 148
Parsing 148
Binding 149
Query Optimization 149
Parallel Plans 151
Algebrizer Trees 151
xvi
8844228899ffttoocc..iinndddd xxvvii 1111//2233//0099 22::5500::0088 PPMM
CONTENTS
sql_handle or plan_handle 152
Statistics 152
Plan Caching and Recompilation 154
Infl uencing Optimization 159
Query Plans 165
Query Plan Operators 168
Reading Query Plans 171
Executing Your Queries 175
SQLOS 175
Summary 183
CHAPTER 6: LOCKING AND LATCHES 185
Transactions 187
Atomic 187
Consistent 187
Isolated 188
Durable 188
Consequence of Concurrent Access 188
Lost Updates 189
Dirty Reads 191
Non-Repeatable Reads 192
Phantom Reads 193
Double Reads 196
Halloween Eff ect 198
Locking 198
Viewing Locks — sys.dm_tran_locks 199
Lock Granularity 202
Lock Modes 205
Lock Hierarchy 212
Lock Compatibility 213
Lock Escalation 214
Deadlocking 216
Pessimistic Concurrency 219
Pessimistic Isolation Levels 220
Concurrency vs. Isolation 231
Optimistic Concurrency 231
Optimistic Isolation Levels 232
How Row Versioning Works 240
Row Versioning Deep Dive 240
Monitoring Row Versioning 249
xvii
8844228899ffttoocc..iinndddd xxvviiii 1111//2233//0099 22::5500::0088 PPMM
CONTENTS
Latches 251
Latch Types 251
BUF Latch Architecture 255
Latch Modes 258
Grant Order 260
Latch Waits and Blocking 261
Sub-latches and Super-latches 262
Latching in Action 263
Without Latching 266
With Latching 267
Summary 268
CHAPTER 7: KNOWING TEMPDB 269
Overview and Usage 270
User Temporary Objects 271
Internal Temporary Objects 275
The Version Store 276
Troubleshooting Common Issues 279
Latch Contention 279
Monitoring Tempdb Performance 289
Troubleshooting Space Issues 291
Transaction Log Growing Too Big? 295
Confi guration Best Practices 296
Tempdb File Placement 296
Tempdb Initial Sizing and Autogrowth 300
Confi guring Multiple Tempdb Data Files 303
Summary 303
CHAPTER 8: DEFINING YOUR APPROACH TO TROUBLESHOOTING 305
Approaching the Problem Correctly 306
Having the Right Attitude 306
Dealing with Management 307
When to Call for Outside Help 308
Defi ning the Problem 309
Tips for Identifying a Problem 309
Bite-Size Chunks 310
Service-Level Agreements 311
Defi ning Exit Criteria 311
Understanding Your Baselines 311
Events and Alerts 315
xviii
8844228899ffttoocc..iinndddd xxvviiiiii 1111//2233//0099 22::5500::0088 PPMM
CONTENTS
Gathering Data 315
Understanding the Data Gathering Process 315
Tools and Utilities 316
Analyzing Data 317
SQL Nexus 317
Profi ler 317
Database Tuning Advisor 318
Visual Studio Database Edition 318
Testing Solutions 318
Troubleshooting Other Components 318
Failover Clustering 318
Replication 319
Analysis Services 322
Summary 327
CHAPTER 9: VIEWING SERVER PERFORMANCE WITH
PERFMON AND THE PAL TOOL 329
Performance Monitor Overview 330
Reliability and Performance Monitor in Windows Server 2008 330
New Counters for SQL Server 2008 in PerfMon 333
Troubleshooting SQL Server Problems 335
Getting Started with PerfMon 338
Monitoring Real-Time Server Activity 338
Starting Out with Data Collector Sets 341
Working with User-Defi ned Data Collector Sets 342
What to Be Aware of When Running PerfMon 346
The Impact of Running PerfMon 346
Servers with Very Poor Performance 348
Common PerfMon Problems 348
Getting More from Performance Monitor 349
Identifying SQL Server Bottlenecks 350
Wait Stats Analysis 356
Getting a Performance Baseline 357
Getting Started with Performance Analysis for Logs (PAL) 357
Templates and PAL 358
Capturing PerfMon Logs 358
Using PAL for Log Analysis 359
Other PerfMon Log Analysis Tools 363
Using SQL Server to Analyze PerfMon Logs 364
Combining PerfMon Logs and SQL Profi ler Traces 364
xix
8844228899ffttoocc..iinndddd xxiixx 1111//2233//0099 22::5500::0088 PPMM
CONTENTS
Using Relog 365
Using LogMan 366
Using LogParser 367
Summary 368
CHAPTER 10: TRACING SQL SERVER WITH SQL TRACE
AND PROFILER 369
Tracing 101 370
Why Trace 370
When to Trace 370
Where to Trace 370
What to Trace 371
How to Trace 371
The Architecture of SQL Trace 372
Event Classifi cation and Hierarchies 374
SQL Trace Catalog Views 376
sys.traces 376
sys.trace_categories 386
sys.trace_events 387
sys.trace_columns 387
sys.trace_subclass_values 389
sys.trace_event_bindings 391
SQL Trace Procedures and Functions 392
sp_trace_create 392
sp_trace_setevent 394
sp_trace_setfi lter 400
sp_trace_setstatus 403
sp_trace_generateevent 404
fn_trace_gettable 406
fn_trace_geteventinfo 408
fn_trace_getfi lterinfo 409
Securing SQL Trace 410
Tracing Login Creation/Deletion 411
Logins Changing Passwords 412
When Tracing Login Can View Object Defi nitions
and Parameter Values 414
Securing the Output of SQL Trace 418
xx
8844228899ffttoocc..iinndddd xxxx 1111//2233//0099 22::5500::0088 PPMM