stored proc taking more time when executed thru a job

• Dhruva_51

  SSChampion

  Points: 10521

  More actions

  May 22, 2013 at 5:47 pm

  #394833

  I have a stored proc which collects the primary key values into temp table and delletes the rows from the main table by joining the rows of temp and main table on primary key. This stored proc is taking 2 hrs to run when setup as a job, but taking less time when ran through ssms. We have almost same settings. I tried to change the run as user but no change. Any suggestions??

• Andrew G

  SSChampion

  Points: 12809

  More actions

  May 22, 2013 at 10:21 pm

  #1617675

  Post the actual execution plan for both the job and the normal run

  SELECT TOP 1000 qs.*, qt.text, qp.query_plan

  FROM sys.dm_exec_query_stats qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

  INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle

  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

  WHERE cp.plan_handle=qs.plan_handle

  AND (ObjType = 'Adhoc' OR ObjType = 'Prepared')

  and text like '%STOREDPROCNAME%'

• SQL Show

  SSCertifiable

  Points: 6143

  More actions

  May 23, 2013 at 3:32 am

  #1617771

  I faced same situation some years back. JOb would take 2 hours where as ssms run it just 20 minutes.

  I used sqlcmd, it was fine, took just 20 minutes.

  I believe its because of memory constraint of sql server. Sqlcmd will take memory out side of sql server(i do not mean memtoleave) 🙂