SQL SERVER – Query Store Feature for Secondary Replicas

SQL Server 2022 introduces an intriguing feature – the Query Store for secondary replicas. This feature extends the Query Store functionality from primary replicas to secondary replica workloads. In this post, we explore this feature, how to enable it, and some considerations regarding its usage. This feature is currently in preview, meaning it’s not yet intended for production deployments.

What is the Query Store for Secondary Replicas?

The Query Store for secondary replicas feature allows secondary replicas to send query execution information back to the primary replica, which would typically be stored in the Query Store. The primary replica then persists this data within its own Query Store. This results in a shared Query Store between the primary and all secondary replicas. The Query Store exists on the primary replica and stores data for all replicas together.

Enabling the Query Store for Secondary Replicas

Before enabling the Query Store for secondary replicas, you must enable trace flag 12606. Here’s how you can enable the trace flags:

• Launch SQL Server Configuration Manager in Windows.
• Right-click on the SQL Server instance service for your SQL Server 2022 (16.x) instance in the list of SQL Server Services and select Properties.
• Select the Start Parameters tab and add the values: -T12606.

Remember, you must restart the SQL Server instance service for these changes to take effect.

Setting up the Query Store for Secondary Replicas

Firstly, you must have an Always On availability group on your SQL Server instance. If Query Store is not already enabled and in READ_WRITE mode on the primary replica, you must enable it with the following commands:

ALTER DATABASE [DBName] SET QUERY_STORE = ON;
GO
ALTER DATABASE [DBName] SET QUERY_STORE
( OPERATION_MODE = READ_WRITE );

To enable the Query Store on all secondary replicas, connect to the primary replica and execute the following:

ALTER DATABASE [DBName]
FOR SECONDARY SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
GO

To disable the Query Store on all secondary replicas, you can use a similar command:

ALTER DATABASE [DBName]
FOR SECONDARY SET QUERY_STORE = OFF;
GO

Conclusion

We must consider the performance implications as we delve into using the Query Store for secondary replicas. The same channel is used by secondary replicas to send query information back to the primary replica and to keep secondary replicas updated. This can result in the Query Store size growing and potential slowdowns during heavy system loads.

Despite these considerations, the Query Store for secondary replicas in SQL Server 2022 paves the way for more efficient data sharing between primary and secondary replicas. While this feature is not yet ready for production deployments, understanding its workings and performance implications is invaluable for database administrators and developers preparing for future advancements in SQL Server technology.

Before you test this feature on your development server, I recommend reading the official documentation. Any updates or changes will be documented there first.

You can always reach out to me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)