CODEWITHSUNDEEP
sql-server
user222427
user222427

Reputation:

SQL While Loop 60 seconds?

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...

Upvotes: 16

Views: 48047

Answers (2)

sam yi
sam yi

Reputation: 4934

-- Your code here --

waitfor delay '00:00:01'
GO 60

Upvotes: 12

M.Ali
M.Ali

Reputation: 69524

To execute you script every second for one minute you could so something like this 

DECLARE @i INT = 1;

WHILE (@i <= 60)
 BEGIN
  WAITFOR DELAY '00:00:01'

       /*Your Script*/

 SET  @i = @i + 1;
END

I would Add another column in the temp table #DiagTable Session INT and add Value of @i in that column just to keep track of records inserted by each loop.

Upvotes: 33

Related Questions