查询sqlserver中的慢sql 

 1 SELECT
 2     a.session_id,
 3     a.client_net_address,
 4     ( total_elapsed_time / execution_count ) / 1000 N'平均时间ms',
 5     total_elapsed_time / 1000 N'总花费时间ms',
 6     total_worker_time / 1000 N'所用的CPU总时间ms',
 7     total_physical_reads N'物理读取总次数',
 8     total_logical_reads / execution_count N'每次逻辑读次数',
 9     total_logical_reads N'逻辑读取总次数',
10     total_logical_writes N'逻辑写入总次数',
11     execution_count N'执行次数',
12     SUBSTRING (
13         st.text,
14         ( qs.statement_start_offset/ 2 ) + 1,
15     ( ( CASE statement_end_offset WHEN - 1 THEN DATALENGTH( st.text ) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1 
16     ) N'执行语句',
17     st.text
18     ,
19     creation_time N'语句编译时间',
20     last_execution_time N'上次执行时间' 
21 FROM
22     sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text ( qs.sql_handle ) st
23     LEFT JOIN (
24     SELECT
25         a.session_id,
26         a.sql_handle sql_handle,
27         b.client_net_address client_net_address 
28     FROM
29         sys.dm_exec_requests a,
30         sys.dm_exec_connections b 
31     WHERE
32         a.session_id = b.session_id 
33     ) a ON qs.sql_handle = a.sql_handle 
34 ORDER BY
35     total_elapsed_time / execution_count DESC

 

posted @ 2023-11-29 15:22  aniymx  阅读(722)  评论(0)    收藏  举报