May 22, 2012 at 3:13 am
Hi
Here's a question that has been bugging me for years. I've looked on various forums and the question has been asked numerous times but the answers are mostly waffle and don't provide any solid solutions.
I've been using SQL Server for over 10 years now, and I know for a fact that, whenever an overnight job fails and I need to get the information out there as quick as possible, it is quicker to run things via SSMS than re-run the job.
I've done this for many different processes on many different servers and it is always quicker in SSMS than SQL Server Agent.
The question is......why?
Maybe there is a configuration setting that controls what resources a job gets but I can't see this mentioned on any forums.
Anybody...?
Thanks
Col
May 22, 2012 at 3:54 am
There's nothing fundamental that would cause SP's to run slower via a SQL Agent job than via SSMS. They certainly don't run under a different priority, but there could be some factors that make it do something different (e.g. you're running with different SET options, so they're hitting different cached plans).
The only other thing I can think is that if you have a lot of steps and msdb's over-running with job history (e.g. you've changed the default job history retention) and the selects/updates/inserts to msdb are what's taking longer.
It's certainly not a "fact" of all SQL installations. I've run with quite a lot over the years and never experienced this...
May 22, 2012 at 4:12 am
In theory, the same stored proc called from any connection should run roughly the same speed, regardless of who called it. But... there are connection settings that will cause different execution plans to be created. If you're getting different performance between running things in SSMS (locally from your own PC presumably) and what is being run automatically on your server, I'd check the ANSI connection settings on both machines. See what's different, because I'll bet you've got something different there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2012 at 4:34 am
Hi Howard
Thanks for your reply.
I'm not sure how/why set options would make a difference (as it happens, the sp I'm looking at at the moment doesn't have any) since the proc would have the same options whether I run it or SQL Server Agent runs it? You may have to excuse my ignorance though....I'm an information guy not a DBA!
We've just migrated to a new server a week ago. A sp that took 50 minutes overnight on the old server now takes anywhere between 2 and 4 hours on the new one, the new one being a much higher specification.
I ran the sp myself through SSMS (on the NEW server) yesterday and it took 40 minutes. I doubt the job history is an issue as it's only been going a week and there are only a couple of jobs that run nightly. The messages out from the jobs are negligible.
I would be interested in what comparisons other people would get if they tried the same thing.
My specific problem at the moment it's why the new server is taking a lot longer than the old to do the same thing. Hence why I'm interested in any config settings that may affect SQL Server Agent.
May 22, 2012 at 4:46 am
The ANSI settings change how the optimizer creates execution plans. So one connection with "concat null yields null" turned on can create a different execution plan from one that has it turned off. That can be the cause right there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2012 at 5:42 am
Thanks, Grant
Your earlier message must have arrive while I was typing last time.
Thanks for the input. Makes sense and is certainly worth a look.
I'm checking the settings from SSMS (for my connection) using......
DECLARE @options INT
SELECT @options = @@OPTIONS
PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'
How do I check the connection settings for the 'SQL Server Agent'?
May 22, 2012 at 5:52 am
Just put that code above into a job step and run it. I think you can configure the job to save its output to a file in case there is too much information returned to be displayed in the job history.
John
May 22, 2012 at 6:17 am
Ditto. Or, it might be accessed by looking at the default ANSI settings for the server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2012 at 6:31 am
Ok, thanks guys.
On my connection I get '5496', whereas in the job it is '5176.
The diff being that my connection has ARITHABORT and QUOTEDIDENTIFIER switched on.
I'll try and change these on the server. Clues on how to would be appreciated.
Thanks
Colin
May 22, 2012 at 6:39 am
You'll want to test before you make any changes that affect all operations on the server. You could end up breaking something else. It may be safer just to set those two options in your job step.
Better still, compare execution plans of the query with and without the settings, and try to understand why there is a difference in performance. You may end up being able to realise further performance improvements with this approach.
John
May 22, 2012 at 6:53 am
colin.counsell (5/22/2012)
Ok, thanks guys.On my connection I get '5496', whereas in the job it is '5176.
The diff being that my connection has ARITHABORT and QUOTEDIDENTIFIER switched on.
I'll try and change these on the server. Clues on how to would be appreciated.
Thanks
Colin
ARITHABORT may effect performance. Make sure you have it ON!
May 22, 2012 at 6:58 am
Thanks
I've added the 2 lines to the sp, so will see what happens tomorrow, though I have checked the old server and got the same results as on the new server.
May 22, 2012 at 7:02 am
Thanks
I've added the 2 lines to the sp, so will see what happens tomorrow, though I have checked the old server and got the same results as on the new server.
May 23, 2012 at 2:31 am
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
May 23, 2012 at 5:00 am
That's very abnormal. Were you able to capture the execution plans for the queries so we can try to compare them to each other? This is not something I'm used to seeing unless there's some sort of configuration issue. Ummm.... Let's see, did you monitor for contention? Blocking, resource contention, that sort of thing? How about the SQL Agent executable. Was it set to run at a lower priority level? Just guessing without being there. This isn't normal on any system I've managed except in circumstance I've already outlined.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply