Select session_id,cpu_time,dt,logical_reads
from [dbo].[Process_monitor]
WHERE dt between '20150715' and '20150715 00:15'
Refer to the screenshot from query store
Plan 99 uses "NCIX_dt_present"( a non clustered Index ) for the query
To ensure that the query always uses the Index "NCIX_dt_present", lets force the plan 99 on query 92 as shown below.
Run the select query few times and verify if plan is getting forced. Now lets drop the index
DROP INDEX Process_monitor.NCIX_dt_present
GO
Re run the select query again few times
Select session_id,cpu_time,dt,logical_reads
from [dbo].[Process_monitor]
WHERE dt between '20150715' and '20150715 00:15'
List of query store failures can be found from the query below
SELECT
qt.query_sql_text,q.query_id,
CAST(query_plan AS XML) AS 'Execution Plan',
qp.force_failure_count,qp.last_force_failure_reason_desc,
rs.first_execution_time, rs.last_execution_time,
rs.count_executions,
rs.avg_duration,
rs.avg_rowcount,
rs.last_logical_io_reads,
rs.avg_cpu_time,
rs.avg_query_max_used_memory
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
WHERE qp.is_forced_plan = 1
AND qp.force_failure_count > 0
Order by rs.last_execution_time
