x

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

more ▼

asked Sep 12 at 05:39 AM in Default

avatar image

pravinvithukadam
1

"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

Sep 13 at 02:18 PM KenJ
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1208
x308
x45
x28

asked: Sep 12 at 05:39 AM

Seen: 31 times

Last Updated: Sep 13 at 02:21 PM

Copyright 2017 Redgate Software. Privacy Policy