Author: Grant Fritchey
Publisher: Apress, 2012
Pages: 499
ISBN: 978-1430242031
Audience: Database developers and administrators
Rating: 4.5
Reviewed by: Kay Ewbank

The title of this book is a pretty good summary of the contents, especially with its subtitle of ‘Troubleshoot and optimize query performance in SQL Server 2012’.

The book has a number of problems with  typesetting, resulting in spaces incorrectly inserted, but it is worth persevering with for the technical content. To give you an idea of the type of scrambling you’re likely to encounter, the first page  says you’ll learn about:

Identifyingp roblematicS QLq ueries

and

Analyzinga ndr esolvingf ragmentation

which at least has irony on its side! I found that most of the spacing errors seem to be in titles and bullet points, so the main body of the text remains readable.

Having dealt with the layout problems, what about the actual material itself? SQL queries are what make your app fly or run like treacle, and Fritchley looks at all the things you can do to collect performance figures so you can analyze how things are running and what you can do to put things right when you discover problems.

The book kicks off with a chapter on the basic concepts of performance tuning for a SQL Server database system, including a flow chart of the steps you need to go through. Chapter two covers system performance analysis, mainly using Performance Monitor, but also looking at resolving hardware bottlenecks and monitoring virtual machines.

By chapter three Fritchley has moved on to analysing queries using Extended Events and the other tools in Management Studio. SQL Server 2012 has made using Extended Events a lot easier with the addition of a GUI wizard interface, and Fritchley shows how to use this before moving on to ways to identify and deal with costly queries. He then covers execution plans and how the query optimizer uses them to improve the way a query runs.

Index Analysis is the topic of the next chapter, and Fritchley discusses index overheads and goes through a list of recommendations for index design such as using narrow indexes and clustered v nonclustered indexes before moving on the advanced indexing techniques such as index joins and filtered indexes.

As the biggest performance improvements can be seen by putting the right indexes in place, the next chapter looks at the Database Engine Tuning Advisor. The name is a bit misleading as this is the tool SQL Server provides to identify the optimal set of indexes without needing an expert knowledge of the database schema and workload. Fritchley gives examples of how to use the Advisor along with a discussion of its limitations.

The next group of chapters cover analysing different aspects of database and query use, with chapters on analysing lookups, statistics, fragmentation, the execution plan cache, query recompilation, query design, blocking queries, deadlock and cursor cost. These chapters really form the heart of the book, and are well written. The chapter on lookup analysis considers ways to avoid excessive lookups to navigate from non-clustered index rows to the corresponding data row in the clustered index.  Statistics analysis shows how to make use of the information SQL keeps on the index key and how to evaluate the info on query execution.

Fragmentation of indexes occurs when the underlying B-tree structure of the index becomes disordered due to data changes. In the chapter on fragmentation analysis, Fritchley looks at the overheads this can cause, how to work out how bad things are, and ways to resolve it. The chapter on query recompilation looks at the effect of converting the variable parts of queries into parameters, meaning that execution plans can be reused when queries are resubmitted. The chapter looks at why SQL Server sometimes recompiles statements in compiled queries, how to work out why it’s happened, and ways to avoid it.

The chapters on blocking analysis and deadlock and cursor cost analysis were particularly interesting. Blocking occurs as the number of users increases, caused as SQL Server’s lock manager prevents access to in-used data to ensure consistency.  Minimizing blocking is important if you’re going to have a lot of users, and Fritchley covers not only the various causes of blocking and how to work out what’s causing the blocks, but recommendations to minimize blocking. Deadlock happens when two or more transactions both want to change the same data. SQL Server has rules to work out which one should win, and the chapter on deadlock analysis looks at error handling to catch deadlocks and techniques to resolve it. Cursors are notoriously expensive for database overheads; processing your data one row at a time is never a good idea. Fritchley explains the advantages of default result sets and looks at how to pick the least bad cursors when necessary.

The final chapters are more high level at least in concept. Database performance testing is essentially a guide to using SQL Server 2012’s new Distributed Replay tool to work out how adding an index or a query has altered the performance of your overall database. Database workload optimization gets a chapter is essentially how to use all the information gathered elsewhere in the book to improve the running of your database. Finally, the SQL Server Optimization Checklist does what is says; goes through everything you should do as the basics of making sure you’ve got SQL set up in the best general way to work well.

Overall, I found this a good book. It’s well written and the information is reliable. The problem with the text layout on headings and lists is irritating but you can work around it.