Microsoft SQL Server Get execution statistics for SQL queries/plans


Example

The following query will return informationa about qeries, their plans and average statistics regarding their duration, CPU time, physical and logical io reads.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id,
        avg_duration, avg_cpu_time, 
        avg_physical_io_reads, avg_logical_io_reads
FROM sys.query_store_plan AS Pl  
JOIN sys.query_store_query AS Qry  
    ON Pl.query_id = Qry.query_id  
JOIN sys.query_store_query_text AS Txt  
    ON Qry.query_text_id = Txt.query_text_id
JOIN sys.query_store_runtime_stats Stats
    ON Pl.plan_id = Stats.plan_id