Job execution

• bruce_reid

  SSCrazy

  Points: 2419

  More actions

  September 18, 2003 at 3:09 pm

  #82955

  Hi Everyone,

  I have few stored procedures running as a single job.The whole job was taking around 4 mins till 12 th of this month,But now its taking around 16 mins .There was not that much data growth in the tables.What should be the reason and how can i come out of this problem.Can anyone give me some idea to improve the execution time of the job.

  Thanks.

• jpipes

  SSChampion

  Points: 12230

  More actions

  September 18, 2003 at 3:40 pm

  #474947

  The job is not what is taking so long...it's the stored procedures that it runs. Go ahead and post the procedure and we'll take a look for mistakes or optimization points...

• mccork

  SSC-Insane

  Points: 22288

  More actions

  September 18, 2003 at 3:55 pm

  #474951

  Are the SPs separate job steps? If so, can you determine which of the steps has increased in time?

  Sounds like a different query plan is being chosen, based on auto updated statistics. You might need to examine each of the queries involved and see if table scans are happening.

  Any other jobs scheduled for the same time?

  To use SQL Profiler to see what resources the individual statements of the job are using:

  - Trace just the event TSQL - SQL:StmtCompleted

  (optionally trace all the Performance events)

  - Trace default columns, but check that CPU, Reads, Writes and Duration are selected

  - Filter on: Application name like 'SQLAgent%0xNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN%' (to get the value for 0xNNNN..., select convert(varbinary, job_id) from msdb..sysjobs for the particular job)

  Optionally also filter LoginName on the login that SQL Agent runs as (usually sa or domain\ntusername). This will minimise display of unrelated lines with NULL application names.

  Cheers,

  - Mark

  
  Cheers,
  - Mark