I'm having an issue getting query store to collect and record data on our production workload, however, it works as expected on our non-production instances (development and staging). The only difference between these three environments is that production is a multi-AZ RDS instance, while dev and staging are single AZ.
All instances are on 2016 SP1 CU7.
The query store settings are identical across all servers and databases, and are as follows:
- actual state: read_write
- flush_interval: 15 mins
- query_capture_mode: all
- size_based_cleanup: auto
- max_plans_per_query: 200
- The max storage size varies based on the database activity level; the minimum I have set is 500mb, and the max is 2gb.
It may also be worth noting that our staging server creates daily from a snapshot of production, and query store seems to be working perfectly on that after being created.
Does anyone have any suggestions on what I may be able to try next, or know of any caveats with query store in RDS?