原 MSSQL中的耗费性能的SQL查询
Tags： 原创MSSQLSQL Server查找耗费性能SQL抓取
👉 本文共约337个字，系统预计阅读时间或需2分钟。
目录
耗费CPU的历史SQL
按平均 CPU 时间获取有关前五个查询的信息
以下示例返回前五个查询的 SQL 语句文本和平均 CPU 时间。
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 | SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time/execution_count DESC; select creation_time, execution_count, TotalWorkerTime, TotalLogicalReads, TotalPhysicalReads, TotalElapsedTime, SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS QueryText, qp.query_plan AS ExecutionPlan from ( SELECT TOP 10 qs.creation_time, qs.execution_count, qs.total_worker_time AS TotalWorkerTime, qs.total_logical_reads AS TotalLogicalReads, qs.total_physical_reads AS TotalPhysicalReads, qs.total_elapsed_time AS TotalElapsedTime, qs.statement_start_offset, qs.statement_end_offset, qs.plan_handle, qs.sql_handle FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time/execution_count desc ) qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp |
按 CPU 对现有请求进行排序
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 | SELECT [req].[session_id], [req].[start_time], [req].[cpu_time] AS [cpu_time_ms], OBJECT_NAME([ST].[objectid], [ST].[dbid]) AS [ObjectName], SUBSTRING( REPLACE( REPLACE( SUBSTRING( [ST].[text], ([req].[statement_start_offset] / 2) + 1, ((CASE [req].[statement_end_offset] WHEN -1 THEN DATALENGTH([ST].[text]) ELSE [req].[statement_end_offset] END - [req].[statement_start_offset] ) / 2 ) + 1 ), CHAR(10), ' ' ), CHAR(13), ' ' ), 1, 512 ) AS [statement_text] FROM [sys].[dm_exec_requests] AS [req] CROSS APPLY [sys].dm_exec_sql_text([req].[sql_handle]) AS [ST] ORDER BY [req].[cpu_time] DESC; GO |
正在运行的SQL统计
最耗费CPU的正在运行的SQL
1 2 3 | select top 5 * from vw_session_lhr order by cpu_time desc; |
最耗费磁盘IO的正在运行的SQL
1 2 3 | select top 5 * from vw_session_lhr order by (reads+writes) desc; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
