I'm basically trying to caputure sql commands and their wait time for CPU, and I simply want to run a while loop for 60 seconds and insert all the data into a temp table. I'm not very good while cursors, or how to accomplish this one in sql.
BEGIN
CREATE TABLE #DiagTable
(
sessionID NVARCHAR (MAX),
dbname NVARCHAR (MAX),
starttime NVARCHAR (MAX),
cmd NVARCHAR (MAX),
stat NVARCHAR (MAX),
cputime NVARCHAR (MAX),
totalelapsedtime NVARCHAR (MAX),
reads NVARCHAR (MAX),
writes NVARCHAR (MAX),
[query] NVARCHAR (MAX)
)
DECLARE @id INT
DECLARE
x CURSOR FOR
SELECT 60
OPEN x
FETCH NEXT FROM x INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
WAITFOR DELAY '000:00:01'
-- begin loop here
INSERT INTO #DiagTable
SELECT a.session_id,
db_name (a.database_id) AS db_name,
a.start_time,
a.command,
a.status,
a.cpu_time,
a.total_elapsed_time,
a.reads,
a.writes,
b.text AS query
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text (a.sql_handle) b
WHERE a.session_id > 50 -- filter out background tasks
AND a.session_id @@spid -- filter out this query session
ORDER BY a.cpu_time DESC;
WAITFOR DELAY '00:00:01'
FETCH NEXT FROM x INTO @id
END
CLOSE x
DEALLOCATE x
-- end loop here
SELECT * FROM #DiagTable;
DROP TABLE #DiagTable;
END
not sure if select 60 is what i want...