Built-In Query Intelligence in SQL Server 2022

Early in the planning for SQL Server 2022, Pedro Lopes and I came up with the term built-in query intelligence. We did this to represent the innovation that is new in SQL Server 2022 to gain and maintain consistent performance with no code changes. In addition, these changes allow you to gain new insights into query performance and tune queries faster and in new ways with enhancements to Query Store. These innovations are “built in” to the engine itself and use “data” to act intelligently. That data is based on statistics or execution information from data sources like Query Store.

Figure 4-1 shows a visualization of all the new IQP features and notes which ones use Query Store.

A screenshot titled intelligent query processing I Q P next gen. It has 3 columns titled upgrade to S Q L server 2022, dbcompat 140 plus, and dbcompat 160 with features underneath each.

This chapter will cover capabilities you can get without dbcompat 160 (the left two columns). Chapter 5 will dive into IQP capabilities if you enable dbcompat 160 including Parameter-Sensitive Plan (PSP) optimization, cardinality estimation (CE) model feedback, and degree of parallelism (DOP) feedback.

Note in the bottom-right corner a box describing that “IQP Gen1” is enabled with dbcompat 140. This represents the IQP capabilities we introduced in SQL Server 2017. “IQP Gen1+2” represents the IQP capabilities we introduced in SQL Server 2019 plus SQL Server 2017. It is important to know this because you might be upgrading from SQL Server 2016 or earlier to SQL Server 2022 and not realize all the previous IQP capabilities you could be using. You can always keep up with all IQP features at https://aka.ms/iqp.

Let’s start by looking at enhancements for Query Store. There are enough new enhancements to SQL Server 2022 I call it the new Query Store.

The New Query Store

Query Store in my opinion is simply one of the coolest features we have ever put in the SQL Server engine. As far back as the mid-2000s, the famous Conor Cunningham approached several of us in customer support about a new way to track query performance. Up to this point, we were all “polling” Dynamic Management Views to persist query performance execution data. Conor had the idea baking into the query processor itself telemetry about query performance execution. This telemetry would be persisted in the user database in the form of system tables. He called it Query Disk Store (QDS), which is now known today as Query Store.

It took several iterations, but Conor’s dream of this innovation was realized in SQL Server 2016. It was also a key piece for success of Azure SQL Database as we turned it on by default for all new databases.

I still find today many customers I talk to do not use Query Store. Some don’t know exactly what it is, some have heard of it but haven’t tried to turn it on, and others have used it but had issues.

By SQL Server 2019, we completed new enhancements to fix some performance issues, added wait stats information (added in SQL Server 2017), and introduced extra controls to help with query capture and better storage management of the data. All of this has set us up to move forward with a new Query Store for everyone.

Query Store Support for Secondary Replicas

While Query Store has been a great innovation for SQL Server, a frequently requested enhancement is to support collecting performance execution statistics for queries run against secondary read replicas for Always On Availability Groups.

Note

You can see how popular this request has been on our https://aka.ms/sqlfeedback site at https://feedback.azure.com/d365community/idea/eade91be-4e25-ec11-b6e6-000d3a4f0da0.

With SQL Server 2022, you can now configure an Always On Availability Group to support capturing statistics if queries are directed at a read replica.

I think the best way to dive deeper into this new capability is to answer some “how” questions.

How to Do You Configure It?

Once Query Store is enabled on the primary replica of the Always On Availability Group, you can run the following T-SQL in the context of the primary database:

ALTER DATABASE CURRENT FOR SECONDARY SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE);

Note

At the time of the writing of the book before General Availability, trace flag 12606 was required to enable this feature. Please consult the documentation at General Availability to see if this trace flag is still required. When you enable this feature, Query Store information is captured for all secondary replicas in the availability group. There is no method to control this per secondary replica.

Once you execute this statement, any query executed against any secondary replica will be captured in Query Store.

But if the query is run against a secondary replica, how can Query Store capture information about the query if the replica database is read-only? Let’s see how it works.

How Does It Work?

