long running query

• 
  This is an argument we've had internally as well. I happen to think we should report them as I would like to know why we are causing these. Not reporting a long running query that we are running would be counter productive to what we're trying to do which is to give an accurate report of what is happening on the sql server.
  
  To that end could you please post the details of the query that is taking a long time please? Then we can take a look at why it's slow
  
  Thanks

  Nigel Morse September 02, 2008 07:00

  - 0 +
• 
  I guess you are correct. So the tool should be executing short running queries to gather its info as opposed to connecting up and sit there as an open process continually running?
  
  
  Here is what the tool was executing:
  
  CREATE PROCEDURE sp_help_jobhistory_full
  @job_id UNIQUEIDENTIFIER,
  @job_name sysname,
  @step_id INT,
  @sql_message_id INT,
  @sql_severity INT,
  @start_run_date INT,
  @end_run_date INT,
  @start_run_time INT,
  @end_run_time INT,
  @minimum_run_duration INT,
  @run_status INT,
  @minimum_retries INT,
  @oldest_first INT,
  @server sysname,
  @mode VARCHAR(7),
  @order_by INT,
  @distributed_job_history BIT
  AS
  IF(@distributed_job_history = 1)
  SELECT null as instance_id,
  sj.job_id,
  job_name = sj.name,
  null as step_id,
  null as step_name,
  null as sql_message_id,
  null as sql_severity,
  sjh.last_outcome_message as message,
  sjh.last_run_outcome as run_status,
  sjh.last_run_date as run_date,
  sjh.last_run_time as run_time,
  sjh.last_run_duration as run_duration,
  null as operator_emailed,
  null as operator_netsentname,
  null as operator_paged,
  null as retries_attempted,
  sts.server_name as server
  FROM msdb.dbo.sysjobservers sjh
  JOIN msdb.dbo.systargetservers sts ON (sts.server_id = sjh.server_id)
  JOIN msdb.dbo.sysjobs_view sj ON(sj.job_id = sjh.job_id)
  WHERE
  (@job_id = sjh.job_id)
  AND ((@start_run_date IS NULL) OR (sjh.last_run_date >= @start_run_date))
  AND ((@end_run_date IS NULL) OR (sjh.last_run_date <= @end_run_date))
  AND ((@start_run_time IS NULL) OR (sjh.last_run_time >= @start_run_time))
  AND ((@minimum_run_duration IS NULL) OR (sjh.last_run_duration >= @minimum_run_duration))
  AND ((@run_status IS NULL) OR (@run_status = sjh.last_run_outcome))
  AND ((@server IS NULL) OR (sts.server_name = @server))
  ELSE
  SELECT sjh.instance_id, -- This is included just for ordering purposes
  sj.job_id,
  job_name = sj.name,
  sjh.step_id,
  sjh.step_name,
  sjh.sql_message_id,
  sjh.sql_severity,
  sjh.message,
  sjh.run_status,
  sjh.run_date,
  sjh.run_time,
  sjh.run_duration,
  operator_emailed = so1.name,
  operator_netsent = so2.name,
  operator_paged = so3.name,
  sjh.retries_attempted,
  sjh.server
  FROM msdb.dbo.sysjobhistory sjh
  LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjh.operator_id_emailed = so1.id)
  LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjh.operator_id_netsent = so2.id)
  LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjh.operator_id_paged = so3.id),
  msdb.dbo.sysjobs_view sj
  WHERE (sj.job_id = sjh.job_id)
  AND ((@job_id IS NULL) OR (@job_id = sjh.job_id))
  AND ((@step_id IS NULL) OR (@step_id = sjh.step_id))
  AND ((@sql_message_id IS NULL) OR (@sql_message_id = sjh.sql_message_id))
  AND ((@sql_severity IS NULL) OR (@sql_severity = sjh.sql_severity))
  AND ((@start_run_date IS NULL) OR (sjh.run_date >= @start_run_date))
  AND ((@end_run_date IS NULL) OR (sjh.run_date <= @end_run_date))
  AND ((@start_run_time IS NULL) OR (sjh.run_time >= @start_run_time))
  AND ((@end_run_time IS NULL) OR (sjh.run_time <= @end_run_time))
  AND ((@minimum_run_duration IS NULL) OR (sjh.run_duration >= @minimum_run_duration))
  AND ((@run_status IS NULL) OR (@run_status = sjh.run_status))
  AND ((@minimum_retries IS NULL) OR (sjh.retries_attempted >= @minimum_retries))
  AND ((@server IS NULL) OR (sjh.server = @server))
  ORDER BY (sjh.instance_id * @order_by)

  laker_42 September 02, 2008 07:05

  - 0 +
• 
  Thanks. I asume that must be from when we gather all the job information from the server. I'm suprised that's taking so long. Can I ask how many jobs do you have on this server please?

  Nigel Morse September 02, 2008 07:12

  - 0 +
• 
  We have around 30 jobs on this server...

  laker_42 September 02, 2008 07:14

  - 0 +
• 
  I misread. It's trying to get the job history. So the query we run is
  

  msdb..sp_help_jobhistory @mode='FULL'
  

  
  Maybe you could run that via SSMS and see how long it takes (and how many rows it returns)
  
  Thanks[/code]

  Nigel Morse September 02, 2008 07:27

  - 0 +
• 
  Nigel, this is a little embarrasing but it returned 135548 rows in about 2 minutes. THere was almost 6 months worth of job history kept! That has been slimmed down so I guess this shouldn't show up as a long running query again. Thanks for following up on this.
  
  John

  laker_42 September 02, 2008 07:33

  - 0 +
• 
  I guess i could argue it's a little embarassing that I even attempt to get 6 months worth of history! But I guess for a monthly job I would still want to get that baseline. SQL agent seems to limit it to 1000 rows, and upto 100 per job by default but obviously people can change that.
  
  We'll look into what we can do to allieviate this, if not for the release then for a futrue version.
  
  Thanks for your help

  Nigel Morse September 02, 2008 08:03

  - 0 +
• 
  Just a quick enhancement request to the details section of this alert. It would be extremely helpful to know the database context of the query being executed.
  
  Thanks!

  nuberfin September 04, 2008 12:36

  - 0 +

Add comment

Please sign in to leave a comment.