Table Of Content

High 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 troubleshooting, 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 troubleshooting 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