We have a Data warehouse that are hosted in a dedicated sql server machine (32Gb memory and 2 processors Intel Xeon E5520 Quad) and we have a ETL process developed using stored procedures. We also use the SSIS to create the flow and call the sp. My tables haven't physical relationship defined (no foreign keys), but all my tables have keys created. I have more than 300 ETL's and now a days it is taking almost seven hours to be completed. I call the procedures in a sequential way. (the next starts when the last finishes). Some of the ETL's must be called in a sequential way because the dependence, but there some, that i could put to process in paralel. The ETL's that load my facts tables could be processed in parallel, although, sometimes they use the same source table. In my tests it looks that when put it to process in paralel it takes more time to be complete. In short my doubt is: is there a way to speed up my load process, kind.... using some hints, changing the isolation level, using maxdop, etc, etc... I realy need improve performance of the process!
Rafael Melo - BR