SQL Server query store usage example

To find query duration average. 

declare @searchSQL varchar(max) ='%SELECT%vwVesselsEditableForms%join%tblEmployeePOAAccess%'

SELECT qsp.query_id, QSP.plan_id, min(QSRS.first_execution_time) min_first_execution_time, max(QSRS.last_execution_time)  max_last_execution_time, sum(qsrs.count_executions*QSRS.avg_duration/1000000) total_seconds, 

qsq.query_hash, qsp.query_plan_hash, avg(QSRS.avg_duration)/1000000 avg_duration_sec, sum(qsrs.count_executions) total_executions, min(qsrs.min_duration)/1000000 min_duration_sec,max(qsrs.max_duration)/1000000 max_duration_sec,

QST.query_sql_text, qsp.query_plan

FROM sys.query_store_plan AS QSP  JOIN sys.query_store_query AS QSQ      ON QSP.query_id = QSQ.query_id  

JOIN sys.query_store_query_text AS QST      ON QSQ.query_text_id = QST.query_text_id 

JOIN sys.query_store_runtime_stats  QSRS   ON QSP.plan_id =QSRS.plan_id

where 

qst.query_sql_text like @searchSQL

group by qsp.query_id, QSP.plan_id,QST.query_sql_text, qsq.query_hash, qsp.query_plan_hash,  qsp.query_plan

order by qsp.query_id,max(QSRS.last_execution_time) desc

To list the query execution history, like oracle active session history query stats.

SELECT qsp.query_id, QSP.plan_id, 

QSRS.runtime_stats_interval_id,  QSRS.first_execution_time, QSRS.last_execution_time, -- timezone is not right, show 7 hours ahead

qsrs.count_executions*QSRS.avg_duration/1000000 total_seconds, QST.query_sql_text, qsq.query_hash, qsp.query_plan_hash, QSRS.last_duration,

QSRS.avg_duration, qsrs.count_executions,qsrs.min_duration,qsrs.max_duration,qsrs.stdev_duration,qsp.query_plan, QSQ.last_execution_time,  -- timezone is not right, show 7 hours ahead, also sometimes it's even less than the first_execution_time

qsrs.avg_rowcount,qsrs.last_rowcount,qsrs.min_rowcount,qsrs.max_rowcount,stdev_rowcount,

QSRS.avg_logical_io_reads,QSRS.avg_logical_io_writes 

FROM sys.query_store_plan AS QSP  JOIN sys.query_store_query AS QSQ      ON QSP.query_id = QSQ.query_id  

JOIN sys.query_store_query_text AS QST      ON QSQ.query_text_id = QST.query_text_id 

JOIN sys.query_store_runtime_stats  QSRS   ON QSP.plan_id =QSRS.plan_id

--where QST.query_sql_text like '%WITH AppointmentsData AS%SELECT S%FETCH NEXT @PageSize ROWS ONLY%'

where 

--qsp.query_id=366311 and

--QSRS.max_duration > 20000000 and 

--qsrs.count_executions*QSRS.avg_duration/1000000>60  and -- 60 seconds

--qst.query_sql_text like '%SELECT%DISTINCT%AMS.*%,%PRW.lngSourceEntityID%AS%VesselID,%PRW.PortsID%AS%PortsID%FROM%tblAMS_Schedules%AS%AMS%'

qst.query_sql_text like  @searchSQL

--and QSRS.runtime_stats_interval_id in (select runtime_stats_interval_id from  sys.query_store_runtime_stats_interval where start_time>getdate()-7)

--order by qsrs.count_executions*QSRS.avg_duration/1000000 desc

order by qsp.query_id,QSRS.runtime_stats_interval_id desc