August 25, 2009 at 1:57 pm
Hello,
I have a job which runs one proc and that proc does some imp/exp within the same sql instance. This job is in test environment and there is no change in the data at all. The proc makes use of local linked server.Mentioned below are my ques:
i) some times the job takes only 1 hr somtimes 2 hours..i checked there are no other jobs running during that time...rebuild index/update stats is done just before the job starts..doesnt really help
ii) i ran the proc from query window takesame time
would appreciate if anyone can suggest me or help me in finding why does the same job takes different times without any change in data and yeah the dev doesnt want to change the proc..thanks
August 25, 2009 at 2:01 pm
You mentioned a linked server. Network traffic might make a difference. Lots of things might. Have you tried tracking CPU, memory and I/O use during the job?
- 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
August 25, 2009 at 2:04 pm
also check for any blocking in the system when your job is running.... you mentioned that there is no other job running when you are running your job, but there could be application using the same data at that time ....
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
August 25, 2009 at 6:23 pm
With the linked server in play, that is where I would troubleshoot first. You said that the proc does the import and export from within the same Instance, and that this is a test server. If it is the same instance, why would you need to use a linked server? Have you tried running the proc without the linked server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2009 at 8:04 am
thanks everyone...the same job takes only 40 mins on their old servers..i compared the settings everything looks to be the same...i know ders no point in using the linked server withint the same instance..but the developer got the code from some other person and doesnt want to tweak..ill keep posting..
August 26, 2009 at 8:21 am
as you say that the code was the same on old server ... so i assume that also had a link server setup on the same server....
at this point i would start looking at disk I/O metrics in perfmon when the job is running...
Also is the transaction using the linked server being called like
BEGIN DISTRIBUTED TRAN
....
... code using linked server
COMMIT TRAN
or
BEGIN TRAN
.....
... code using linked server
COMMIT TRAN
or direct call to the linked server ??
these could lead you to check RPC, or DCOM settings on the box.
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply