May 10, 2011 at 7:59 pm
I just realized my proc with this option "set arithabort off" takes forever to complete. Mentioned below is the code which is an issue when used with the option "set arithabort off"
INSERT INTO [ServerName].[MyDB].[dbo].[TCB]
([RId]
,[ErID]
,[PatType]
,[TalSite]
,[TeChId]
,[TRId]
,[TechEncounterID]
,[TDS]
,[TRC]
,[TCI]
,[TUR]
,[TNU]
,[TTC]
,[TCP]
,[TOP]
,[TCP]
,[TFD]
,[TFL]
,[TtType]
,[TADate])
OUTPUT INSERTED.[RId] INTO @RegIds
ANy idea?
May 11, 2011 at 11:00 am
?
May 11, 2011 at 11:08 am
No, but maybe I can check both execution plans to see if I see something.
May 11, 2011 at 11:14 am
Your code isn't complete. There are no calculations taking place in what you posted. Arithabort causes transactions/batches to fail if they violate data type rules on arithmetic operations. E.g.: If a number is too large for the data type to hold, or if you end up dividing by zero. Turning it off makes these calculations return NULL instead of aborting the transaction/batch.
Without seeing the rest of the code, at the very least, I can't even begin to help on this one. There's nothing posted that is in any way affected by arithabort.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 11, 2011 at 2:44 pm
That option is one of the settings that is used to generate an execution plan. So, if you have it on for one connection and off for a different connection - you will get two different execution plans.
Because this setting affects how queries are processed - having this turned off could cause a bad execution plan to be generated.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 12, 2011 at 8:51 am
Thanks everyone.I am little lost now. First i was able to run the proc from management studio and not through job. I was NOT able to run when i set the option "SET ARTHIABORT OFF", this made me confirmed that that is the issue. Next day i ran the same proc from management studio( closed and opened it again so set options can be the way it is) it is running rediculously slow. Now i am leaning more towards parameter sniffing? What do you say?
May 13, 2011 at 2:50 pm
Have you compared the execution plan between the two sessions?
ARITHABORT is one of those settings that can affect index usage, for example if it is OFF, then SQL Server may ignore an index on a view. That's the kind of thing you want to look for in the execution plan.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply