Chapter 21:​ Intelligent Query Processing

Performance tuning can be difficult, with this in mind, Microsoft has created some features that can automatically correct troublesome areas. The optimizer uses various attributes (e.g. row counts, stats) to produce a query plan, however these plans can sometimes be suboptimal, later versions of SQL Server can get information at runtime and feedback into creating a better plan. 

The chapter opens with a look at various aspect of Adaptive Query Processing, including: 

• Adaptive joins (type of join used by execution plan determined at runtime, often giving significantly better performance)

• Query Processing Feedback (runs of query can give suboptimal results [often based on bad stats and/or rowcounts], later runs can feedback and alter execution plan. Can do for memory grants, cardinality estimates, and Degree of Parallelism. 

• Optimized plan forcing (Can override optimizer to select a better execution plan) 

Next, the chapter looks at Approximate Query Processing, where approximate aggregation counts can be obtained quickly to help produce good-enough execution plans. The 3 counts discussed are:  

• APPROX_​COUNT_​DISTINCT

• APPROX_​PERCENTILE_​CONT 

• APPROX_​PERCENTILE_​DISC 

There’s a look at Table Variable Deferred Compilation, where the actual table row counts are used to create a better execution plan – in previous versions the row count was estimated and often gave an inefficient plan when the table contained many rows. The chapter ends with a look at Scalar User-Defined Function in-lining, here the UDF is in-lined and converted to a scaler subquery that can then be optimized (previously they could not be optimized).

This chapter provided a very useful overview of a miscellany of features grouped as Intelligent Query Processing. This seems to be a fruitful area for SQL Server to develop, allowing many potential performance problems to be automatically corrected.

Chapter 22:​ Automated Tuning in Azure and SQL Server

Automatically tuning databases potentially offers big gains for relatively little effort. Since SQL Server 2017 (and Azure SQL Database) Microsoft has begun looking at automated tuning to improve the performance of your queries. 

The chapter looks at:  

• Automatic plan correction (based on plans/stats in Query Store)

• Azure SQL Database automatic index management (a useful walkthrough is given)  

This chapter provides some useful example code to examine automated tuning in SQL Server (much of the underlying mechanism is hidden). Tuning recommendations exposed via the DMV sys.dm_db_tuning_recommendations are explained. 

Overall, this is a very interesting chapter, it’s certainly an ever-expanding area to keep an eye on for the future.  

Chapter 23:​ A Query Tuning Methodology

This chapter summarises performance best practices in single place. It provides various checklists (together with explanations) including:  

• Database design 

• Use Constraints (e.g. PK/unique. helps optimizer produce better plan)

• Index design best practices (e.g. include, filter, clustered, missing indexes etc)

• Put tables into in-memory storage

• Use columnstore indexes 

• Configuration settings 

• Memory configuration options (max server memory)

• Cost threshold for parallelism (35s?)

• Database compression (more CPU but typically faster IO) 

• Database admin 

• Keep the statistics up-to-date

• Maintain a minimum amount of index defragmentation 

• Query design 

• Avoid non-sargable search conditions

• Avoid optimizer hints

• Ensure there are no implicit data type conversions

• Minimize logging overhead

• Adopt best practices for database transactions   

In many ways this chapter summarises the whole book, and certainly justifies the cost of the book itself. The recommendations offered are sensible, but, as repeated often in this book, please ensure you test them on your own system to know they do improve performance. 

Conclusion 

This book covers a wide range of performance topics and techniques, with the central aim of improving the performance of your SQL workload. It is generally easy to read, with practical step-by-step walkthroughs, helpful links between chapters and to websites, and good use of screenshots. Typically, code is provided to back up the assertions made.  

This book covers most code-based performance topics. The book should take your level of expertise from level 3 to level 8 (based on a 1 to 10 scale). 

The author claims the book is a rewrite, but I suggest it is at least 80% similar to the previous edition – but with some (relatively) small updates for SQL Server 2022. This is to be expected since the fundamentals don’t really change. There are two new chapter (Graph Databases, and Intelligent Query Processing), and several other chapters have been merged or split. Additionally, some chapters have been dropped. Some of these dropped chapters are quite interesting (e.g. Database Workload Optimization - a useful case exercise, applying many features learned in the book), it must have been difficult for the author to decide which chapters to omit. Please see my review of the previous version of this book for further details. 

Although written for SQL Server 2022, much is applicable to SQL Server 2017 and earlier versions. If you want a solid, wide ranging, SQL code-based optimization book, I can highly recommend this book.

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.