sql008 to monitor batch job performance

Hi,
Can someone tell me how do I monitor the performance of this sql jobs with script below? basically I would like to know how long it takes to complete the batch at 1000 ?  I maybe will increase it until we satisfy with the performance

DECLARE @i INT
WHILE 1 = 1 BEGIN
     BEGIN TRANSACTION
      DELETE TOP(1000)
      FROM DB_Table  where timestamp < getdate() -185

      SET @i = @@ROWCOUNT
      commit transaction
      IF @i = 0 BREAK
      --PRINT '# of Records deleted in batch ---- ' + CAST(@i AS VARCHAR(10))
END
GO
motioneyeAsked:
Who is Participating?
 
AnujSQL Server DBACommented:
The performance really depends on the tables and index structure, because it needs to update index structure too.

if you need to know the history details of the job then check job history by right clicking the job and go to view history, this has the details regarding duration, last run status etc. You can also get the same details from msdb too.

If you need more details like cpu time, i/o usage, locks and blocks then its recommended to use SQL Server profiler. Start SQL Server profiler, then run the job and this will capture the statement level details of the job. You can customize the Trace file to get more details.



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.