Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Agent Job Expand / Collapse
Author
Message
Posted Thursday, January 30, 2014 4:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:55 AM
Points: 40, Visits: 389
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
Post #1536240
Posted Thursday, January 30, 2014 12:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:01 PM
Points: 199, Visits: 600
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.
Post #1536519
Posted Thursday, January 30, 2014 1:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:55 AM
Points: 40, Visits: 389
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.
Post #1536542
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse