我基本上想捕获SQL命令及其CPU等待时间,并且我只想运行一个while循环60秒,并将所有数据插入临时表中。 我不太擅长游标,也不知道如何在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'
-- 在此处开始循环 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 -- 过滤掉后台任务 AND a.session_id <> @@spid -- 过滤掉当前查询会话 ORDER BY a.cpu_time DESC;
WAITFOR DELAY '00:00:01'
FETCH NEXT FROM x INTO @id END
CLOSE x
DEALLOCATE x
-- 在此处结束循环 SELECT * FROM #DiagTable;
DROP TABLE #DiagTable; END
不确定选择60是否是我想要的...