ChrisM@Work (10/6/2015)
tindog (10/6/2015)
ChrisM@Work (10/6/2015)
Perhaps the data which the SSIS package interacts with is not in cache the first time it's run.I'm surprised it takes so long for so few rows. What exactly does it do? Are there simple queries involved anywhere in it's operation?
That is possible, thankyou.
In theory it should be a relatively straightforward collection of upsert/delete tasks, unfortunately it's full of poorly written and inefficient procedures (I believe the individual who created the process wasn't too familiar with SQL and was working under pressure), if I was able to share it with you I don't think you'd be surprised at it taking so long!
I guess you've just identified your own coalface. If you're lucky you might find a quick win or two. A query I was thrown this morning which took nearly 15 minutes to run, executes in 1.5 seconds with the index hints removed...
Possibly. On the test server I've managed to get the process down from 1.5mins to 15 seconds so far, mostly from changing the upsert from slowly changing dimension to use a T-SQL MERGE. However the issue in the original post has never happened on the test server (although the usage on this server is significantly less so that could explain why?)
That's pretty good!