Author: Ritesh Shah and Bihag Thaker
Publisher: Packt Publishing
Pages: 478
ISBN: 978-1849685740
Audience: DBAs and SQL Developers
Rating: 2.5 or 4.0 (depending on your tolerance of bad grammar)
Reviewer: Ian Stirk

Chapter 14 Implementing Physical Database Structure

Disk I/O is often the primary cause of poor performance in database systems. If you can get data from the disks into memory more quickly, you’ll improve the performance of your database. By spreading the tables/indexes over different files/filegroups, on different physical drives, you’ll have more disk controllers that can work in parallel to get the data more quickly. The log file and tempdb need special consideration.

The chapter starts with a discussion of physical file placement and how it can result in parallel I/O operations. This is followed by scripts and discussions relating to moving large tables, and non-clustered indexes to separate physical disks. Tempdb is singled out since it can impact all the other databases on the server. Interestingly RAID types were not discussed in any detail.

Chapter 15 Advanced Query Tuning Hints and Plan Guides

Hints are a misnomer! The name implies they have optional influence, they do not, by using hints you are forcing the optimizer down a certain path. Sometimes the optimizer gets things seriously wrong, so you can use hints to force it to take the specified path. Be very careful when using hints, since their behaviour may change with future SQL Server releases.

The chapter starts with an overview of how a query runs i.e. syntax is checked, break down the query, and optimization. The hints discussed include NOLOCK, FORCESEEK, and INDEX. Plan guide usage is also discussed.

Generally, this chapter provides an adequate introduction to the use of hints, with some good evidence backed scripts. There is much more to know.

Chapter 16 Dealing with locking, blocking and deadlocking

Data consistency is achieved with the use of transactions. Transactions protect data by taking out locks, which can affect the performance of other queries. Thus is the interplay between data consistency and concurrency. Sometimes, if a block cannot be resolved with time, a deadlock results, here SQL Server will rollback one of the queries.

The chapter starts with a discussion of transactions, data consistency, concurrency, and locking. The lock object hierarchy is explained (i.e. database, file, object, table, extent, page and key). Scripts are provided to identify long running transactions, since they are often the cause of blocking and thus complaints from users. While the script provided is useful, much more could have been done, such as identifying the individual SQL Statements involved – I have actually extended the script and added it to my toolkit. Scripts and discussions are provided to identify blocking and deadlocking (using both the profiler and trace flag 1204).

I’m surprised the chapter doesn’t mention the system_health extended event. This is often the quickest and easiest method for retrieving deadlock information, with no setup cost. Also the DEADLOCK PRIORITY setting was not discussed. The dangers of lowering the isolation level by using NOLOCK was not explained.

Overall, a useful chapter, but be careful about following the advice about using NOLOCK without first looking at its potential side effects.

Chapter 17 Configuring SQL Server for Optimization

One of the good things about SQL Server, compared with other databases, is that it generally runs ok without too much setup. That said, there are some configurable settings that might improve the performance of your systems.

The chapter starts with a brief discussion of the configuration tool sp_configure. This is followed by a discussion of MAXDOP, which I found a little troubling. It advocates using all CPUs (MAXDOP 0) which is generally not recommended, and not changing MAXDOP on OLTP databases (but the default is MAXDOP 0!). The real answer of course is to experiment with the MAXDOP setting, on a test system that uses a production-like workload.

This is followed by a discussion of configuring memory for 32-bit and 64-bit environments, then configuring Optimize for Ad hoc Workloads. Both of which are adequately explained.

Again there is some bad grammar.

Chapter 18 Policy-Based Management

Policy Based Management (PBM) is a tool for easier maintenance and ensuring standards across database systems. It is possible to check that backups are not on the same drive as the database itself, MAXDOP has the required value, tables follow a given naming convention, and much more. I wasn’t sure if this chapter related directly to performance tuning, but PBM can certainly prevent or report on bad practices.

The chapter discusses how PBM helps enforce and apply policies across database systems. Policies, conditions and facets are all explained. An example walkthrough is provided that ensures stored procedure names do not start with sp_. This chapter is a useful introduction to PBM, perhaps more could have been said about the conditions PBM can check.

Chapter 19 Resource Management with Resource Governor

Often problems occur when a long running report uses a lot of resources, slowing down other users’ queries. With the Resource Governor, it is possible to allocate resources (CPU, memory) to a given set of users, so query performance is managed better.

The chapter starts with a discussion of the problems that can occur with unconstrained resources. The Resource Governor can allocate resources based on source, logins or users. The chapter provide a walkthrough using both the GUI and SQL. This chapter provides a good introduction to resource management.

Conclusion

This is a wide-ranging book, with sufficient instruction to get most SQL developers/DBAs started on investigating performance problems. It’s a very practical book, typically providing detailed step-by-step walkthroughs for each recipe/topic, and makes good use of screenshots.

There’s some nice incidental information provided along with the recipes, and plenty of pointers on where to find further information. Because the topics are largely distinct, there is limited interlinking of recipes/topics.

Although the book purports to relate to SQL Server 2012, much of it is equally applicable to SQL Server 2005 and 2008.

Unfortunately, the book contains several areas of substandard English grammar. If you can tolerate this, the book can be a good read. Personally I think the book’s editors should have been fired!