Chapter 15:​ Monitoring Indexes

This chapter covers the use of Performance Monitor (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 section on Query Store mentions the exciting new feature of automating indexes, as promised on the book’s blurb, but it only provides a link to Azure documentation and says the feature is unavailable in SQL Server 2022.  

If the book purports to support Azure SQL, then it could include a section on automatic tuning, which includes automatically creating and dropping indexes.

Chapter 16:​ 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!

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 server. 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 makes 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 walkthrough of using the DTA via the command line is provided. 

Chapter 17:​ 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 2022, much of the content is applicable to earlier editions.  

More could have been included specifically about SQL Server 2022 and Azure functionality. The blurb for this book (on Amazon) says “This fourth edition includes new guidance and features related to columnstore indexes, improved and consolidated content on Query Store, deeper content around Intelligent Query Processing”. Suggesting this is targeted at these SQL Server 2022 improvements. However, the book:

• omits the significant columnstore index improvement in SQL Server 2022 - to have ordered data, this helps create more granular segments, giving better segment elimination and thus faster performance (the book actually says the data can’t be ordered!)

• has around one page about Query Store (in 2 sections)

• has just one small paragraph about using a single aspect of Intelligent Query Processing

• has only a few short sentences (3?) on Azure  

In my review of the previous edition of this book I noted various errors (e.g. limit of 8 indexes for in-memory tables) that had not been corrected in that new edition, and many of these are still present in this 4th edition. 

Most of this book’s content excellent but trying to pass this edition off as a significant update for SQL Server 2022 and Azure, which it is certainly not, is underhand. I would estimate around 97% of this book’s content is the same as the previous edition, and some of the newer SQL Server 2022 and Azure index features are omitted. 

I feel cheated by this new edition, it is a VERY superficial/lazy upgrade, largely to include SQL Server 2022 and Azure in the title - to cash-in on the recent release of SQL Server 2022. The foundation information about indexes typically doesn’t change much between SQL Server editions, if you own the 2019 version of this book, then you do not need this edition.

For more recommendation of SQL Server Books, see Pick Of The Shelf - SQL Server in  Programmer's Bookshelf.

To keep up with our coverage of books for programmers, follow @bookwatchiprog on Twitter or subscribe to I Programmer's Books RSS feed for each day's new addition to Book Watch and for new reviews.