Datawarehouse Flat files Bulk data load Slow Loading

Question

Datawarehouse Flat files Bulk data load Slow Loading

Vishal_MSBI

Valued Member

Points: 61
More actions
November 28, 2013 at 11:21 pm

#285630

Hi Friends,
We have to load 15-20 GB of file in Data warehouse daily. The current configuration of servers are:
We were able to load 15 GB of file in 20 minutes in INT server. While we started loading data in Production Environment, it taking 1 hour to load whereas the production environment has better RAM and CPUs.
INT.
Datawarehouse Server:
16 CPU, 64 GB RAM, SQL Server 2012 Enterprise Edition.
ETL Server
4 CPU, 32 GB RAM,, SQL Server 2012 Enterprise Edition.
Production:
Datawarehouse Server:
16 CPU, 128 GB RAM, SQL Server 2012 Enterprise Edition.
ETL Server
16 CPU, 64 GB RAM,, SQL Server 2012 Enterprise Edition.
Do anyone can help me find the problem ?
Thanks in advance
Vishal;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 1

Maybe the production server has more contention for resources?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Vishal_MSBI Valued Member Points: 61 More actions · Answer 2

Hi Koen,

Thanks for the reply.

I am not a DBA. Can you help us to troubleshoot the issue ?

Thanks

Vishal;

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

when you say "Load data in production server"

its means you process the file or you bring the data from the Source database.

If its source database check if the what kind of response you are getting. because there are cases in which you get slow response from the source server due to network traffic, if its a remote location then might be your broadband connection speed.

there are many cases you need to check which side is causing the bottleneck.

hope it helps.

Vishal_MSBI Valued Member Points: 61 More actions · Answer 4

Hi SSC,

This is the flat file which is getting loaded. This is the cluster environment. ETL and DW are in separate nodes.

Let me know if you need more information.

Vishal;