SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Agent Job


SQL Agent Job

Author
Message
peter.cox
peter.cox
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 540
Hi

Got an unusual one here. We've a SQL Agent overnight process; takes about 7-8 hours a night, processes about 350GB of data. Moves the data from [more than ]one DB to another, then constructs the SSAS cube. I'm relatively new in the job (although been a DBA 10 odd years) and one of my tasks is to reduce it in time.

Anyway the process starts with the first step; simple truncate, insert via select. For the last 3 months (that i've been here) it's taken about 3.5 minutes for the step. Last night it took 7 hours 15 minutes. Never seen such a large increase in time.

This has obviously delayed us!

I've no idea why - there appears to have no locks on the database/tables involved. The server - according to Idera - was running fine. Underlying data hasn't really changed. Nothing out of the ordinary at all. As it's SQL 2012 I've checked system_health etc etc. Nothing really.

Any ideas? Anywhere I should look to work out what the problem is?

One thing i've noticed here is that server reboots are infrequent; it's been up 3 months now, so is it a simple cash of turn it off and on again?

ta

pete
Daryl AZ
Daryl AZ
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 691
I have never had a Truncate issue so I would start at the select into.

a) How much space is available for the database? Was the delay caused by a space constraint?
b) What type of indexes are on the target table? Did something with the indexes change?
c) sometime breaking a large import into smaller chunks can do wonders.

Just some thoughts good luck.
peter.cox
peter.cox
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 540
No space constraints. DB about 50gb free, and the log about 40gb. I monitor the log size every ten minutes and there was no issue. No issue on tempdb either. Indexes are dropped and recreated again at the end of the SP.

Breaking it up can be good, but to go from 3min to 7hrs, and yet remain the same size implies it's not really an issue. Especially as it works every other night okay.

In fact I ran the same SP into another table to check. Took c3 minutes as normal.

One thing I've discovered later is another SQL box on the same Disc spindles seemed to have massive as yet unexplained RAM issues at a similar time. Nothing was flagged on the original box, but it's a coincidence. I've got people looking, but could just be a coincidence.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search