Author: Christian Bolton, Rob Farley et al.
Publisher: Wrox
Pages: 576
ISBN: 978-1118177655
Audience: Troubleshooting DBAs and Developers
Rating: 4.3
Reviewer: Ian Stirk

I expected this book to give me an insight into what to do when I get various SQL Server problems. Identify the problem? How do I solve it? And can it give me an understanding as to why it occurred? Did the book fulfil these requirements?

There are two distinct parts to this book, internals and troubleshooting. The first section is more theoretical, and the second is more practical. The aim of the internals section is to provide a deeper background understanding for the subsequent troubleshooting section.

The topics covered are both diverse in terms of subject matter (e.g. hardware, concurrency, extended events) and in terms of the expected level of experience (e.g. PowerShell is introductory, and latches are advanced). Some of the subjects seem a bit off topic (Virtualization), and even esoteric (Spinlocks).

In some respects I didn’t feel there was enough cohesion between the various chapter subjects. More should have been done by the editors to unite the chapters and make the flow easier. That said, what is present in each chapter is both well written and interesting, if not entirely focused on internals and troubleshooting.

There is a large degree of overlap with the 2008 version of this book (maybe 60+%), some chapters have been dropped (e.g. Management Data Warehouse), and others added (e.g. PowerShell). In many ways I think the 2008 edition of the book was more focused on the topic of troubleshooting.

Below is a chapter-by-chapter exploration of the topics covered, I’ve given each chapter its own rating.

Chapter 1 Architecture

This chapter provides a great overview of the main SQL Server components and how they interact, all illustrated with useful diagrams. It uses the example of running a SELECT query to introduce you to the various SQL Server components i.e. Buffer pool (plan and data caches), Relation engine (optimizer etc), and Storage engine (transactions, access, etc). This is followed by an example of an UPDATE query to introduce you to the transaction log, checkpoint process, lazy writer, dirty pages, and recovery interval. SQL Server’s execution model is discussed with reference to SQLOS (a thin interface between SQL Server and the Windows OS), and some of its DMVs are introduced. This is an interesting background chapter for any SQL Server book. (5/5)

Chapter 2 Demystifying Hardware

Often one of the easiest ways to improve performance is to upgrade the hardware. However, it is still possible for inefficient code to negate the advantages that improved hardware may bring. Hardware is a topic many database professionals profess ignorance of, so the information given in this chapter should help enable you to make more informed hardware decisions. Topics covered include: workload type, history, vendor selection, hyperthreading, licences, and hardware metric tools. The author makes the interesting point that the decline in vendor competition could be harmful for the industry. I found the chapter a bit dull in parts, due to the subject matter rather than the writing style. The subject matter is up-to-date, and provides information that should prove useful in supporting any proposed hardware improvements. (4/5)

Chapter 3 Understanding memory

Overall I found this chapter to be a bit removed from everyday SQL Server work and quite theoretical in parts. That said, there are some useful snippets of SQL included. The importance of memory is illustrated by how speed is measured for disks access (milliseconds) compared with memory access (nanoseconds), were possible you want as much data in memory as possible, reading disks is slow. The author makes the point that when problems occur, memory should be investigated before CPU or I/O, since memory problems can result in problems in these other areas. The structure of memory nodes, clerks and caches are discussed. This is followed by optimization in terms of min and max server memory, lock pages in memory, and optimize for ad-hoc workloads. (4/5)

Chapter 4 Storage systems

This chapter is also largely theory, containing details of SQL Server and Windows I/O subsystems. Again there’s a useful diagram that shows how the pieces link together, these include CPU cache, buffer pool, HBA, storage array and disks. As expected the various types of RAID are discussed – but not the contention that typically exists between what storage engineers prefer i.e. RAID 5 everywhere is most cost effective, compared with what SQL Server would prefer i.e. RAID 10 or 1 is better for performance. Capacity planning is introduced together with a discussion of growth, thin provisioning and instant file initialization.

The second part of the chapter discusses measuring performance. The general benchmark being average disk access should be less than 20ms. An overview of disk access at the physical level is given. Solid State Disks, which provide an order of magnitude improvement in disk access times, are briefly discussed. The chapter ends with a walkthrough of the popular disk performance tools SQLIO/SQLIOSim.

Overall I found the chapter interesting for background information, but not particularly innovative. (4/5)