dm_exec_sql_text的使用
2023-12-26 11:53阅读：
SELECT t.* FROM
sys.dm_exec_requests AS r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS
t WHERE session_id =
59 -- modify this value with your actual
spid
B. 按平均 CPU
时间获取有关前五个查询的信息
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;
C. 提供批处理执行统计信息
以下示例返回按批执行的 SQL 查询的文本，并提供有关它们的统计信息。
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 sql_statement, execution_count,
plan_generation_num, last_execution_time, total_worker_time,
last_worker_time, min_worker_time, max_worker_time,
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 s1.sql_handle, s1.statement_start_offset,
s1.statement_end_offset;
