Chapter 8:​ Auto Plan Correction and Wait Statistics 

Automatically identifying queries that are now running more slowly (i.e. regressed), and changing these to use a previously faster query plan, could prove to be a very useful feature.

The chapter opens with a look at how you identify regressed queries, previously we used the Regressed Queries Report, here we use the DMV sys.dm_db_tuning_recommendations to identify query plans that would benefit from being forced (the DMV also provides the SQL do to this). To enable automatic plan correction, you need to enable the setting via the Azure blade (screen), or for SSMS use the ALTER DATABASE command. Example code is given so you can follow along.

Next, the chapter switches to looking at Wait Statistics stored in Query Store. When your SQL isn’t running on the CPU, it is waiting, SQL Server stores these waits as wait stats, and analysing these can give an insight into why your queries are running sub-optimally. A general overview of the wait categories is given (e.g. locks), together with some useful code for identifying the wait types associated with a given query. The Wait Statistics Report is also briefly re-examined.

This chapter provides a useful insight into how to enable automatic plan correction.

Query Store is an intermediate-level topic, but often this book explains things from a more basic level, in light of this, the authors should have explained the meaning of “GO 30” etc in their code (it means run the above batch of SQL statements 30 times).

The authors correctly point out that the recommendations in the DMV sys.dm_db_tuning_recommendations will be lost after each server restart etc – I do wonder if Microsoft should have created an equivalent Query Store catalog view for this (especially since many of the Query Store catalog views seem to be persisted DMVs, with an added time interval).

There’s some useful JSON code to unravel the contents of the planForceDetails column of the DMV sys.dm_db_tuning_recommendations. 

The authors make a good point that the book’s Azure screen images may look different from the current Azure portal, things change regularly in Azure. The authors should explain what a ‘blade’ is.

Why are Automatic Plan Correction and Query Store Wait Statistics together in the same chapter? They are only very tenuously linked. They should each have their own separate chapter, actually automatic plan correction is a natural component and/or extension of the previous chapter.

Chapter 9:​ Troubleshooting Issues with Query Store 

Query Store uses resources, and thus can be a source of problems itself. This short chapter opens with a look at how you can investigate the Query Store specific wait types, they begin with ‘qds’ (e.g. QDS_TASK_START), to determine what problems Query Store might have. 

SQL code is provided to inspect the QDS wait types via the DMV sys.dm_os_wait_stats. There’s a useful link to Paul Randal’s Wait Type documentation, specifically highlighting the wait types that are innocuous (and can safely be ignored in your investigations).

Next, we look at the other means of troubleshooting Query Store, Extended Events (XEs). XEs allow you to trace what is happening on the instance (they have made SQL Trace/Profiler redundant). A brief walkthrough is provided on how to select the Query Store events.

Although the chapter shows how to use wait stats and XEs to identify problems with Query Store, no real-life example was provided.

Overall, this was another useful chapter, on how you would troubleshoot problems with Query Store itself.

Perhaps the Query Store specific waits should have been shown in the context of other waits, to give some perspective of their importance. While there is a useful reference to Paul Randal’s wait types documentation, it would have been better introduced in the previous chapter on Wait Statistics. 

The line starting “The first four columns are for…” should read “The first four rows are for …”

Chapter 10:​ Community Tools 

This chapter looks at some free community tools that can be useful when working with Query Store. 

dbatools is an easy to use extensive PowerShell library that abstracts the user from lots of detail. The chapter looks at the three Query Store commands in the dbatools library, namely: 

• Get-DbaDbQueryStoreOption (get Query Store properties)

• Set-DbaDbQueryStoreOption (set Query Store properties) 

• Copy-DbaQueryStoreConfig  (copy Query Store properties) 

For each command, an example usage is provided. It should be noted that Copy-DbaQueryStoreConfig has been replaced by Copy-DbaDbQueryStoreOption.

Next, the chapter looks at sp_BlitzQueryStore, this essentially queries the Query Store catalog views, and outputs relevant metrics (e.g. slowest queries in the last 7 days). Some query analysis is also done (e.g. top 3 waits). It is possible to specify various parameters to sp_BlitzQueryStore, to target your investigation (e.g. to a certain store procedure).

This chapter provides a useful look at two free Query Store tools. Some readers may be unfamiliar with using PowerShell, so perhaps a brief demonstration of using the Integrated Scripting Environment (ISE) or VS Code, together with some screenshots, would have been useful (else provide a reference link etc). 

I would encourage every DBA or SQL dev to get familiar with the wonderful dbatools library (it’s useful for restores, upgrades, High Availability, migrations etc).

Conclusion 

This book aims to use Query Store to improve your SQL Server queries, and largely succeeds. It is generally easy to read, having useful screenshots, code examples and inter-chapter links. On the other hand, it is sometimes a bit dry, long-winded, and has unnecessary repeats. Less is sometimes more, the book would have been better with around 25% fewer pages (remove repetition and unnecessarily simple sentences).

Although the book focuses primarily on SQL Server 2019, much of it is applicable to earlier versions of SQL Server that have the Query Store (2016 and higher).

The book contains a great deal of useful information, however some of it is readily available in the Microsoft documentation, or can be obtained with a little experimentation with the product. The authors should have included more useful SQL code for the reader (often just need to extend what is provided to include joins to other Query Store catalog views). The book might also gain by showing more output results.

I imagine Query Store to be an intermediate-level topic, but the authors sometimes treat the topic as too introductory. Generally, if you’re familiar with DMVs and catalog views, I would not expect to be shown the SQL to select all the data from a catalog view (i.e. SELECT * FROM sys.database_query_store_options)

Sometimes the use of English is substandard (e.g. missing words, wrong words), these problems should have been caught by the editor. 

Overall, a useful look at what Query Store does, and how it can be used in your everyday problem investigations. With corrections, it could make an excellent 2nd edition.

• For more recommendations of books on SQL Server see Pick Of The Shelf - SQL Server in our Programmer's Bookshelf section.

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.