SQL Server获取超出指定时间的查询（本例是60分钟）

 

DECLARE @longrunningthreshold int
SET @longrunningthreshold=60 
 

;WITH cte AS
  (SELECT [Session_id]=spid,
          [Sessioin_start_time]=
     (SELECT start_time
      FROM sys.dm_exec_requests
      WHERE spid = session_id),
          [Session_status]=Ltrim(Rtrim([status])),
          [Session_Duration]=Datediff(mi,
                                        (SELECT start_time
                                         FROM sys.dm_exec_requests
                                         WHERE spid = session_id), Getdate()),
          [Session_query] = SUBSTRING (st.text,
                                       (qs.stmt_start / 2) + 1,
                                       ((CASE qs.stmt_end
                                             WHEN -1 THEN Datalength(st.text)
                                             ELSE qs.stmt_end
                                         END - qs.stmt_start) / 2) + 1)
   FROM sys.sysprocesses qs CROSS apply sys.Dm_exec_sql_text(sql_handle) st) 

SELECT session_id, session_duration, session_status, [session_query]
FROM cte WHERE session_duration > @longrunningthreshold
and Session_id<>@@SPID