|Troubleshooting SQL Server - A Guide for the Accidental DBA|
Page 1 of 4
Author: Jonathan Kehayias & Ted Krueger
This Book aims to provide solutions to the most common problems encountered by the inexperienced (accidental) DBA. How does it fare?
The premise behind the book’s title is that many DBA roles are filled accidentally, often by the developer that has the most database knowledge. This typically results in a steep and stressful learning curve, as mistakes are made. This book, which can be downloaded as a free pdf file from
aims to help the accidental DBA overcome the most common problems.
The book was published in September 2011, and covers SQL Server 2005, 2008, and 2008 R2. It occasionally drops back to SQL Server 2000 and jumps forward to Denali (the name of the beta version of SQL Server 2012). Although SQL Server 2012 is not covered explicitly, I think 95% of the book is still applicable to SQL Server 2012.
Considering how much it covers, this is a short book, it could have easily been twice as big. Luckily it concentrates on what you need to know, and provides useful links for further reading. In many ways its limited size makes it more difficult to criticize negatively. The first seven chapters (of nine) read like a guide to performance tuning and troubleshooting. So it comes as a surprise when the last two chapters switch to admin related issues. Some useful code is supplied, while it is adequate for the introduction of a topic, if you want to delve deeper you will need to rely on the chapter text to extend the code or look elsewhere. One small gripe is the lack of diagrams and outputs.
The book is largely divided into chapters based on resource, for example High CPU Utilization. This makes it easier for a DBA, perhaps under pressure, to find the relevant section and its solutions. However, this does sometimes lead to a disorganized book, for example chapter 3 talks about the trace utility but it is only discussed in detail in chapter 5. Additionally, organizing the chapters by resource, means many disparate areas of SQL Server are covered for a given problem type, and this can seem messy. Some sections have inadequate cross referencing. Tools are typically introduced where they are first used, it might have been better to have a separate chapter specifically on tool usage and reference this in subsequent chapters.
The book quite rightly emphasizes the need to corroborate bits of problem-related information. It is very easy to take a single piece of information and start investigating the wrong area. Although the book provides guidelines and recommendations, it correctly asserts that all changes should be tested on your own system, since the suggestions often depend on the nature of the workload, and the current version of the technology.
Chapter 1 A performance Troubleshooting Methodology
This chapter provides an overview of the book, and discusses the methodology used. There is an emphasis on corroborating disparate sources of information to identify the underlying problem, rather than working with a single, often misleading, piece of information. The tools covered include wait statistics, performance monitor (PerfMon), and plan cache. It’s odd that SQL Trace isn’t introduced here.
There’s a good overview of the more common wait types. PerfMon is introduced together with PAL (Performance Analysis of Logs) with its very useful threshold values and included templates. The basic idea behind the use of a Dynamic Management View (DMV) snapshot is discussed (take a snapshot of some DMV counters, do some work, take another snapshot of the same DMV counters, get the delta between the 2 DMV snapshots – this can then be ascribed to the work undertaken). The fallacy of the Page Life Expectancy (PLE) value of 300 seconds is discussed. Finally the plan cache is examined, with reference to the most reads, writes, and physical reads. Getting the query plan from the DMVs is mentioned but no code given. Some useful (but limited) code is supplied and overall this is a very good introduction.
|Last Updated ( Thursday, 23 May 2013 )|