Chapter 6 XML Indexes

Chapter 7 Spatial Indexing

Chapter 9 Full-Text Indexing

These are atypical indexes, relating to unstructured data. I think they’re included for completeness rather than for detail (after all they contain the word ‘index’). That said, in each case a satisfactory overview is given with examples to get you started in your investigations. 

XML indexes allow you to query XML by storing path, tags, and value details in an index. Spatial data relates to the geometry and geography data types. Full-text indexing relates to indexing text-based content/documents. 

I think it makes more sense to group the ‘peripheral’ index chapters together, I’m not sure why the chapter on Indexing Memory-Optimized tables separates them. 

Perhaps the big change with XML indexes in SQL Server 2022 is the use of compression, this can be a significant space saving and often good for performance (more data per read) – and surely that’s the main purpose of an index. However, this was not mentioned in this chapter. Again, this makes me wonder how much of an ‘update’ this book really is over its previous edition.

Chapter 8:​ Indexing Memory-Optimized Tables

In-Memory tables are stored in memory, unlike their on-disk counterparts. They are one of the main reasons for upgrading SQL Server, providing significant performance improvements. The chapter describes what In-Memory tables are, and how to create them. 

The chapter next discusses the two indexing options associated with In-Memory tables: hash indexes are optimized for key lookups, and range indexes are useful for searching for a range of data. Example code is given to illustrate the usage of both types of index. 

In later editions of SQL Server (from 2014) the many restrictions associated with using In-Memory processing have been progressively removed. The chapter says there is a maximum limit of 8 indexes for in-memory tables, this is incorrect, starting from SQL Server 2017 there is no longer any limit. Similarly, it states in-memory indexes can’t be changed after they’re been created, but the ALTER TABLE statement can be used with in-memory tables to change these indexes. This chapter needed better updating. 

It should be noted that the name “Range Index” was what Microsoft initially gave to this index in the CTP release of SQL Server 2014, however, the terminology was later changed in its production release, and it is now called “Non-Clustered Index”. The book has 2 editions to correct this error.

Chapter 10:​ Indexing 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 11:​ Index Maintenance 

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

Like 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.

There is a link to Microsoft documentation about automatic tuning in Azure. This, and perhaps 2 other links to Azure in other chapters, are the only significant mentions of Azure in the whole book…

Chapter 12:​ 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 Query Store as an input to the DTA – a feature that’s sure to be invaluable. 

Chapter 13:​ Indexing 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. 

Chapter 14:​ 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).