Why to stored procedures run slower from SQL Server Agent than through SSMS?

• colin.counsell

  Right there with Babe

  Points: 764

  More actions

  May 25, 2012 at 2:23 am

  #1492984

  Hi

  I got tech. support involved in the end to look at why the job was taking so long to run on the new server.

  It turns out that there was a very high read latency, that was caused by the 'Delayed Ack' being enabled. They disabled this yesterday afternoon and the job ran very quickly this morning.

  I'm still not sure why sp's run quicker (for me!) in SSMS than in a job, but I'm happy for now 🙂

  Thanks

  Colin

• AnthonyR

  SSC-Addicted

  Points: 447

  More actions

  July 15, 2015 at 8:11 pm

  #1812835

  colin.counsell (5/23/2012)

  ---

  Hi

  I added code to the sp last night set ARITHABORT and QUOTED_IDENTIFIER.

  It has just finished after 3 hours 10 mins!

  It ran in 50 mins on the old server and ran in 40 mins yesterday on the new server ecxecuting it from SSMS.

  We reorganised all the indexes yesterday before anyone throws that idea in.

  Anbody any other ideas?

  Thanks

  I'm sorry for necro'ing an old thread, but...

  You should be aware that ARITHABORT is a run time option and QUOTED_IDENTIFIER is a parse time option. I draw your attention to the SET (Transact-SQL) page:

  Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. If used inside a stored procedure, any SET setting is ignored.

  This means:

  1. Your SQL Agent job step's SQL should have SET ARITHABORT ON before EXEC dbo.yourProcName

  2. Your stored procedure should have SET QUOTED_IDENTIFIER ON before CREATE PROC dbo.yourProcName...

  Hope this helps,

  Ant.

  See also:

  SET ARITHABORT (Transact-SQL)

  SET QUOTED_IDENTIFIER (Transact-SQL)