When you enable Query Store and performance information is captured for a query, it is first recorded in a set of memory structures. Asynchronously, Query Store information is flushed to disk (to a collection of system tables in the user database). Runtime statistics are flushed to disk based on a configuration value called DATA_FLUSH_INTERVAL_SECONDS. You can read more about how Query Store collects information at https://docs.microsoft.com/sql/relational-databases/performance/how-query-store-collects-data.

If you have enabled Query Store capture for secondary replicas, Query Store information is captured in memory structures on the secondary replica, but not flushed to the disk for the secondary replica database (because the database is read-only). Instead, the information is sent back to the primary replica where it is flushed to disk. Always On Availability Group replicas have a channel to communicate with each other, and we simply use that channel to send data back to the primary. The SQL Server 2022 engine has been enhanced to understand this new type of information sent back to the primary and then adds the data to Query Store on the primary replica as seen in Figure 4-2.

A flowchart of query store and stats. The flow starts with queries and ends at query store queries for S Q L.

Let’s explore more the flow of how this works. Let’s assume you have an Always On Availability Group set up and configured for Query Store on secondaries. When a user issues a query against the secondary read replica, the engine will collect Query Store stats in memory. These stats are not persisted on the secondary because the database is read-only.

• Background tasks will queue these stats in memory in the secondary replica engine. At some point (an internal implementation detail), these stats will be sent on the same communication channel used for availability group log changes (the dbm endpoint). We try to make sure that these performance stats have a lower priority than log changes so as not to disrupt replica of log changes.

Note

As I was writing this chapter, our team was investigating whether we could enable the system stored procedure sp_query_store_flush_db to force the sending of stats from the secondary to the primary.

• On the primary replica, background tasks will receive these Query Store stats and persist them to disk, tracking the replica details along with the stats. Since these stats are stored in the database and are logged changes, they are also sent to secondary replicas like all logged changes.

• Now, any user can run queries or reports for Query Store stats against the primary replica or secondary replicas.

In order to distinguish the difference between queries on the primary and secondary replicas, a new column has been added to Query Store view sys.query_store_runtime_stats called replica_group_id. This maps to a new view called sys.query_store_replicas, which contains all the replica names, roles, and their ID value.

There are a few things I think you should know about the expected behavior of this capability:

• If you enable this feature, you may need to increase the value of MAX_STORAGE_SIZE_MB, especially if you have multiple secondary replicas where you are executing queries.

• The results do not show up on the primary replica immediately. Since we use the same channel for replicating changes to replicas, we don’t want to negatively impact transaction changes. Therefore, we send back data to the primary in a delayed fashion. Today there is no configuration value to adjust this time. In my experience, it can take several minutes for query performance information to appear on the primary after executing a query on a secondary replica.

• Even with this delay, if your application is sensitive to changes to be replicated in your Always On Availability Group, you should test this option carefully to ensure the use of the standard communication channel for replica changes is not impacted. Throughput can also depend on how often you execute queries on secondary replicas and how many secondary replicas are in the availability group.

• The only Query Store views that capture the replica_group_id are sys.query_store_runtime_stats and sys.query_store_wait_stats. If you want to find out information in the plan store (i.e., Sys.query_store_query) specific to a replica, you will need to join to one of these views (using the plan_id as the join column). In other words, if you only query sys.query_store_plan, you will not be able to tell whether plans were captured from the primary or secondary replicas.

• At the time of the writing of this chapter, we were still working out what features that use Query Store may or may not be supported for read replicas. This includes Query Store hints, optimized plan forcing, DOP feedback, and CE feedback. You can keep track of all the latest updates with this feature in the documentation at https://docs.microsoft.com/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?#query-store-for-secondary-replicas.

How Do You Use It?

Since we are just enhancing the existing Query Store functionality, you can now use Query Store like you do today to analyze query performance. And because all the data is in the primary replica, any backup of the primary replica database includes all the information from all replicas.

IQP Nextgen Defaults

When you upgrade to SQL Server 2022, several IQP features are simply “turned on” without any dbcompat change required. This means that you could be using a dbcompat level from versions of SQL Server prior to when IQP “was born,” like SQL Server 2016 (dbcompat 130), and take advantage of new IQP features. This includes approximate percentile and optimized plan forcing. Let’s explain how each of these features works and how you can take advantage of them.

IQP Nextgen with dbcompat 140+

For example, if you upgraded from SQL Server 2016 and kept the dbcompat level at 130, you would able to take advantage of these features (provided you meet the criteria for each).

But let’s say you upgrade from SQL Server 2017 or 2019 and have dbcompat set at 140 or 150. What additional features can you use to improve performance? First, remember that any IQP features that shipped in 2017 and 2019 are available to you depending on dbcompat 140 or 150.

In SQL Server 2022, if you are using dbcompat 140 or greater, then you can take advantage of enhancements for memory grant feedback.

In Chapter 2 of SQL Server 2019 Revealed, I provided a detailed background and explanation of memory grant feedback. Let me summarize here the key points I made in that chapter about memory grant feedback.

There are certain operators in a query plan that require their own memory allocation such as hash joins and sorts. When a query is compiled, SQL Server will decide how much memory is needed for these types of operators; this is called a memory grant. I love this detailed blog post by one of our longtime developers Jay Choe on memory grants: https://docs.microsoft.com/en-us/archive/blogs/sqlqueryprocessing/understanding-sql-server-memory-grant. Memory grants are also known as query memory.

Inaccurate memory grants can occur for many reasons. One of the most common is inaccurate cardinality estimations for operators that feed something like a hash join or sort. It could simply be that statistics are out of date.

Like any other problem, our support team would work with customers to provide workarounds. There were many options such as query hints or resource governor settings. But nothing really solved the underlying problem. Users would struggle to do a deep investigation on why the memory grant was not accurate.

In SQL Server 2017, we created a feedback model for memory grants for batch-mode operations (only columnstore at the time) if you used dbcompat 140. In SQL Server 2019, we enhanced the feedback system if dbcompat 150 was enabled to include row-mode operations (but batch mode was also introduced in SQL Server 2019 to be used without columnstore).

The concept of the feedback system was to evaluate how memory was actually used compared with the initial memory grant at the start of execution. On the next execution, if the memory used was significantly higher than the initial grant, the memory grant would be increased. If memory used was lower, the grant would be decreased.

SQL Server effectively learns from previous executions to make future executions more efficient. This innovation for me personally was groundbreaking. All those support cases over the years where customers struggled with spills or blocking problems could be solved automatically by the engine.

Memory Grant Feedback Persistence

If you are using dbcompat 140 or greater with SQL Server 2022 and Query Store is enabled for READ_WRITE, we will persist memory grant feedback details to Query Store. This allows any memory grant feedback to survive server restart or plan cache eviction.

What better way to learn than through an exercise? I’m all about efficiency, so I’ll reuse the example for memory grant feedback I introduced in SQL Server 2019 Revealed in Chapter 2 with a few additions.

A quick comment about this example before you go through it: I’m inducing the conditions where memory grant feedback is needed by forcing a hash join on a query and simulating cardinality problems. But the innovation here is real. This capability is solving real-world problems faced by applications every day that are not worried about whether a hash join is used by the query processor or don’t even know what a memory grant is. That is the intention of IQP. Just make it work!

Prerequisites

The prerequisites for using this example are as follows:

• SQL Server 2022 Evaluation Edition.

• Virtual machine or computer with minimum of two CPUs with 8Gb RAM.

• SQL Server Management Studio (SSMS). The latest 18.x or 19.x build will work.

• A copy of the scripts from the GitHub repo of this book from the ch4_builtinqueryintelligencepersistedmgf directory.

Follow These Steps for the Exercise

Follow these steps to see memory grant feedback with persistence on SQL Server 2022:

