January 30, 2014 at 4:07 am
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
January 30, 2014 at 12:50 pm
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.
January 30, 2014 at 1:47 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply