SQL Server Find Missing Index using Query Store

--Query to find missing index from query store, from last 30days usage

SELECT TOP 20

qsq.query_id,

SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,

SUM(qrs.count_executions) AS sum_executions,

AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,

SUM(qsq.count_compiles) AS sum_compiles,

(SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt

WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,

TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2

WHERE qsp2.query_id=qsq.query_id

ORDER BY qsp2.plan_id DESC)) AS query_plan

FROM sys.query_store_query qsq

JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id

CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx

JOIN sys.query_store_runtime_stats qrs on

qsp.plan_id = qrs.plan_id

JOIN sys.query_store_runtime_stats_interval qsrsi on

qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id

WHERE

qsp.query_plan like N'%<MissingIndexes>%'

and qsrsi.start_time >= DATEADD(HH, -720, SYSDATETIME())

GROUP BY qsq.query_id, qsq.query_hash

ORDER BY est_logical_reads DESC;

GO