Most of the time, when we use SQL statements to query data, it is inevitable to miss the consideration of SQL statement performance, so sometimes it will cause SQL Server service occupation is too high. In order to roughly check which SQL statements cause problems, we can use the following SQL query to find all the most time-consuming SQL statements recently. The specific QUERY SQL statement is as follows:
SELECT s2.dbid, s1.sql_handle, ( SELECT TOP 1 SUBSTRING( s2.text, statement_start_offset / 2 + 1, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), S2. text)) * 2) ELSE statement_end_offset END) -statement_start_offset) / 2 + 1)) AS execute SQL, Last_worker_time 'last time to execute (ms)', last_execution_time' last time to execute ', total_worker_time 'Total time to execute (ms)', Min_worker_time 'minimum execution time (ms)', max_worker_time' maximum execution time (ms)', execution_count, PLAN_GENERation_num, total_Physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid IS NULL ORDER BY last_worker_time DESC, s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;Copy the codeExecution effect:
