Page 1 of 2
Author: Chris Shaw, Grant Fritchey et al.
Audience: DBAs and developers wanting to dive deeper
Reviewer: Ian Stirk
It is practically impossible to write a book that covers everything in SQL Server 2012. However, if you ask a group of SQL Server experts to write on topics that interest them, you’ll end up with a book full of enthusiasm and detail – this is such a book…
I expected the book to be mostly innovative, but it is probably 60% new material and 40% rehash of material available elsewhere.
The chapters fall into three categories, either innovative, an overview of a given area, or a very detailed investigation of a small topic. Often the book is not specifically about 2012 features, but practices that can be applied to many versions of SQL Server. Many of the authors suggest ways of working, without mandating it, however they do provide persuasive arguments to back up their approaches.
Owing to the disparate nature of the contents, I’ll discuss each chapter in turn. I’ve given each chapter a rating since it is really like having several types of books within a book. All the chapters are worth reading, but I suspect some of the ratings may reflect my personal interests/bias.
In many ways, this book is a bit like Manning Press’s SQL Server MVP Deep Dive books (see side panel and Kay Ewbank's review), which is no bad thing since SQL Server has so many diverse areas of interest.
Chapter 1: Be Your Developer’s Best Friend. This chapter discusses the conflict of interest that occurs between the developer and the DBA. Developers want freedom to perform their work, while DBAs want them to work within certain constraints. The author provides a solution by creating a pattern for various utility stored procedures, which use ‘execute as’ to provide the ability to perform (audited) functionality, e.g. create a snapshot, change MAXDOP etc. In essence, it provides a set of controlled routines to give the developers greater flexibility and eases the DBA’s concerns and workload. Some very useful code samples included. (5/5)
Chapter 2: Getting It Right: Designing the Database for Performance. This is really a primer for the author’s own Apress book. The author emphasizes getting the base correct (e.g. normalization, constraints, indexing, datatypes), providing hands-on examples of why you need to work in this way. The chapter is very practical, and the author knows all too well the answer to most questions is ‘it depends’. (5/5)
Chapter 3: Hidden Performance Gotchas. This is an unusual chapter in the context of the rest of the book, rather than provide a practice, it concentrates on three of the more unusual performance problems that might occur on your servers. The three not so obvious performance problems relate to predicates, residuals, and spills. The author explains how the problems are seen, and fixed. Be aware the chapter itself is very good, but I’m not sure it really fits into the spirit of the book. (4/5)
Chapter 4: Dynamic Management Views. DMVs are my own specialty. If you care for your SQL Server, want to know what’s happening internally, or quickly/easily fix database problems, you need to know about DMVs. Here you are presented with code for some of the more common performance tuning queries including: what queries are currently running, longest duration queries, poor performing queries, and identifying missing indexes. The author provides plenty of practical code to help fix your database problems. (5/5)
Chapter 5: From SQL Trace to Extended Events. This chapter provides an overview of three related diagnostic tools: SQL Trace, event notifications, and extended events. Relatively little importance is given to SQL Trace since it is being deprecated. Event notification is explained and some useful example code for getting mailed when a deadlock occurs is provided. Extended events are the future, and the chapter details their infrastructure (Events, predicates, actions etc). It’s another chapter that probably belongs to another book, but what is included is very good. (5/5)
Chapter 6: The Utility Database. The idea behind this is to create a utility database to help answer questions such as ‘is the performance today worse than last month?’ Or ‘how has capacity changed in the last 6 months’ etc. Many of us probably create a utility-type database/queries afresh when we start a new project, what we should do is aim for re-use, and this chapter provides code to do just that. Checklists are provided for Daily tasks (backups, disk space, error logs, failed jobs etc), Long term tasks (audits, config changes, patches etc). Example database structures are provided. There is an error in one of the scripts (stored procedures performance page 151), since the code relates to the individual SQL statement level, rather than the stored procedure level. This should have been caught by technical reviewers. (4/5)
Chapter 7: Indexing Outside the Bubble. The premise of this chapter is the interplay between factors that need to consider for implementing an index i.e. adding an index might degrade performance elsewhere. Various tools are discussed, including missing indexes DMVs, workload through the Database Tuning Advisor (DTA), and examining the plan cache for missing index indicator. One of the new features of 2012 is the use of the plan cache as input to the DTA, this should have been mentioned since it is an excellent feature. (4/5)
Chapter 8: Release Management. This chapter contains a few typos that should have been removed by the editing team. The chapter provides an overview of the process the author uses to release changes into production. The approach has evolved after painful mistakes have been made in the past, so now it’s fairly solid e.g. test the rollback script. There is an emphasis on the need for different environments (dev, test etc), a documented release processes, and getting the correct players involved in the process. Continuous improvement/tuning of the process is discussed and encouraged. If you don’t have such a process already, this is a great starting point. (4/5)
Chapter 9: Compliance and Auditing. Recent legislature e.g. Sarbanes-Oxley, has shown the need for checks and controls. This chapter outlines what is available within SQL Server 2012 for server auditing (of backup/restores, login failure, various changes), and database auditing (of SELECT, UPDATE, INSERT, DELETE on a table). Time is spent explaining how to query the audit file, and how to create alerts for audit events. Again much of the chapter is straightforward and not particularly innovative. (4/5)