Author: Jason Strate and Grant Fritchey
Publisher: Apress
Pages: 403
ISBN: 978-1484211199
Audience: Performance DBAs/Developers
Rating: 4.5
Reviewer: Ian Stirk 

Chapter 9 Index Maintenance

As data changes, indexes can degrade, which can degrade query performance. This chapter is about ensuring your indexes are optimal. The first section is concerned with physical and logical fragmentation. SQL to optimise the index is given. As throughout the book, there are some great examples to illustrate the points made. 

Similar to index fragmentation maintenance, statistics maintenance is also discussed. Typically an index’s statistics are automatically updated when 20% of its underlying data changes. While this is fine in most cases, there are times when the statistics may need to be updated manually. You can imagine an index with 100 million rows, if 1 million rows are changed each day, it might take more than a month for the index’s statistics to get updated automatically – potentially given sub-optimal performance until they are updated. The use of maintenance plans and a custom update script is explained.

Chapter 10 Indexing Tools

This chapter discusses in-depth the use of the missing index DMOs and Database Engine Tuning Advisor (DTA) to find what indexes your systems need. In both cases, the recommendations need to be consolidated and considered, rather than being blindly implemented. 

Of the two, the missing index DMOs are simpler to use, you can easily identify the more obvious indexes that are missing from your system. However, they do have more limitations (e.g. column order is not specified, only considers non-clustered indexes). DTA is more comprehensive, providing you supply it with a workfile that contains all the relevant queries you want to optimize. The DTA can recommend clustered, non-clustered, partitioning, new, and dropping of indexes. It was good to see a mention of the use of the plan cache as a new input to the DTA – a feature that is often neglected. 

Chapter 11 Index Strategies

This chapter expands on the earlier chapters and provides more index usage information, including the use of heaps, clustered indexes, primary keys, foreign keys, GUIDs, columnstore indexes, included columns, filtered columns, compression, and indexed views. Throughout the chapter the importance of testing is emphasised, and the use of SET STATISTICS IO used to record and compare metrics. 

There is a very nice point that we are all using heaps more than we think, since temporary tables are heaps by default! This expands further to explain that heaps are often not so harmful, unless you do further filtering or sorting. There is a minor error saying filtered indexes were introduced in SQL Server 2005, but they were introduced in SQL Server 2008. 

Chapter 12 Query Strategies

This chapter looks at aspects of SQL queries that can prevent otherwise useful indexes from being used. Although short in content the approach is excellent i.e. taking a scientific approach, based on testing to reduce the number of reads. 

The query aspects covered are: 

• the LIKE statement

• Concatenation

• Computed columns

• Scalar functions (e.g. RTRIM, DatePart)

• Data conversion 

I loved the examples in this chapter, I had wanted it to be longer, but I couldn’t think what else they could have included (related to indexes specifically).

Chapter 13 Monitoring Indexes

This chapter covers the use of perfMon counters, DMOs, wait stats, SQL trace, and Extended Events (XE). In each case, details of what to collect, their meaning and context, are explained. Sample SQL is provided to get you started in collecting data periodically. Some correlations between the perfMon counters and wait stats are discussed.

Chapter 14 Index Analysis

This chapter uses as its input the output from the monitoring chapter. It is good to see expected values for various perfMon counters, this should prove useful in identifying problem conditions. The various common wait stats are explained. For more information on interpreting the meaning of waits and perfMon counters, see Tom Davidson’s seminal paper SQL Server 2005 Waits and Queues – still relevant today! http://msdn.microsoft.com/en-us/library/cc966413.aspx. 

I had expected the Performance Analysis of Logs (PAL) tool to be used on the perfMon counters , to automatically highlight the major problems on the box. This is a great tool and should be included in your performance toolkit. 

SQL is given to identify heaps, duplicate indexes and overlapping indexes. The SQL code works if you follow the approach given, but for a more generic (and useful?) version you can replace the text: IndexingMethod.dbo.index_usage_stats_history with: sys.dm_db_index_usage_stats. There’s some useful SQL for identifying un-indexed foreign keys (but not for tables without primary keys). 

The authors make an excellent point about using DTA. DBAs might want to prove their mettle by examining the data and manually creating the indexes. But often using the DTA may be a better option. A detailed walkthrough of using the DTA via the command line is provided. 

Chapter 15 Indexing Methodology

This chapter is relatively small. It discusses the importance of communication with regards to impact analysis and status reports. Deployment and rollback scripts are briefly discussed, together with the importance of source control and having repeatable scripts. The last section concerns script execution. 

The improvement cycle never stops of course, since data and usages change. At the end of this process (monitor, analyze, implement) it’s time to start the cycle again. 

Conclusion

This book probably covers everything you would want to know about indexes, it has depth and range, is full of relevant practical examples, and has a methodological approach to performance tuning using indexes. Although written for SQL Server 2014, much is applicable to 2012 and 2008. 

Ignoring the one completely new chapter “Indexing Memory-Optimized Tables”, the book’s text is around 95% the same as the previous edition of the book. Chapters 4, 5, and 6 were the previous edition’s Chapter 4. Chapters 13, 14 and 15 were the previous edition’s Chapter 10.

Unfortunately, updating the book from SQL Server 2012 to SQL Server 2014 introduced errors. For example: some cross-chapter references are incorrect, some references to 2012 should have been updated to 2014, the text “sys.selective_xml:index_paths” and “sys.xml:indexes” is meaningless. Greater care should have been taken during the editing process.

Ignoring the book’s editing problems, it is an excellent resource and will prove useful to anyone looking to improve the performance of their SQL Server databases.