This is slightly complex to explain, but very easy to reproduce.
For about 10%-30% queries (by count) significant amounts of Query Store data is not available. I have looked at SQL 2016 and SQL 2017 where Query store had been running for weeks+, where there was activity on the database(s).
The query below will return two sets of data, the top set does not have a value for
query_store_query.last_execution_time, while the bottom set does have data in the field. The 'have nots' are also missing most the run time stats data.
EXEC sp_query_store_flush_db; --Same results without this, but just to rule it out in the examples. go Select * from sys.query_store_query where query_store_query.last_execution_time is null -- there are bunch of these, also missing other data, why? Select * from sys.query_store_query where query_store_query.last_execution_time is not null
I Initially discovered this using exported Query Store data My data is in an Excel sheet, I sorted and compared and did not find any common factors separating the 'haves' from the 'have nots'
To rule out data export issues, I used the code above to get data directly from the system dataview. Similar results to findings in exported data.
To rule out the system dataview, I used "Tracked Queries" to report from the root data. (Query Store > Tracked queries > Configure)
3.A. For the 'haves', the query plan(s) and such are displayed, just as you would expect
3.B. For the 'have nots', there is no query plan and no metrics
The only scope limiting factor I have found is that on very active databases, the 'have nots' are limited to the last few hours. But on slow databases, the 'have nots' can have initial_compile_start_time dating back several weeks. (I suspect the 'have nots' are being purged then recreated the next time the now "Never seen before, new" query is run)
The 'have nots' can have any range of compile counts, number of plans, etc.
What do the 'have nots' have in common? Why are they missing data when others are not?
Why this matters: If as I currently suspect, the run time data is not being captured correctly on the 'have nots' some/all of the most resource intensive queries may not be reported in query store reports.
Update: Query Store Capture Mode
All does not eliminate this issue.
Using the suggested setting by Erin Stellato I have Query Store Capture Mode set to
Auto, as pointed out in a comment and answer this could be the cause. But after testing (purged the QS on one of my database and set it to ALL), I am still seeing some 'have nots' after the database has run for a while