1. 1.

   Download the WideWorldImportersDW database backup from https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImportersDW-Full.bak. The restore script in the next steps assumes the backup is located in c:sql_sample_databases. There is a customized backup available with extended data preloaded you can use at https://github.com/microsoft/sqlworkshops-sql2022workshop/releases.

    
2. 2.
   Restore this database to your SQL Server 2022 instance. Execute the script restorewwidw.sql. You may need to change the directory paths for the location of your backup and where you will restore the database files. This script runs the following T-SQL statements:

   USE master;

   GO

   DROP DATABASE IF EXISTS WideWorldImportersDW;

   GO

   RESTORE DATABASE WideWorldImportersDW FROM DISK = 'c:sql_sample_databaseswideworldimportersdw-full.bak'

   WITH MOVE 'wwi_primary' TO 'c:sql_sample_databaseswideworldimportersdw.mdf',

   MOVE 'wwi_userdata' TO 'c:sql_sample_databaseswideworldimportersdw_userdata.ndf',

   MOVE 'wwi_log' TO 'c:sql_sample_databaseswideworldimportersdw.ldf',

   MOVE 'wwidw_inmemory_data_1' TO 'c:sql_sample_databaseswideworldimportersdw_inmemory_data'

   GO
    
3. 3.
   In order to run some of the examples, you will need a larger table than what exists by default in WideWorldImportersDW that does not have a columnstore index. Therefore, execute the script extendwwidw.sql to create a larger table. Extending this database will increase its size, including the transaction log, to about 8Gb overall. This script runs the following T-SQL statements:

   USE WideWorldImportersDW;

   GO

   -- Build a new rowmode table called OrderHistory based off of Orders

   --

   DROP TABLE IF EXISTS Fact.OrderHistory;

   GO

   SELECT 'Buliding OrderHistory from Orders...'

   GO

   SELECT [Order Key], [City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key]

   INTO Fact.OrderHistory

   FROM Fact.[Order];

   GO

   ALTER TABLE Fact.OrderHistory

   ADD CONSTRAINT PK_Fact_OrderHistory PRIMARY KEY NONCLUSTERED([Order Key] ASC, [Order Date Key] ASC)WITH(DATA_COMPRESSION=PAGE);

   GO

   CREATE INDEX IX_Stock_Item_Key

   ON Fact.OrderHistory([Stock Item Key])

   INCLUDE(Quantity)

   WITH(DATA_COMPRESSION=PAGE);

   GO

   CREATE INDEX IX_OrderHistory_Quantity

   ON Fact.OrderHistory([Quantity])

   INCLUDE([Order Key])

   WITH(DATA_COMPRESSION=PAGE);

   GO

   -- Table should have 231,412 rows

   SELECT 'Number of rows in Fact.OrderHistory = ', COUNT(*) FROM Fact.OrderHistory;

   GO

   SELECT 'Increasing number of rows for OrderHistory...';

   GO

   -- Make the table bigger

   INSERT Fact.OrderHistory([City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key])

   SELECT [City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key]

   FROM Fact.OrderHistory;

   GO 4

   -- Table should have 3,702,592 rows

   SELECT 'Number of rows in Fact.OrderHistory = ', COUNT(*) FROM Fact.OrderHistory;

   GO
    
4. 4.
   Set up the demo by executing the script setup.sql. This script runs the following T-SQL statements:

   USE [WideWorldImportersDW];

   GO

   ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

   GO

   ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

   GO

   ALTER DATABASE WideWorldImportersDW SET QUERY_STORE CLEAR ALL;

   GO

   This script will set the dbcompat to 150 to allow memory grant feedback for row mode to be enabled. Query Store is already enabled for this database, so we simply need to clear the procedure cache (to ensure we get a new fresh compile) and Query Store statistics so we can focus only on this query.

    
5. 5.
   To easily simulate a problem where a memory grant is not accurate based on incorrect cardinality, execute the script set_stats.sql. This script executes the following T-SQL statements:

   USE WideWorldImportersDW;

   GO

   UPDATE STATISTICS Fact.OrderHistory

   WITH ROWCOUNT = 1000;

   GO
    
