asked
ETL : Slowness
Hi Team, I am working for Indian NBFC in BIU team. I am responsible for EDW management and reporting solutions. Currently, one of leading Vendor is supporting us for EDW activities (ETL - SSIS, SSAS, SSRS). From last few months we are facing ETL performance issue where ETL timing has increased drastically. We have done initial RCA , and found its due to the sudden data volume growth. So we have enhanced our server infra by adding drive space, RAM and Core. In spite of increasing server capacity we are still facing issue with the ETL timing. Also we are not able to address Server bandwidth, vendor’s point is we are not getting bandwidth to fetch data (read / write) but as per IT infra team we have good bandwidth (256 MB) but our SQL services not able to consume the same. I need help to address two major issues here; how to crunch down ETL timing further What are the technical things I need to check How we will get constant bandwidth for ETL process Thanks, Pravin Vithu Kadam India +91-9987037828
"Sudden data volume growth" is a pretty ambiguous finding for an RCA. Have you reviewed the reports build into the Integration Services Catalogue? Those show duration at the individual task level within each package - at this point, you should have a pretty good idea of which tasks in which packages have had the greatest performance reduction -
https://docs.microsoft.com/en-us/sql/integration-services/troubleshooting/troubleshooting-reports-for-package-execution Other than network, which we random internet strangers don't have enough information to firmly attribute as the cause, certain query patterns that work well with smaller data sets can really struggle with larger data sets, regardless of hardware upgrades. Also, an indexing plan that worked for your smaller environment may need to be adjusted as the data volume changes. If your problem is based inside the SQL instance or its underlying operating system/hardware configuration, the SQL instance can help to identify the specific resources it is waiting on -
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ Hopefully, this gets you pointed in the right direction
21 People are following this question.
