f T-SQL 查看自行設定的 Long Query ~ 迪貝之家

T-SQL 查看自行設定的 Long Query

DECLARE slow_query_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT      wait_type,
wait_time/1000 wait_time,
            status   ,
            session_id  ,
            cpu_time/1000 cpu_time  ,
            convert(varchar,start_time, 120) start_time  ,
            total_elapsed_time/1000 total_elapsed_time,
            name   
FROM        sys.dm_exec_requests r
                        LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE       r.session_id > 50 AND r.session_id <> @@SPID
and         r.total_elapsed_time/1000 > 600
and         r.wait_time/1000 > 60
and         wait_type not like '%BROKER_TRANSMITTER%'
order by total_elapsed_time desc;

DECLARE @outputinfo varchar(MAX);
set   @outputinfo = N'N';
DECLARE @wait_type varchar(100);
DECLARE @wait_time bigint;
DECLARE @status varchar(100);
DECLARE @session_id int;
DECLARE @cpu_time bigint;
DECLARE @start_time varchar(100);
DECLARE @total_elapsed_time bigint;
DECLARE @name varchar(100);

OPEN slow_query_cursor;

FETCH NEXT
FROM slow_query_cursor
INTO @wait_type,@wait_time,@status,@session_id,@cpu_time,@start_time,@total_elapsed_time,@name;

WHILE @@FETCH_STATUS = 0
begin

set   @outputinfo =  @outputinfo + ':' + @name +'/' + @start_time + '/'+ cast(@session_id as varchar) + '/'+ cast(@total_elapsed_time as varchar) + '/' + cast(@cpu_time as varchar) + '/' + @status + '/' ;
set   @outputinfo =  @outputinfo + cast(@wait_time as varchar) +'/' + @wait_type;

   FETCH NEXT
   FROM slow_query_cursor
   INTO @wait_type,@wait_time,@status,@session_id,@cpu_time,@start_time,@total_elapsed_time,@name;
end

   -- Close the cursor
CLOSE slow_query_cursor;
-- Deallocate the cursor
DEALLOCATE slow_query_cursor;

if len(@outputinfo) = 1
   print N'Y';
else
   print  @outputinfo;