Query Statistics History Report Duration and Execution Time taken RRS feed

  • Question

  • Hi everyone

    I have about 4 jobs calling the same stored procedure but each time it runs it executes different Id's. When Job1 is running it may takes 45 minutes for job to complete, Job2 can take 1 hour 20 minutes to run. They basically overlap with each other but touch different Ids that need to be executed.

    Questions 1

    The stored procedure creates a bunch of #temp tables and I believe each time a job runs every time it goes through the same code and create seperate #temp tables. Like Job1 runs it creates #temp1_1, Job2 runs and creates #temp_2, Job3 runs and creates #temp_3 in memory.

    Can you please confirm if my understanding is right.

    Question 2

    When I check the Query statistics History report I see server queries mentioned in the stored procedure running for hours, but the jobs completes in less than an hour. I am saying hours thinking that time mentioned there is in seconds. First of all is the total duration, execution/minutes is that in Milli seconds or Seconds?

    So how is it possible that query shows 6 hours running but none of the job runs for 6 hours straight.

    Thanks much


    Thursday, June 13, 2013 4:28 PM

All replies

  • Hi ,

      For #1 , Your understanding is correct. Different #temps will be created for each spid i.e the job

      For #2 , in SSRS Execution log table all the durations are in milli seconds.

    Best Regards Sorna

    Friday, June 14, 2013 6:49 AM
  • Thanks much Sorna

    For #2 this is all done in SSMS not SSRS. In the image above, is execution/min and total duration in milli seconds or seconds?

    Thanks again


    Friday, June 14, 2013 7:38 AM