Vous êtes ici :
Ce script interroge le Query Store pour extraire le top 50 des requêtes les plus longues (en durée cumulée) depuis les dernières 4 heures, avec les plans d’exécution et informations associées. A adapter selon le besoin.
Query Store Report
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 |
DECLARE @hours INT; SET @hours = 4; WITH RuntimeStats AS (SELECT rs.plan_id -- duration , AVG(rs.avg_duration) AS avg_duration , MIN(rs.min_duration) AS min_duration , MAX(rs.max_duration) AS max_duration -- executions , SUM(rs.count_executions) AS count_executions -- cpu , AVG(rs.avg_cpu_time) AS avg_cpu_time , MIN(rs.min_cpu_time) AS min_cpu_time , MAX(rs.max_cpu_time) AS max_cpu_time -- logical reads , AVG(rs.avg_logical_io_reads) AS avg_logical_io_reads , MIN(rs.min_logical_io_reads) AS min_logical_io_reads , MAX(rs.max_logical_io_reads) AS max_logical_io_reads --logical writes , AVG(rs.avg_logical_io_writes) AS avg_logical_io_writes , MIN(rs.min_logical_io_writes) AS min_logical_io_writes , MAX(rs.max_logical_io_writes) AS max_logical_io_writes -- physical reads , AVG(rs.avg_physical_io_reads) AS avg_physical_io_reads , MIN(rs.min_physical_io_reads) AS min_physical_io_reads , MAX(rs.max_physical_io_reads) AS max_physical_io_reads -- rowcount , AVG(rs.avg_rowcount) AS avg_rowcount , MIN(rs.min_rowcount) AS min_rowcount , MAX(rs.max_rowcount) AS max_rowcount -- tempdb , AVG(rs.avg_tempdb_space_used) AS avg_tempdb_space_used , MIN(rs.min_tempdb_space_used) AS min_tempdb_space_used , MAX(rs.max_tempdb_space_used) AS max_tempdb_space_used -- DOP , ROUND(AVG(rs.avg_dop), 0) AS avg_dop , MIN(rs.min_dop) AS min_dop , MAX(rs.max_dop) AS max_dop -- memory , ROUND(AVG(rs.avg_query_max_used_memory), 0) AS avg_query_used_memory , MIN(rs.min_query_max_used_memory) AS min_query_used_memory , MAX(rs.max_query_max_used_memory) AS max_query_used_memory -- execution time , MIN(rs.first_execution_time) AS first_execution_time , MAX(rs.last_execution_time) AS last_execution_time FROM sys.query_store_runtime_stats AS rs WHERE rs.last_execution_time > DATEADD(HOUR, -@hours, GETUTCDATE()) GROUP BY rs.plan_id) SELECT TOP (50) ---- Query consolidated q.query_id , qt.query_sql_text , SUM(ROUND((rs.avg_duration / 1000), 0) * rs.count_executions) OVER (PARTITION BY q.query_id) AS total_query_duration_ms , ROUND( SUM(rs.avg_duration / 1000 * rs.count_executions) OVER (PARTITION BY q.query_id) / SUM(rs.count_executions) OVER (PARTITION BY q.query_id) , 0 ) AS avg_query_duration_ms , MIN(ROUND((rs.min_duration / 1000), 0)) OVER (PARTITION BY q.query_id) AS min_query_duration_ms , MAX(ROUND((rs.max_duration / 1000), 0)) OVER (PARTITION BY q.query_id) AS max_query_duration_ms , SUM(rs.count_executions) OVER (PARTITION BY q.query_id) AS query_exec_count ---- plans consolidated informations , rs.plan_id , rs.count_executions AS plan_exec_count --, plan_type_desc -- SQL2022 -- duration ms , ROUND((rs.avg_duration / 1000), 0) * rs.count_executions AS total_duration_ms , ROUND(rs.avg_duration / 1000, 0) AS avg_duration_ms , ROUND(rs.min_duration / 1000, 0) AS min_duration_ms , ROUND(rs.max_duration / 1000, 0) AS max_duration_ms -- cpu time ms , ROUND((rs.avg_cpu_time / 1000) * rs.count_executions, 0) AS total_cpu_time_ms , ROUND(rs.avg_cpu_time / 1000, 0) AS avg_cpu_time_ms , ROUND(rs.min_cpu_time / 1000, 0) AS min_cpu_time_ms , ROUND(rs.max_cpu_time / 1000, 0) AS max_cpu_time_ms -- logical reads , ROUND(rs.avg_logical_io_reads * rs.count_executions, 0) AS total_logical_io_reads , ROUND(rs.avg_logical_io_reads, 0) AS avg_logical_io_reads , rs.min_logical_io_reads AS min_logical_io_reads , rs.max_logical_io_reads AS max_logical_io_reads -- logical writes , ROUND(rs.avg_logical_io_writes * rs.count_executions, 0) AS total_logical_io_writes , ROUND(rs.avg_logical_io_writes, 0) AS avg_logical_io_writes , rs.min_logical_io_writes AS min_logical_io_writes , rs.max_logical_io_writes AS max_logical_io_writes -- rowcount , ROUND(rs.avg_rowcount * rs.count_executions, 0) AS total_row_count , ROUND(rs.avg_rowcount, 0) AS avg_row_count , rs.min_rowcount AS min_row_count , rs.max_rowcount AS max_row_count -- tempdb , ROUND(rs.avg_tempdb_space_used * rs.count_executions, 0) AS total_tempdb_space_used , ROUND(rs.avg_tempdb_space_used, 0) AS avg_tempdb_space_used , rs.min_tempdb_space_used AS min_tempdb_space_used , rs.max_tempdb_space_used AS max_tempdb_space_used -- DOP , ROUND(rs.avg_dop, 0) AS avg_dop , rs.min_dop AS min_dop , rs.max_dop AS max_dop -- memory , ROUND(rs.avg_query_used_memory, 0) * 8 AS avg_query_used_memory_kb , rs.min_query_used_memory * 8 AS min_query_used_memory_kb , rs.max_query_used_memory * 8 AS max_query_used_memory_kb -- compilation , CAST(p.last_compile_start_time AS DATETIME2(0)) AS last_compile_start_time , p.last_compile_duration / 1000 AS last_compile_ms , ROUND(p.avg_compile_duration / 1000, 0) AS avg_compile_ms , p.count_compiles -- execution datetime , CAST(rs.first_execution_time AS DATETIME2(0)) AS first_execution_time , CAST(rs.last_execution_time AS DATETIME2(0)) AS last_execution_time -- plan , TRY_CONVERT(XML, p.query_plan) AS query_plan_xml , p.query_plan AS query_plan_text FROM RuntimeStats rs JOIN sys.query_store_plan AS p ON rs.plan_id = p.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id ORDER BY -- Query total duration SUM(ROUND((rs.avg_duration / 1000), 0) * rs.count_executions) OVER (PARTITION BY q.query_id) DESC -- Plan first execution time , rs.first_execution_time DESC; |
Table of Contents