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

  • 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
  • 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
  • Uptime in the screenshot suggests the server has been recently restarted. Is it frequently restarted? – Sir Swears-a-lot Nov 20 '17 at 1:51
  • It's likely the CAST to XML that is the issue. Remove that from the SELECT and see if it finishes without issue. You likely have a complex query plan with many nodes (or just large in size) that is taking a long time to render as XML. The dm_exec_cached_plans has a column for size_in_bytes that may help you find which plan is the culprit. HTH. – SQLRockstar Nov 20 '17 at 12:18

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.