Query Store is enabled on an Azure SQL database, but a costly ad hoc query that runs only a few times a day never appears in its history. The DBA wants that query captured without changing the application. What should the DBA change?
Show answer and explanation
Answer: A
Query Store's default capture mode is AUTO, which records only queries that execute often enough or consume significant resources. A costly query that runs a few times a day can fall below those thresholds and be skipped. Setting QUERY_CAPTURE_MODE to ALL records every query, so the expensive ad hoc statement appears in history. The application does not need to change, because this is a database-level Query Store option.
Why the other options are wrong
- B. Setting OPERATION_MODE to READ_ONLY stops Query Store from collecting new data. It would make the missing query problem worse rather than fixing capture.
- C. The maximum storage size limits how much data Query Store holds. It does not control which queries are captured, so raising the cap would not add the missing query to the history.
- D. The stale query threshold controls how long Query Store keeps data about queries before cleanup. Shortening it removes history sooner and does not change which queries are captured.