Table Of ContentHigh Performance SQL Server
Troubleshooting SQL Server
A Guide for the Accidental DBA
Jonathan Kehayias and Ted Krueger
Foreword by Paul Randal
Technical review by Gail Shaw
ISBN: 978-1-906434-77-9
Troubleshooting SQL Server
A Guide for the Accidental DBA
By Jonathan Kehayias and Ted Krueger
With a Foreword from Paul S. Randal
First published by Simple Talk Publishing September 2011
Copyright Jonathan Kehayias and Ted Krueger 2011
ISBN 978-1-906434-77-9
The right of Jonathan Kehayias and Ted Krueger to be identified as the authors of this work has been
asserted by them in accordance with the Copyright, Designs and Patents Act 1988.
All rights reserved. No part of this publication may be reproduced, stored or introduced into a retrieval
system, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or
otherwise) without the prior written consent of the publisher. Any person who does any unauthorized act
in relation to this publication may be liable to criminal prosecution and civil claims for damages.
This book is sold subject to the condition that it shall not, by way of trade or otherwise, be lent, re-sold,
hired out, or otherwise circulated without the publisher's prior consent in any form other than which it is
published and without a similar condition including this condition being imposed on the subsequent
publisher.
Edited by Tony Davis
Technical Review and Additional Material: Gail Shaw
Cover Image by Andy Martin
Typeset & Designed by Matthew Tye & Gower Associates
Table of Contents
Introduction ................................................................................... 16
Who is this book for? .............................................................................................................17
Code Examples .......................................................................................................................18
Chapter 1: A Performance Troubleshooting Methodology .......... 19
Defining a Troubleshooting Methodology ........................................................................20
Wait Statistics: the Basis for Troubleshooting ..................................................................23
Virtual File Statistics ..............................................................................................................28
Performance Counters ..........................................................................................................30
Plan Cache Usage ...................................................................................................................38
Summary .................................................................................................................................40
Chapter 2: Disk I/O Configuration ................................................42
Disk Configuration: Basic Considerations .........................................................................43
Disk size vs. disk throughput ...........................................................................................44
Random versus sequential I/O ..........................................................................................45
Choosing the Right RAID Level ..........................................................................................46
A brief overview of RAID configurations .......................................................................48
Disk size and throughput considerations .......................................................................54
Workload considerations ...................................................................................................57
Direct Attached Storage vs. Storage Area Networks .........................................................61
Direct Attached Storage .....................................................................................................61
Storage Area Networks ......................................................................................................62
Diagnosing Disk I/O Issues ..................................................................................................65
Common Disk I/O Problems ................................................................................................65
Sizing for capacity instead of I/O performance ............................................................66
Incorrect workload isolation ............................................................................................67
Incorrect partition alignment ..........................................................................................68
Incorrect bandwidth using SAN configurations ...........................................................70
Summary ..................................................................................................................................71
Chapter 3: High CPU Utilization .................................................. 73
Investigating CPU Pressure ..................................................................................................74
Performance Monitor ........................................................................................................74
SQL Trace ............................................................................................................................76
Dynamic Management Views ...........................................................................................77
Common Causes of High CPU Usage .................................................................................85
Missing indexes ..................................................................................................................86
Outdated statistics .............................................................................................................88
Non-SARGable predicates .................................................................................................89
Implicit conversions ............................................................................................................93
Parameter sniffing ..............................................................................................................95
Ad hoc non-parameterized queries ................................................................................103
Inappropriate parallelism ................................................................................................108
TokenAndPermUserStore ................................................................................................117
Windows Server and BIOS power saving options .......................................................120
Summary ................................................................................................................................122
Additional Resources ...........................................................................................................122
Chapter 4: Memory Management ...............................................126
The Self-Tuning Database Engine .....................................................................................127
How SQL Server Allocates Memory ..................................................................................127
32-bit Virtual Address Space limitations.........................................................................131
Using 64-bit SQL Server .................................................................................................140
Memory configuration options with 64-bit SQL Server ............................................143
Diagnosing Memory Pressure ............................................................................................149
Memory-related counters ................................................................................................150
Memory-related DMVs .....................................................................................................154
Common Memory-Related Problems ................................................................................155
The SQL Server memory leak myth ...............................................................................155
Paging problems ................................................................................................................155
OS instability due to Lock Pages in Memory
plus unlimited max server memory ...............................................................................156
App Domain is marked for unload due to memory pressure ....................................157
Error 701 and FAILED_VIRTUAL_RESERVE ..............................................................158
Over-provisioned virtual machines ................................................................................159
Memory settings for multiple instances .......................................................................160
Summary ................................................................................................................................161
Chapter 5: Missing Indexes .........................................................163
Index Selection and Design ................................................................................................164
Index key column order ...................................................................................................165
Use of included columns..................................................................................................169
Index width ........................................................................................................................172
Identifying Missing Indexes ...............................................................................................172
Workload analysis with the Database Engine Tuning Advisor .................................173
Missing index feature ......................................................................................................180
Missing indexes on foreign keys .....................................................................................188
Identifying Unused Indexes ................................................................................................189
Identifying Duplicate Indexes ............................................................................................191
Summary ................................................................................................................................192
Chapter 6: Blocking .....................................................................193
Locks and Concurrency (a Brief Review) ..........................................................................194
Lock modes ........................................................................................................................194
Lock types ...........................................................................................................................196
Lock escalation ..................................................................................................................197
Concurrency and the transaction isolation levels .......................................................198
Latches and latch contention .........................................................................................203
Monitoring Blocking ...........................................................................................................204
Using sysprocesses (SQL Server 2000 and later) ........................................................205
Cumulative wait statistics with DBCC SQLPERF (waitstats) ...................................208
Dynamic Management Views ........................................................................................208
Performance Monitor .......................................................................................................216
Automated Detection and Notification of Blocking ......................................................217
The Sp_blocker_pss80 process .......................................................................................218
SQL Trace ...........................................................................................................................218
Event notifications ............................................................................................................223
Extended Events ...............................................................................................................226
Resolving Blocking ...............................................................................................................228
Bad database design .........................................................................................................229
Inappropriate isolation level ...........................................................................................229
Poorly written queries .....................................................................................................230
Missing indexes .................................................................................................................231
Poor application design ....................................................................................................231
Outdated hardware ...........................................................................................................232
Hints, Trace Flags and Other Last Resorts .......................................................................233
Locking hints .....................................................................................................................233
Summary ................................................................................................................................236
Chapter 7: Handling Deadlocks ..................................................237
The Lock Monitor ................................................................................................................238
Capturing Deadlock Graphs ...............................................................................................238
Trace Flag 1204 ..................................................................................................................239
Trace Flag 1222 ...................................................................................................................241
SQL Profiler XML Deadlock Graph event .....................................................................241
Service Broker event notifications ..................................................................................243
WMI Provider for server events ......................................................................................245
Extended Events ...............................................................................................................246
Reading Deadlock Graphs ..................................................................................................249
Interpreting Trace Flag 1204 deadlock graphs ............................................................249
Interpreting Trace Flag 1222 deadlock graphs ..............................................................254
Interpreting XML deadlock graphs ................................................................................257
Common types of deadlock and how to eliminate them ..............................................259
Bookmark lookup deadlock ............................................................................................260
Range scans caused by SERIALIZABLE isolation .......................................................262
Cascading constraint deadlocks ....................................................................................264
Intra-query parallelism deadlocks ..................................................................................265
Accessing objects in different orders ............................................................................266
Handling Deadlocks to Prevent Errors .............................................................................267
T-SQL TRY…CATCH blocks ..........................................................................................268
Handling ADO.NET SqlExceptions in .NET code .....................................................270
Controlling Deadlock Behavior with Deadlock Priority ................................................271
Summary ................................................................................................................................272
Chapter 8: Large or Full Transaction Log ...................................273
How the Transaction Log Works .......................................................................................274
How SQL Server writes to the transaction log ...........................................................276
Understanding log truncation ........................................................................................277
Sizing and growing the log .............................................................................................278
Diagnosing a Runaway Transaction Log .........................................................................280
Excessive logging: index maintenance operations ......................................................281
Lack of log space reuse .....................................................................................................285
Other possible causes of log growth .............................................................................295
Handling a "Transaction Log Full" Error .........................................................................299
Mismanagement or What Not To Do ...............................................................................302
Detach database, delete log file ......................................................................................302
Forcing log file truncation ...............................................................................................303
Scheduled shrinking of the transaction log .................................................................304
Proper Log Management ....................................................................................................305
Summary ................................................................................................................................307
Chapter 9: Truncated Tables, Dropped Objects and Other
Accidents Waiting to Happen .....................................................308
Example Case: The Missing Sales Order Data ................................................................309
Recovering Lost Data ...........................................................................................................310
Recovering from backup ..................................................................................................310
Recovering without a backup .........................................................................................320
Finding the Culprit ..............................................................................................................324
Prevention is Better than Cure ..........................................................................................326
Plan for recovery from all data losses .............................................................................327
Implement a change control process .............................................................................329
Implement an appropriate security model ...................................................................330
Access control measures ...................................................................................................331
Summary ................................................................................................................................338
Appendix: What to Do When All Else Fails .................................340
Microsoft Customer Support Services .............................................................................340
Online Resources .................................................................................................................342
Articles ................................................................................................................................343
Blogs ....................................................................................................................................343
Forums ...............................................................................................................................344
Hiring a Consultant .............................................................................................................346
Foreword from Paul Randal
For most people, troubleshooting performance, corruption, or other problems with SQL
Server is a necessity rather than a preference. Unless you're an advanced DBA or consultant
with many years of troubleshooting experience, the first question you often ask yourself is:
where do I start? There are quite a few books on SQL Server internals, SQL Server trouble-
shooting, or both – but they're all organized from the perspective of SQL Server architecture
or internals.
With the burgeoning number of non-DBAs dealing with SQL Server, what these people really
need is a book that's organized by problem area – and that's where this book differs from the
other troubleshooting books out there. If you're an accidental or involuntary DBA, you've
been asked to look after a SQL Server, and you're struggling with how to figure out a problem,
this book is for you. On the other hand, if you're a seasoned DBA or consultant and you want
a fresh way of thinking through SQL Server problem solving, this book will be immensely
useful to you as well.
The book covers many of what I consider to be the most common problems that people
encounter when dealing with SQL Server. For each problem it gives a brief overview of the
underlying technical details before getting into the "How do I work towards solving the
problem" details that are the most important for a time-constrained IT professional to know.
There's also a liberal sprinkling of links to background articles and blog posts for the more
intrepid readers to explore.
Jonathan, Ted, and Gail are all highly-respected SQL Server MVPs, prolific bloggers (going into
great depth with their blog posts), and spend large amounts of time responding to inquisitive
and in-need people from around the world on a variety of Internet forums and Twitter. Their
writing is clear and precise, which is of paramount importance when imparting trouble-
shooting details to people who may be unfamiliar with the topics being described.
I have personally learned from all three of these talented SQL Server experts, and I encourage
you to do so too.
Paul S. Randal, CEO – SQLskills.com
Redmond, WA – October 2011
x
