Book Review: SQL Server Query Tuning & Optimization

SQL Server 2022 Release To Manufacturing is just around the corner. The current release as of this writing is SQL Server 2022 Release Candidate 0. This SQL Server edition is the most “Azure-enabled release yet.” What that means, according to Microsoft, is that it “provides true resilience by connecting SQL server to Azure through seamless disaster recovery to Azure SQL Managed Instance.” There are tons of resources that you can find online to learn more about the features of the latest iteration of SQL Server.

If you are like me and still prefer the physical book as your primary reading material, Benjamin Nevarez’s book from Packt Publishing is a great book to add to your bookshelf (it’s available in digital format too).

I have reviewed the last two editions of Benjamin’s other book on “High-Performance SQL Server” (here & here).

And just like how I recommended those to friends, I highly recommend SQL Server Query Tuning and Optimization: Optimize SQL Server 2022 Queries and Applications to anyone interested in making their SQL Server performant; more so if you are a SQL Database Administrator or SQL Developer.

TL;DR – I highly recommend this book.

Marlon Ribunal

I like how the table of contents is arranged in a two-column format which allows easier scanning when you are looking for a specific feature or section in the book. I know this is a minor feature but it’s kinda a big deal to me 🙂

As the book title indicates, you don’t expect to see all the new features introduced in SQL Server 2022 here (e.g, Ledger, Azure Synapse Link for SQL, etc). If you are looking for a great resource for SQL Server query tuning and optimization, this is your book. And if you are not planning to implement SQL Server 2022 in the near future, need not worry because it also covers older versions.

It is just proper that the book starts with Introduction to Query Tuning and Optimization in Chapter 1, which covers how the query optimizer works and how it produces efficient execution plans. The first chapter takes you from the fundamental of query tuning to the basics of an execution plan.

I like the cadence of this book. It’s kinda fast-paced when it comes to the progression of discussion. By Chapter 2, you are lunging at Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), SQL Trace, Extended Events, and Data Collector. This chapter covers the foundation of query and performance tuning with the use of DMVs and DMFs. In this early part of the book, you’re already shown how to find expensive queries, how to investigate bottlenecks such as blocking and waits, and how to work with SQL trace and extended events to investigate further.

Chapters 3 to 5 cover the Query Optimizer, Execution Engine, and Working with Indexes. I feel like Chapter 3 – Query Optimizer – should have been the second chapter but that’s no big deal. Chapter 4 covers the Execution Engine, which can help you find what is happening under the hood while queries are being executed. Although this isn’t the first time I heard about Halloween Protection, I like the fact that Benjamin dedicated a whole section about this Update operation behavior. Some of the books out there don’t even mention Halloween Protection. Chapter 5 covers Indexes. Working with Indexes is a big deal and this book covers the topic quite well considering that extensive discussion about Indexes merits its own book.

Chapter 6 covers Statistics which includes a section about the “new” Cardinality Estimator introduced in SQL Server 2014 and a little background on estimation models. Cardinality Estimation is a big aspect of an execution plan and I like that this chapter recommends ways of detecting and fixing cardinality estimation errors. This is the chapter that I might often refer to in my day-to-day job as a Database Administrator.

Chapter 7 covers In-Memory OLTP. I don’t work with this feature and didn’t delve into it, to be honest. It’s in this book if you need it.

Now onto the biggest features and enhancements of existing features in SQL Server 2022, Chapters 8 to 10 cover Understanding Plan Caching, Query Store, and Intelligent Query Processing. The discussion of the execution plan in the previous chapters focuses on how the query optimization process produces them. Chapter 8 – Understanding Plan Caching – focuses on what happens to those plans. This is basically an extension of Chapter 1. If you are planning to read this in phases, you could probably sew them together as a contiguous chapter. Chapter 8 is capped with a discussion of a SQL Server 2022 feature, the Parameter-sensitive plan. The Query Store has become a de facto method of gathering performance-related information. In fact, Quey Store is on by default in SQL Server 2022. Chapter 9 covers the enhancements to Query Store. Chapter 10 covers an overview of Intelligent Query Processing (set of features) which was intended to improve the workload performance without the need for application changes. SQL Server 2022 further enhanced some of the features within the Intelligent Query Processing family.

Chapter 11 covers an Introduction to Datawarehouse and the common performance problems and how to solve them. Again, this is a big topic and probably requires another book for its extensive discussion. This chapter is here if and when you need it.

I understand why the author decided to place the chapter on Query Hints at the end of the book, Chapter 12. Do you need Hints? Probably not. Do you always “not need” Hints? Probably not, either. There are instances where Hints are “necessary” depending on your needs. This chapter covers some of those scenarios.