October 12

0 comments

SQL Server 2022 – Query Store Enhancements

By Ryan Freeman

October 12, 2022


Welcome to the second in the series of our SQL Server 2022 feature specific posts. Last week we were looking at Parameter Sensitive Plan optimisation (or PSP optimisation for short) and this week we are looking at Query Store enhancements to this latest version of SQL Server.  

When will we have SQL Server 2022? 

For those of you don’t know, SQL Server 2022 is coming and should be released later in 2022. At the time of writing, you can now download SQL Server 2022 preview to try the new features in the latest release. ‘SQL Server 2022 is the most Azure-enabled release of SQL Server yet, with continued innovation across performance, security, and availability.’ – Microsoft. 

What is Query Store? 

Query Store, also dubbed the ‘flight data recorder’ was originally released with SQL Server 2016 and provides the ability for DBAs (Database Administrators) to quickly gain insights on query plan choice and the impact these choices have on SQL Server performance.  

Using Query Store, DBAs have the power to force execution plans, identify regressions and identify elevated waits statistics. When enabled, Query Store asynchronously captures a history of queries, plans, and runtime statistics, and persists this data at the database scope. 

What are the new enhancements? 

Firstly, Query Store will now be enabled by default, ensuring new databases can take advantage of all the features dependent on query store to its full extent. It’s worth noting that this will apply to all new databases created in SQL Server 2022, however for any databases restored into SQL Server 2022 you will need to enable Query Store manually in the settings. 

Another welcome update is that Query Store will now be supported on Availability Group read-only replicas which will allow DBAs to provide better performance analytics for read-only workloads.  

Query Store hints is another enhancement for Query Store and if you have used Azure SQL Database and Azure SQL Managed Instance then you will already be familiar. Query Store hints is used to shape query plans and enhance query execution without changing application code. 

Hopefully that is a good introduction to this these new Query Store enhancements, next week we’ll be looking at Link feature for Azure SQL Managed Instance so stay tuned! 

Are you excited by the new version of SQL 2022? At Digital Samurai we certainly are! We would love to hear your thoughts. 

It may also benefit you to know that on the 27th July 2022, extended support ended for SQL Server version 2012. Is this a version you are using, or any prior? 

If you have any questions on SQL Server 2022 or upgrading to the later versions of SQL Server, then please feel free to get in touch and we will be very happy to assist. 

Digital Samurai are a team of data and SQL experts that have that have experienced every version through the years – yes, all the way back to SQL Server 6.0! We also have a wealth of experience in upgrades and migrations and are always on hand to help. 

Ryan Freeman

About the author

You might also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>