| Query Store for SQL Server 2019 (Apress) |
Page 1 of 3 Author: Tracy Boggiano & Grant Fritchey This book aims to use Query Store to improve your SQL Server queries, how does it fare? Query Store is a tool that collects performance related information, allowing you to identify the cause of problems, and provides some solutions (including automated solutions). Query Store provides aggregated information about the queries running on your SQL Server databases. The book is aimed at DBAs and developers responsible for query performance. It’s a relatively short book, having around 210 pages, spread over 10 chapters. Although the book primarily focuses on SQL Server 2019, much of it is applicable to earlier versions of SQL Server that have Query Store (i.e. 2016 and higher). Below is a chapter-by-chapter exploration of the topics covered. 
Chapter 1: What Is Query Store? The book opens with a look at the usefulness of Query Store, in particular relating to a baseline – this allows subsequent values to be compared to determine if there is a problem. The Overall Resource Consumption Report is used for this. Query Store is the front end for various underlying catalog views, and these are outlined. (e.g. sys.query_store_runtime_stats). Next, some reports are discussed that can identify queries that may be performing more slowly (e.g. Regressed Queries Report), again only a taster for later. The chapter next looks at other tools that were used before Query Store, some of which are still useful. Tools examined include: SQL Server profiler, Extended Events (XEs), and Dynamic Management Views (DMVs). This is followed with a look at some methods used to produce consistent query performance before Query Store, these include: plan guides, updating stats, and recompiling queries. After all the previous background information, now we move onto the crux of this book, Query Store. This VERY brief section lists the information that is collected, and then there’s a sentence on automatic plan correction, and query-level wait stats. This 2-page section is hardly worthwhile – especially since much on this topic had already been given in passing. The section on Waits says you can’t tell the waits for an individual query – but you can if you run the query and look at the DMV sys.dm_exec_session_wait_stats. But, it is much easier with Query Store. This chapter provided a useful overview of how query troubleshooting was done before Query Store, and briefly how it is done with Query Store. The importance of having a baseline in determining a problem is highlighted. The chapter is generally easy to read, having useful screenshots, code examples and inter-chapter links. Additionally, it is sometimes a bit dry (listing too many methods/properties), too wordy, and unnecessarily repeats. These traits apply to the whole of the book Chapter 2: Overview and Architecture of the Query Store The aim of this chapter is to give you an understanding of the components of Query Store, in the belief that this will aid your understanding of how it works, and highlight its low-impact presence. Query Store is a database-level setting. Only Data Manipulation Language (DML) statements are collected. Query Store collects information on:
There’s a brief look at query processing via the algebrizer (parser, object binding) and query optimizer, resulting in the execution plan. Information from the execution plan step is processed asynchronously by the Data Collection process before storing details temporarily in memory, which is periodically flushed and stored in the Query Store. This process changes slightly when there’s a forced plan (i.e. a previous and hopefully better performing execution plan). Information about the runtime and wait stats is automatically collected, and is periodically written to Query Store. This information can be stored by interval, allowing two periods to be compared. Next, the chapters look at the underlying tables and their columns for each of the objects the Query Store captures data for. Helpfully, these tables can also be used to identify queries that have failed. At times, the information in this chapter was unnecessarily repeated (e.g. wait stats are not present in SQL Server 2016 Query Store). And also has unnecessary information (e.g. we’ll talk about sys.database_query_stats_options later). I think if you come from a DMV background, you will find many similarities with these Query Store catalog views, enabling you to take advantage of some of your existing knowledge. In fact, I suspect many of us have developed our own Query Store-like processing previously. Overall, a useful background chapter on the structures that underlie Query Store, together with an outline on how the data is stored. This should aid understanding in subsequent chapters. Chapter 3: Configuring Query Store SQL Server often has features with default settings, whilst these may be useful, often with experience these values need to change. The chapter opens with a simple command to enable Query Store. Next, various configuration options are explained, these include:
In each case, a useful overview of the option is provided, together with its underlying SQL. Many of the options can be changed via the Query Store GUI. We now come to perhaps the most useful part of the book, what best practices to apply to Query Store. Configuration options best practices include:
Other best practices include:
There’s a useful discussion on enabling Automatic Plan Regression Correction (APRC), a very useful feature to solve immediate problems, providing you remember it may not be a suitable long term fix. The chapter ends with a useful section on Query Store maintenance, covering monitoring space usage, clearing Query Store, removing plans and queries, and identifying failed forced plans. I really enjoyed this chapter, it had lots of useful information, especially the best practices. That said, sometimes things are repeated and explained unnecessarily, for example, see this paragraph: There is one catalog view that holds the settings for Query Store: sys.database_query_store_options. For more on the catalog views, see Chapter 5. You can use the sys.database_query_store_options catalog view to view the settings for Query Store in the database. To view the configuration settings for Query Store, use the query in Listing 3-14. SELECT * FROM sys.database_query_store_options The first sentence is good. The second sentence has already been made earlier in this chapter. The third sentence repeats the first sentence. The fourth sentence states the obvious, do we really need to be told how to select data, in what should be an intermediate-level book?! Similarly, there is SQL to enable a feature (APRC), followed by near-identify SQL to disable that feature, this is unnecessary! There’s a spelling mistake that made me smile: “… you are capturing the write amount of data…” |
||||
| Last Updated ( Wednesday, 14 July 2021 ) |