Scenario
Earlier executions of a package used to complete in less time. However, it is taking more time now. There are no specific errors or retries.
Review and compare execution logs
Right-click on a package > View Execution History Log
Review Gantt charts for fast and slow execution to compare objects, steps and timings. Check to see if overall number of objects or steps have increased.
View Tools menu > Instance Execution Overview Log. Review various measures and drill-down to various objects. Two examples are shown below:
Right-Click on data warehouse or a table > Execution Overview Log
When you turn on logging (execution package settings > Log Execution time and Log Row Count check boxes), it will show how fast a table ran and how many rows were transferred.
Compare slow and fast executions, looking at timings and number of rows.
Environment, machine and network factors
Review Windows Event Viewer logs for errors or warnings related to ODX Service and TimeXtender Scheduler Service.
When reading data from another machine, look for these factors when applicable:
- network latency
- CPU/ memory on other machine
- memory allocation for SQL server (for large executions)
- reboots, server maintenance, nightly patching (if any)
- retries on the package
- intermittent connectivity issues to Azure server
Check for blocking
- Use a Profiler tool to trace SQL queries in currently executing package.
- Number of Threads: lower the number of concurrent execution threads on ODX data source
- Redesign: split data sources and/or execution packages to avoid interdependency of data sources
- Implement incremental load for loading big tables.
- If your ODX storage is on a SQL database, consider moving it to Azure Data Lake, even if the data warehouses are on-premises.
- Tune SQL Server performance - adjust relevant parameters which affect concurrency and memory consumption to give extra room for non-SQL processes from TimeXtender and ODX services.
Periodic Maintenance and Cleanup
- Right click on the Instance Node > Advanced > SQL Database Cleanup Tool, in order to remove objects no longer used in the instance.
Warning: Ensure that the objects you select to cleanup are not being used in other instances, if they are sharing the same storage.
- Also review "Proactive maintenance, cleanup & backup" section in this article Self-Service Troubleshooting in TimeXtender
