我基本上试图捕获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' -- 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
不确定选择60是否是我想要的......