Author: Jason Strate 
Publisher: Apress
Pages: 615
ISBN: 978-1484254639
Print: 1484254635
Kindle: B0825PBF8R
Audience: Performance DBAs/Developers

Chapter 8 Index Myths and Best Practices

This chapter was a nice bit of fun, short, sharp and quite revealing. Various myths were discussed (typically illustrated with practical examples), including:  

• Databases don’t need indexes

• Primary keys are always clustered

• Online index operations don’t block

• Any column in a multicolumn index can be used as a filter

• Clustered indexes store rows in physical order

• Indexes always output in the same order

• All tables should have heap/clustered index  

This was followed by some index best practices. Maybe the best advice given is that you should always test things yourself! The best practices included:  

• Use clustered indexes on Primary Key by default

• Balance index counts (i.e. reads < updates)

• Set FillFactor for indexes individually

• Index Foreign Keys

• Continuously review your index environment  

Chapter 9 Index Maintenance

As data changes, index structure 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 an 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, JSON, 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 could have included (related to indexes specifically).

Chapter 13 Monitoring Indexes

This chapter covers the use of perfMon counters, DMOs, wait stats, SQL trace, Extended Events (XE), and Query Store. 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. The new section on Query Store mentions the exciting new feature of automating indexes, as promised on the book’s back cover, but it only provides a link to Azure documentation and says the feature is unavailable in SQL Server 2019.  

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, overlapping indexes and uncompressed 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 author 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 2019, much of the content is applicable to earlier editions. 

Although there have been 3 new editions of SQL Server since the 2014 release of this book, I would estimate around 95% of this book’s content is the same as the previous edition. 

The previous edition suffered from sloppy editing during the process of updating the book from SQL Server 2012 to SQL Server 2014, many of these have been corrected, but a few new ones introduced. 

I did wonder how much coverage Azure SQL Database would get. Promisingly, the back cover states “You will get a look at the Index Advisor now available in Azure SQL Database…”, however, the entire book contains only 2 sentences about Azure. I felt a bit cheated. But please don’t let these minor problems distract you from what is essentially an excellent resource and will prove useful to anyone looking to improve the performance of their SQL Server databases. Highly recommended.

For more recommendations of SQL Server books see Pick Of The Shelf - SQL Server in our Programmer's Bookshelf section.