I have a SQL job that runs an SSIS package and it basically pulls records from staging table and dumps it into the master table. It used to take around 8 hrs. to run but in these last 10 days or so the run duration has increased and I would like to know if there is a way I can find out where the job is spending more time and what it is waiting on either running a profiler trace or running a tsql script inside a job to capture anything useful. It runs every day except Sat at 11p est.
6
-
Note that you'll be able to tell where the job is spending its time today - but unless you already have such information from (say) two weeks ago, you won't be able to tell where there's a difference in how its spending its time. Also, don't neglect standard troubleshooting: any new (or updated) jobs start running two weeks ago or so? Any change in the amount of data to be processed?– RDFozzJul 20, 2017 at 17:42
-
I totally get that and there are some days when the data load is not that heavy and it completes quickly whereas the same is not true for other days and it takes longer to complete. However, I do need a way to figure out where exactly in the process it spends a lot of time waiting on that the run duration increases so drastically.– FeivelJul 20, 2017 at 19:06
-
1Take a look at this question and answer. While not really a direct duplicate of your question, I think it provides an answer for you - using the logging capabilities of SSIS. Since you're using 2008, you'd be doing it "the hard way", but you should be able to track when you enter and leave certain states for each task in your package.– RDFozzJul 20, 2017 at 19:15
-
8 hours is a longtime for any integration or etl job. How many rows/gb are we talking about. How much longer is it now taking?– Sir Swears-a-lotJul 21, 2017 at 10:25
-
Yup...8 hrs. is long by any means. Currently, it is close to 100,000 records per hour.– FeivelJul 21, 2017 at 19:41
|
Show 1 more comment
1 Answer
Though not really enough info here to give a full answer these steps are how I would approach trouble shooting this...
check the execution logs for the package. Take note of the timings on steps. It should he obvious which takes longest.
execution plans are helpful once you know which to look at. If it's stored procs or a fairly static query the should be in the query plan.
change the package to manually log individual steps and information that you can clearly analyse later it will help identify the slow running points. Add row counts to see if you can quantify if a per row metric to calculate run time. Even run a query to log current connections and transactions to see if it's clashing with other tasks.
This being said 8 hour sync between staging and live is huge unless we're talking about 100s of millions of rows. The run time could suggest missing indexes on staging but the execution plans should tell you that.
