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 who 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 initially released with SQL Server 2016 and provided the ability for DBAs (Database Administrators) to quickly gain insights on query plan choices 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, methods, and runtime statistics and persists this data in 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 must manually enable Query Store in the settings.
Another welcome update is that Query Store will now be supported on Availability Group read-only replicas, allowing DBAs to provide better performance analytics for read-only workloads.
Query Store hints are another enhancement for Query Store, and if you have used Azure SQL Database and Azure SQL Managed Instance, you will already be familiar. Query Store hints shape query plans and enhance query execution without changing application code.
Hopefully, that is an excellent introduction to these new Query Store enhancements; next week, we’ll be looking at the Link feature for Azure SQL Managed Instance, so stay tuned!
Conclusion
If you have any questions on SQL Server 2022 or need help upgrading to the later versions of SQL Server, please feel free to get in touch; we will be delighted to assist.
Digital Samurai are a team of data and SQL experts that have experienced every version through the years – yes, back to SQL Server 6.0!
We also have a wealth of experience in upgrades and migrations and are always available to help.