When opening the query store for the first time on any give day, it will take a VERY long time to output data.

I took a SQL script from this redgate blog article :

Top 10 most expensive queries based on average runtime

  CAST(query_plan AS XML) AS 'Execution Plan',
FROM sys.query_store_plan qp
INNER JOIN sys.query_store_query q
  ON qp.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
  ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats rs
  ON qp.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

After leaving this query running for 11 minutes I canceled it as it wasn't returning anything. Why is that so? Opening the query store through the GUI will result in the same issue.

In this screenshot you can see how cpu-intensive this query is, why is that so ? Did I improperly configure my query store, is there anything more to do than simply enable the query store and query away ? enter image description here

  • 1
    Should have took this screenshot one second later :(
    – A_V
    Nov 15 '17 at 15:02
  • Are these tables that you're querying "virtual" tables - i.e. they exist only in RAM?
    – Vérace
    Nov 15 '17 at 18:04
  • 1
    What are the configuration options that you used for the query store? What is the execution plan for the query?
    – Joe Obbish
    Nov 16 '17 at 1:33
  • 1
    I'm using the default config. This server gets rebooted at least every months for patches. Tried using the query again today without the cast as XML. After 5 minutes it still didn't worked so I cancelled it.
    – A_V
    Nov 20 '17 at 15:45
  • 1
    Please take a look at my own problem, if you don't mind. We might have hit the same issue.
    – Marian
    Apr 30 '18 at 19:41