SQL statement statistics query .
SELECT TOP 10 total_worker_time / 1000 AS [ executed since compilation CPU total time（ ms)],
total_elapsed_time/1000 as [ the total time taken to complete the execution of this plan ],
total_elapsed_time / execution_count/1000 as [ the average time taken to complete and execute this plan ],
execution_count as [ the number of times executed since the last compilation ],
creation_time as [ compilation schedule time ],
deqs.total_worker_time / deqs.execution_count / 1000 AS [ average usage CPU time（ ms)],
last_execution_time AS [ last time the plan was executed ],
total_physical_reads [ the total number of physical reads performed during execution after compilation ],
total_logical_reads/execution_count [ average number of logical reads ],
min_worker_time /1000 AS [ the minimum amount used during a single execution period CPU time（ ms)],
max_worker_time / 1000 AS [ maximum used during a single execution period CPU time（ ms)],
SUBSTRING(dest.text, deqs.statement_start_offset / 2 + 1,
(CASE
WHEN deqs.statement_end_offset = -1 THEN
DATALENGTH(dest.text)
ELSE deqs.statement_end_offset
END - deqs.statement_start_offset
) / 2 + 1) AS [ implement SQL],
dest.text as [ complete SQL],
db_name(dest.dbid) as [ database name ],
object_name(dest.objectid, dest.dbid) as [ object name ]
,deqs.plan_handle [ query the compiled plan to which it belongs ]
FROM sys.dm_exec_query_stats deqs WITH(NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
--average usage CPU time descending
ORDER BY (deqs.total_worker_time / deqs.execution_count / 1000) DESC
--execution time in descending order
--ORDER BY total_elapsed_time / execution_count DESC;
Average CPU usage time in descending order
Execution time in descending order
View execution plan .
select * from sys.dm_exec_query_plan(0x05000A0045E84551103903112400000001000000000000000000000000000000000000000000000000000000)
