SQl Script runs slow running from SQL Agent

Question

SQl Script runs slow running from SQL Agent

PJ_SQL

SSCertifiable

Points: 6699
More actions
December 5, 2016 at 3:09 pm

#332597

I have a job which runs SQL Script along with function which has started to run slow.
But when run the same query in the SSMS, it runs fine.
What could be the reason?
December 8, 2016 at 11:11 pm

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 1

PJ_SQL (12/5/2016)
I have a job which runs SQL Script along with function which has started to run slow.
But when run the same query in the SSMS, it runs fine.
What could be the reason?

Could be any of a thousand reasons. Some of them could be quite simple. For example, does the job run at a different time of day than when you run it in SSMS?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MadAdmin SSChampion Points: 11261 More actions · Answer 2

SET options.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

MadAdmin (12/6/2016)
SET options.

I agree that this may be a good place to start. A common problem comparing a query running in Management Studio vs the same query run from a .Net connection or SQL Server Agent connection, is the arithabort setting.

SELECT client_interface_name, login_name, quoted_identifier, arithabort, ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @@SPID

for Management Studio arithabort defaults to 1, but other connections default to 0. Microsoft says you should always use the ON or 1 setting:

https://msdn.microsoft.com/en-us/library/ms190306(v=sql.110).aspx

You can change the default connection settings at the instance level, (try it in a dev environment first of course)

in Management Studio Object Explorer, right click on the instance and select Properties. On the Connections page there is a Default connection options area, scroll down to arithmetic abort and check this checkbox. All future user connections will use that setting now unless they explicitly set it off.