6. 6.
   In a query editor in SSMS, select Query ➤ Include Actual Execution Plan (you can also enable it with Ctrl+M). Then execute the script execute_query.sql, which will take ~30 seconds to complete (the row results are irrelevant to this example). This script executes the following T-SQL statements:

   USE WideWorldImportersDW;

   GO

   SELECT fo.[Order Key], fo.Description, si.[Lead Time Days]

   FROM  Fact.OrderHistory AS fo

   INNER HASH JOIN Dimension.[Stock Item] AS si

   ON fo.[Stock Item Key] = si.[Stock Item Key]

   WHERE fo.[Lineage Key] = 9

   AND si.[Lead Time Days] > 19;

   GO
    
7. 7.

   Select the Execution Plan tab from the results window. You will see a graphical showplan output that looks like Figure 4-3.

    

A flowchart of the query plan. The flow starts with table scan and clustered index scan, and goes through hash match to end with the select cost of 0%.

Notice the yellow warning on top of the Hash Match operator. If you move your cursor over this operator, you can see a warning about a spill to tempdb that looks like Figure 4-4.

A screenshot of an overlapping tab titled hash match. The tab has 2 columns and 18 rows. The cursor points to estimated execution mode.

You can see that the hash join required ~426Mb (52024*8192) of memory and the original grant was only for ~1.4Mb.

1. 8.

   If you move your cursor over the SELECT operator, you can confirm the memory grant was ~1.4Mb as seen in Figure 4-5.

    

A screenshot of an overlapping tab for select cost. The tab titled select has 2 columns and 7 rows. An arrow points to memory grant 1424 K B from the fifth row.

If you right-click the SELECT operator and select Properties, you can expand the MemoryGrantInfo property as shown in Figure 4-6.

A screenshot of a table with 2 columns for memory grant and 1424 K B. Memory grant info is selected in memory grant. An arrow points to no first execution.

IsMemoryGrantFeedbackAdjusted = NoFirstExecution means that the query has been executed for the first time, so no feedback adjustment occurred. Note the MaxUsedMemory value doesn’t reflect the tempdb spill of pages.

1. 9.
   Since we are using SQL Server 2022, after the execution of this query, feedback is stored in Query Store. You can see this feedback by executing the script get_plan_feedback.sql. This script executes the following T-SQL statements:

   USE WideWorldImportersDW;

   GO

   SELECT qpf.feature_desc, qpf.feedback_data, qpf.state_desc, qt.query_sql_text, (qrs.last_query_max_used_memory * 8192)/1024 as last_query_memory_kb

   FROM sys.query_store_plan_feedback qpf

   JOIN sys.query_store_plan qp

   ON qpf.plan_id = qp.plan_id

   JOIN sys.query_store_query qq

   ON qp.query_id = qq.query_id

   JOIN sys.query_store_query_text qt

   ON qq.query_text_id = qt.query_text_id

   JOIN sys.query_store_runtime_stats qrs

   ON qp.plan_id = qrs.plan_id;

   GO

   Your results should look like the following (I flipped the results vertically so you could see them line up for each column):

   feature_desc      Memory Grant Feedback

   feedback_data

   [{"NodeId":"0","AdditionalMemoryKB":"624504"}]

   state_desc        FEEDBACK_VALID

   query_sql_text

   SELECT fo.[Order Key], fo.Description, si.[Lead Time Days]  FROM  Fact.OrderHistory AS fo  INNER HASH JOIN Dimension.[Stock Item] AS si   ON fo.[Stock Item Key] = si.[Stock Item Key]  WHERE fo.[Lineage Key] = 9  AND si.[Lead Time Days] > 19

   last_query_memory_kb      1424

   The feedback_data column shows the new memory grant that will be used for the next execution of the same query, which should be plenty based on the tempdb spill from the first execution.

    
2. 10.

   Execute the execute_query.sql script again. You should see the query complete now in just a few seconds.

   If you look at the graphical query plan, you will notice there is no warning for the hash join operator. If you move your cursor over the SELECT operator, you should see a memory grant like Figure 4-7.

    

