sqlserver 查看cpu占用最高的回话以及sql语句
时间: 2023-05-23 10:04:53 浏览: 207
A:您可以使用如下的SQL代码来查看SQL Server中CPU占用最高的会话以及相关的SQL语句:
```
SELECT TOP 1
total_worker_time/execution_count AS [Avg CPU Time],
total_worker_time AS [Total CPU Time],
total_elapsed_time/execution_count AS [Avg Duration],
total_elapsed_time AS [Total Duration],
(total_logical_reads+total_logical_writes)/execution_count AS [Avg Logical IO],
(total_logical_reads+total_logical_writes) AS [Total Logical IO],
execution_count,
session_id,
plan_handle
FROM
sys.dm_exec_query_stats
ORDER BY [Total CPU Time] DESC;
-- Get the SQL text for a specific plan handle
SELECT
txt.text
FROM
sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS txt
WHERE
plan_handle = <plan handle from previous query>;
```
这里使用了系统视图 `sys.dm_exec_query_stats` 来获取历史查询的统计信息,以便找出CPU占用最高的会话。然后,您可以根据 `plan_handle` 查找相应的SQL语句。请将 `<plan handle from previous query>` 替换为您从第一个查询中找到的特定 `plan_handle`。