SQL2017 DBMirroring Query store not working on mirrored databases

Question

Post reply

SQL2017 DBMirroring Query store not working on mirrored databases

Johan Bijnens

SSC Guru

Points: 135265
More actions
November 7, 2023 at 2:42 pm

#4323084
I have noticed on our SQL2017 mirrored databases Query store is not working.
( Moving to AGs is not an option. )
Settings in use:
```
ALTER DATABASE [MyMirroredDb] SET QUERY_STORE = ON
GO

ALTER DATABASE [MyMirroredDb] 
SET QUERY_STORE 
(OPERATION_MODE = READ_WRITE, 
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), 
DATA_FLUSH_INTERVAL_SECONDS = 900, 
INTERVAL_LENGTH_MINUTES = 60, 
MAX_STORAGE_SIZE_MB = 100, 
QUERY_CAPTURE_MODE = ALL, 
SIZE_BASED_CLEANUP_MODE = AUTO, 
MAX_PLANS_PER_QUERY = 200, 
WAIT_STATS_CAPTURE_MODE = ON)
GO
```
What am I missing ?

For the record: for non-mirrored databases Query Store works as expected.
Johan

Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 8, 2023 at 10:06 pm

This was removed by the editor as SPAM
January 13, 2024 at 4:56 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 2

Mirrored databases can't be written to, right? If so, then I'm pretty sure Query Store won't work on a mirrored database. Same as AG. It doesn't work there either (except for SQL Server 2022).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Johan Bijnens SSC Guru Points: 135265 More actions · Answer 3

My bad. I left some ambiguity in my question.

I'm talking about the primary database of a mirrored set, not about the recoverying version of it.

btw: In the mean time I have noticed all QS states were off (DesiredState) , which is strange because I configured them to be readwrite long time ago.

So I have alterd them to be ReadWrite again and monitor them for a while

ALTER DATABASE [mymirroreddb] SET QUERY_STORE = ON
GO

ALTER DATABASE [mymirroreddb] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE, 
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), 
DATA_FLUSH_INTERVAL_SECONDS = 900, 
INTERVAL_LENGTH_MINUTES = 15, 
MAX_STORAGE_SIZE_MB = 1024, 
QUERY_CAPTURE_MODE = AUTO, 
SIZE_BASED_CLEANUP_MODE = AUTO, 
MAX_PLANS_PER_QUERY = 200, 
WAIT_STATS_CAPTURE_MODE = ON
)
GO

/* BTW */
ALTER DATABASE [mymirroreddb] SET QUERY_STORE CLEAR ALL;
/* does not delete rows in the QS tables */

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 4

Ah, sorry I misunderstood.

If they were switched to read only on you, it's likely something to do with size and the cleanup management. The size-based cleanup should be a backup to the time-based cleanup. The reason is, the sized-based cleanup can, for want of a term, miss it's window to cleanup. You can still get full storage and it will automatically switch Query Store to read only. Getting an alert when a databases Query Store status changes will help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning