sys.query_store_query question

• as_1234

  SSCrazy

  Points: 2883

  More actions

  January 26, 2026 at 3:12 pm

  #4724215

  All,

  My query is as follows:

  SET DATEFORMAT dmy
  SELECT p.query_id,
  DATEADD(MICROSECOND,-rs.max_duration,rs.first_execution_time) AS starttime,
  first_execution_time AS endtime, q.last_optimize_cpu_time
  FROM sys.query_store_query_text t JOIN sys.query_store_query q
  ON t.query_text_id = q.query_text_id 
  INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id 
  INNER JOIN sys.query_store_runtime_stats rs ON rs.plan_id=p.plan_id
  WHERE OBJECT_NAME(q.object_id) = 'x'
  AND (rs.first_execution_time BETWEEN '19/01/2026 00:00:00' AND '19/01/2026 23:59:59'
  OR rs.first_execution_time BETWEEN '21/01/2026 00:00:00' AND '21/01/2026 23:59:59')
  

  The results are as follows:

  

  Am I correct in understanding that:

  1) The value 957257310 in last_optimize_cpu_time indicates that SQL server took 15 minutes to optimise the query before starting to run it?

  2) The object x is a procedure. Am I correct that the optimisation would take place before any of the queries ran?

  Apologies if I missed an obvious error somewhere.

  Thanks

   

• Steve Jones - SSC Editor

  SSC Guru

  Points: 740730

  More actions

  January 26, 2026 at 4:49 pm

  #4724262

  It does seem that this is 15 minutes compilation/optimization. This should be a one time cost (until the proc changes) that is incurred. This seems very high, but you might check the number of times the query ran since it was compiled

  SELECT q.query_id, 
         q.last_optimize_cpu_time / 1000000.0 AS optimize_cpu_seconds,
         COUNT(*) AS execution_count
  FROM sys.query_store_query q
  WHERE q.query_id = 957257310