While attempting to find a query that ran on a read only replica, it got me wondering whether I needed to be querying the secondary, the primary, or the always on listener. Turns out the answer is none of the above. Query store only collects information from the primary. If you query any of these it will only return queries that were run on the primary.
So if I can’t monitor query level performance on my read only secondary via query store, what are my options:
- DMVs (not historical)
- Extended Events (much better than profiler but not good at constantly monitoring for all workload over indefinite periods of time)
- Use third party monitoring tools. SQLGrease (shameless plug) provides all the goodness of query store even if it’s not available +many more features query store doesn’t offer.
Oddly enough just as I was about to hit the publish button on this post, I received Brent Ozar’s daily email newsletter which pointed out this is the top feature request from SQL Server users.