Vous êtes ici :
Ce script vous permet d’extraire la liste des 20 requêtes les plus coûteuses exécutées sur votre instance SQL Server.
Libre à vous de filtrer (sur le nombre d’exécutions, les lectures ou les écritures, la base de données, etc…) ou de trier (par durée moyenne ou cumulée, par lectures ou écritures, etc…)
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 |
/*------------------------------------------------------------------- [SCRIPT] Query statistics [DATABASE] all (or filter with where clause) [DESCRIPTION] Query stats [MAJ PAR] DATAFLY - Arian Papillon [DATE] 20181010 -------------------------------------------------------------------*/ SELECT TOP 20 st.text, pl.query_plan, execution_count, creation_time, last_execution_time, total_worker_time, total_worker_time / execution_count AS [Avg LogicalReads], qs.min_worker_time, qs.max_worker_time, CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Duration (s)], CAST((CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) / execution_count) AS DECIMAL(28, 2)) AS [Avg Duration (s)], CAST(qs.min_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Min Duration (s)], CAST(qs.max_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Max Duration (s)], total_logical_reads AS [Total LogicalReads], total_logical_reads / execution_count AS [Avg LogicalReads], min_logical_reads AS [Min LogicalReads], max_logical_reads AS [Max LogicalReads], total_logical_writes AS [Total LogicalWrites], total_logical_writes / execution_count AS [Avg LogicalWrites], min_logical_writes AS [Min LogicalWrites], max_logical_writes AS [Max LogicalWrites], total_logical_reads + total_logical_writes AS [AggIO], CAST((total_logical_reads + total_logical_writes) / (execution_count + 0.0) AS DECIMAL(28, 2)) AS [AvgIO], total_rows AS [Total Rows], total_rows / execution_count AS [Avg rows], min_rows AS [Min rows], max_rows AS [Max rows], qs.min_grant_kb, qs.max_grant_kb, qs.total_grant_kb, (qs.total_grant_kb / qs.execution_count) AS [AvgGrantKB], DB_NAME(st.dbid) AS database_name, OBJECT_NAME(st.objectid, st.dbid) AS object_name, st.objectid AS OBJECT_ID, sql_handle, plan_handle FROM sys.dm_exec_query_stats qs OUTER APPLY sys.dm_exec_sql_text(sql_handle) st OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS pl WHERE (1=1) -- AND DB_NAME(st.dbid) = 'XXXX' -- AND total_logical_reads + total_logical_writes > 0 -- AND execution_count > 100 -- many executions -- AND sql_handle IS NOT NULL ORDER BY qs.total_worker_time DESC -- qs.total_grant_kb DESC; -- total_logical_reads DESC; -- total_logical_reads + total_logical_writes DESC -- [Total Duration (s)] DESC; -- [Avg Duration (s)] DESC |
Table of Contents