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;