A screenshot of an overlapping tab for select cost. The tab titled select has 2 columns and 7 rows. An arrow points to memory grant 611 M B from the fifth row.

If you right-click the SELECT operator and select Properties, you can expand the MemoryGrantInfo and see details like Figure 4-8.

A screenshot of a table with 2 columns for memory grant and 611 M B. Memory grant info is selected in memory grant. 2 arrows point to 6 2 5 5 2 8 and yes adjusting in 611 M B.

IsMemoryGrantFeedbackAdjusted = YesAdjusting means that feedback has been applied from the previous execution to adjust the memory grant. No tempdb spill results in a significantly faster execution time.

1. 11.

   Execute the script get_plan_feedback.sql again. You will see in the results the value for last_query_memory_kb will reflect the new, larger memory grant.

    
2. 12.
   At this point, the behavior of this scenario is exactly like SQL Server 2019, except previously the memory grant feedback was only stored in the cached query plan. Now you can see that the feedback is persisted in Query Store. Execute the script clear_proc_cache.sql to clear the plan cache. This script uses the following T-SQL statements:

   USE [WideWorldImportersDW];

   GO

   ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

   GO
    
3. 13.

   Execute the script execute_query.sql again. You will see the query finishes in a few seconds and uses the same memory grant as stored in the feedback_data column in Query Store.

    

You can disable memory grant feedback persistence by using the following database-scoped configuration option:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF

You can also disable memory grant feedback persistence at the query hint level using the option DISABLE_MEMORY_GRANT_FEEDBACK_PERSISTENCE.

Now memory grant feedback will support a wider variety of workloads using percentiles and persist feedback to Query Store to survive server restarts and plan cache eviction.

Keep track of all the latest information about memory grant enhancements for SQL Server 2022 at https://docs.microsoft.com/sql/relational-databases/performance/intelligent-query-processing-details?#percentile-and-persistence-mode-memory-grant-feedback.

The Intelligent Query Processor

In this chapter you explored what built-in query intelligence means. Simply put, it’s faster queries with no code changes. It leverages enhancements to Query Store, with the built-in store for query performance statistics now on by default for new databases and enhancements for hints and read replicas. It is about nextgen IQP capabilities to get you better performance using approximate percentile, optimized plan forcing, and enhancements to memory grant feedback. You can get all of these improvements even without using the new dbcompat level 160 for SQL Server 2022.

I asked Kate Smith, Senior Program Manager over the IQP space, about her thoughts on the significance of built-in query intelligence in SQL Server 2022:

In my opinion, query processing is the beating heart at the center of SQL Server’s capabilities. All queries leverage it, and all benefit from our efforts there. The latest work in Intelligent Query Processing continues this trend – by allowing the optimizer to self-tune to a customer’s specific needs, we expand the scope of what is possible from requiring manual intervention and move towards a self-regulating system. The features released in SQL 22 expand the capabilities in many ways – addressing previous challenges in memory grant feedback, introducing the ability for the optimizer to use different CE models with cardinality estimation feedback, creating parameter sensitive plans, and even adjusting the degree of parallelism for a query without user input. What’s more, these features can all work together, seamlessly. So while before SQL 22 a problematic query might have required multiple iterations of manual tuning, that same query can now be adjusted, on the fly, across many dimensions, without any user action. These adjustments can be persisted across restarts and can apply across different replicas. Taken together, the latest IQP features will greatly reduce headaches for SQL Server DBAs everywhere. I’m thrilled to see these features land, and I’m excited for users to start seeing their benefit. Be warned though: we are not done! This team is always looking to the future, and what more we can deliver. So, stay tuned.

If you are ready for more, the story gets better in the next chapter. If you are ready to move to dbcompat 160, read on to the next chapter about how built-in query intelligence evolves even further with solutions to three age-old problems: Parameter-Sensitive Plans, cardinality estimation model issues, and degree of parallelism.