SQL Server 2022 introduces an exciting feature: the ability to access the Query Store from a secondary replica in an Availability Group. This enhancement offers a significant advantage, allowing you to redirect troubleshooting tasks to a secondary replica without impacting the performance of the primary replica. It’s crucial to note that all replicas (primary and secondary) within an Availability Group share a single Query Store.
When you create a new database in SQL Server 2022, the Query Store is enabled by default. This automatic activation is part of the server’s integration with the newly introduced feedback mechanisms within the Intelligent Query Processing feature family.
However, a critical question arises: how do you enable the Query Store on secondary replicas when migrating existing databases to SQL Server 2022, especially if these databases have the Query Store feature disabled? Unfortunately, the process isn’t as straightforward as one might hope.
To access the Query Store in an Availability Group, you must first activate it on the primary replica using the following T-SQL commands:
ALTER DATABASE [QueryStoreDemo] SET QUERY_STORE = ON GO ALTER DATABASE [QueryStoreDemo] SET QUERY_STORE (OPERATION_MODE = READ_WRITE) GO
Once executed, these commands activate the Query Store only on the current primary replica. Secondary replicas remain unaware of the Query Store until you enable it explicitly, which can be done with this T-SQL statement:
ALTER DATABASE [QueryStoreDemo] FOR SECONDARY SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE ) GO
Before enabling the Query Store on a secondary replica, you need to activate trace flag 12606 on the primary replica. Without this flag, attempting to enable the Query Store on a secondary replica will result in a syntax error.
After executing the T-SQL statement, the Query Store becomes active on all secondary replicas. Subsequently, you can remove the trace flag from the primary replica. Adding new secondary replicas to your Availability Group will automatically enable the Query Store on them without additional steps.
Caution: The Query Store feature for secondary replicas is currently in preview and should not be used in production environments. For more details, refer to the SQL Server 2022 release notes.
Online Training Opportunity
To dive deeper into this topic and master the nuances of SQL Server Availability Groups, consider enrolling in my comprehensive online training course. This course is designed to guide you through the complexities of managing and optimizing SQL Server Availability Groups.
You’ll gain hands-on experience, insider tips, and the confidence to implement these features effectively in your own SQL environment. Visit the course website for more information and to secure your spot in this sought-after training.
Thanks for your time,
-Klaus
Note: this blog posting was copy-edited by ChatGPT