- 1. Using SQL Dynamic View
As follows:
Select Top B.text, A.total_worker_time,a.total_logical_reads,a.total_elapsed_time,execution_count from sys.dm_exec_query_stats a Cross as border by Total_worker_time/desc
View the online Help document view: Sys.dm_exec_query_stats can get all the field meanings, mainly
Total_worker_time: Total CPU consumption time
Total_logical_reads: Number of logical Reads
Total_elapsed_time: Time Consuming
Execution_count: Number of executions.
Total_worker_time/execution_count desc Indicates how much CPU is consumed per execution, followed by a flashback order, to find the most CPU-consuming SQL statement. Text
The above can only look for SQL Server after emptying the cache data, if SQL Server restarts, or SQL Server due to insufficient memory causes the execution plan to be freed. Then there is no way to find it.
- 2. Use the attack Profiler. New Trace:
Tick the following events. The first is a stored procedure, the second is a SQL statement
Filter select for more than 100 seconds for slow SQL (in milliseconds), click OK. As follows:
How to find a-dba to execute a slow SQL statement