2

I'm trying to use Query Store on SQL Server on Amazon RDS, and not having any luck. The query store works for a few hours after each failover, but then stops working. For example, our last manual failover was June 13 @ 5am. When I query the records in the query store, the last record there is from June 13 @ 10am. When I try to clear the query store, the records aren't removed. Same happens if I try to turn query store off for the databases.

SELECT  RecordCount = COUNT(*), 
        LastDate = MAX(last_execution_time)
FROM    sys.query_store_runtime_stats;
GO
ALTER DATABASE [foo] SET QUERY_STORE CLEAR;
GO
SELECT  RecordCount = COUNT(*), 
        LastDate = MAX(last_execution_time)
FROM    sys.query_store_runtime_stats;

... returns ...

RecordCount LastDate
----------- ----------------------------------
9025        2017-06-13 10:13:16.0500000 +00:00

(1 row(s) affected)

RecordCount LastDate
----------- ----------------------------------
9025        2017-06-13 10:13:16.0500000 +00:00

(1 row(s) affected)

I'm hoping that I'm just missing an RDS specific method for dealing with the query store, but have found nothing of use yet. Any suggestions?

  • What is the result set of this query? SELECT actual_state, actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options; – Dean Savović Jun 27 '17 at 19:06
  • 2, READ_WRITE, 0, 31, 150 – Scott Ivey Jun 27 '17 at 19:27
  • Also worth noting, SQL version is 13.0.4422 - 2016 Standard SP1 + CU2 – Scott Ivey Jun 27 '17 at 19:32
  • and now you are not able to clear the content of the Query Store? – Dean Savović Jun 28 '17 at 11:27
  • correct - using the sa account, when I run alter database set query_store clear, nothing happens. Query store records are all still there, and I get no error saying that it didn't work. – Scott Ivey Jun 28 '17 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.