A few weeks back, the folks at Apress Publishing sent me a copy of the book Expert Performance Indexing in SQL Server for me to review. Since I know both the authors and since I’m familiar with the spectacular work they do, I was happy to do so. Doesn’t mean I won’t do an honest review though. I can’t be bought! (Well I can, but considerably more money should be involved 😉 ) Apparently the book is already in second edition. I haven’t read the first edition, so I can’t compare those.
About the authors
Jason Strate (blog | @StrateSQL) is a SQL Server MVP with tons of experience with databases and data warehouses (he’s did a legendary one-month run on SSIS a few years back). He’s a well known speaker and an active member of the SQL Server community. Grant Fritchey (blog | @GFritchey) aka the Scary DBA. A longtime SQL Server MVP and a vocal member of the SQL Server community. He’s an active participant at the forums of SQLServerCentral.com and speaks at various events (where our paths have crossed a few times).
If anybody could write a book about SQL Server indexing, it’s those two. You can tell from the book they have years and years of technical experience, and they try to pass it on to the reader. Very successfully, I must add. Grant has also a book on SQL Server Query Performance Tuning, so this book is a logical step. And Jason wrote the first edition of course 🙂
General
I’m pleased to say this is an excellent book. I definitely learned a lot from it. The book goes into real technical detail – which is sometimes hard to follow if you’re not a hardcore DBA but a BI developer instead, like me – and it comes packed with a lot of scripts that you can immediately start using in your environment. The title starts with “Expert” and there’s a good reason for that. The introduction states this book is also for “novices in indexing”, but if this is really your very first book on indexing, you might feel a wee bit overwhelmed after a few chapters. If you do keep up with the pace, you’ll find that this book is an excellent resource for everything there’s to know about indexing in SQL Server. Although a bit more info on columnstore indexes would have been nice.
The book is very well written. There are almost no typos or mistakes in the book (kudos for the reviewers and the editors). There were a few issues with hyperlinks to chapters in the PDF ebook, but nothing too serious. When needed, there are plenty of screenshots to back-up the technical explanations. The T-SQL scripts use the Microsoft sample databases (AdventureWorks and Contoso), so it’s easy to try them out on your machine as well. You can read the book front-to-back like I did, or you can pick any chapter of your interest and just start reading. Only at the end there’s one chapter – Index Analysis – that is dependent on the previous chapter, Monitoring Indexes.
Chapter by Chapter
Let’s go over the various chapters:
- Chapter 1 is a general introduction to all the various kinds of indexes in SQL Server. It uses a great analogy with books inside a library. This chapter should be mandatory reading for everyone working with SQL Server.
- Chapter 2 talks about index storage fundamentals. The kid gloves are off, this chapter dives into the technical detail. It explains how data is stored inside pages and it demonstrates how you can use DBCC commands or dynamic managements views/functions to take a peek into those pages.
- Chapter 3 explains how you can query the metadata of indexes (for example physical and operational statistics) and it shows how SQL Server uses statistics for the indexes and how you can examine them. This chapter is important, as it lays the foundation for scripts used in later chapters.
- Chapters 4, 5 and 6 are about XML, spatial and full-text indexes respectively. I skipped those chapters. The reason for this is because I’m pretty sure I’m never going to use them. If someday I would, I know I can read up on it using those chapters. But I couldn’t be bothered reading them right now. The same goes for chapter 7, which is about indexing in-memory OLTP tables. Not exactly a feature I’m going to use soon in my data warehouses, so I decided to skip that chapter as well.
- Chapter 8 is a great chapter. It takes a few common myths about indexes and debunks them. An example is “Clustered indexes store records in physical order”. If you thought they were, it’s time you read this chapter 🙂 I guess this chapter is the result of a few great slidedecks and presentations the authors did over the years. This chapter should also be mandatory reading. Give it to everyone starting with SQL Server. The authors also present a few indexing best practices. They are careful enough to mention that best practices are not carved in stone and that they should be taken with a grain of salt.
- Index maintenance is the topic of chapter 9, which is mainly about index fragmentation and statistics maintenance. You’ll learn how to find issues and how to resolve them, either with maintenance plans or with custom T-SQL scripts. A great resource for DBAs.
- Chapter 10 talks about indexing tools, such as the missing index DMOs. The bigger part of the chapter however is used in an attempt to salvage some of the reputation of the Database Tuning Advisor. Lots of screenshots here and even an explanation on how to use the command line interface.
- Chapter 11 is a very interesting one. It goes over the four big types of indexes (heaps, non-clustered, clustered and columnstore) and presents different scenarios and use cases where you like to implement them. Row and page compression are discussed as well. A very good chapter and one I will reread soon because there’s a lot of useful information there. There’s only one issue in this chapter: one clustered index pattern talks about “surrogate keys”. The definition is given as “A surrogate key is a column in the data that can uniquely identify one row from all the other.”, which is incorrect in my opinion. A surrogate key is a meaningless key, separate from the source system and typically used in data warehouses. A better term would have been natural key,candidate key or business key. Now it can lead to a bit of confusion with readers familiar with the data warehouse surrogate key definition. I’ve reached out to the authors and they acknowledge the use of an incorrect definition, so this will probably get fixed if there’s a third edition 🙂
- Chapter 12 is another great one. It explains that it still matters how you write your queries. Because even with world-class indexes in place, nothing can shield SQL Server from stupidly written queries. This chapters teaches you how to make sure your queries still use the correct indexes.
- Chapter 13 is probably the most useful for DBAs. It provides you with a full framework for monitoring indexes. It comes with scripts that read performance counters, dynamic management objects and event traces and store all of that information in a monitoring database for further analysis. Lots of useful scripts here, if you want to know what is going on with your indexes.
- When you have all the monitoring information on your indexes, chapter 14 tells you how you can analyze that information to take action. This chapter reminded me a bit of sp_blitzindex.
- The last chapter is a very short one and the only one that is not technical. It talks about indexing methodology. I know it’s important, but the book wouldn’t be worse if it was left out. For the interested readers.
Conclusion
This book is an excellent book about indexing in SQL Server. It covers about everything there is to know in great technical detail. Which is one of the “downsides” of the book: it’s not exactly for beginners. But hey, the title says “Expert Performance Indexing”, not “Indexing for dummies”, so you could think the technical detail is one of the books biggest strengths. The book is a bit more geared towards DBAs, but I guess that’s fair since they deal with them the most in their daily activities. There are a lot of scripts in the book which you can immediately use in your environment to monitor, analyze or learn more about the indexes in your databases. My favorite chapters are chapter 8 (myths and best practices), chapter 11 (indexing scenarios) and chapter 12 (how to not mess up your queries).
My only criticism: there isn’t a chapter about columnstore indexes. I feel there can be a lot more said about those indexes. And if XML indexes get a separate chapter, so should they. Maybe in a third edition?
I recommend this book for everyone who wants to get a better understanding in SQL Server and who wants to make take their expertise in indexing to the next level.