Post reply

What can i do to improve performance on this scenario?

  • August 27, 2012 at 12:50 pm

    #393032

    Hello all!

    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!

    Warm Regards,

    Rafael Melo - BR

  • August 27, 2012 at 12:55 pm

    #1530477

    There's no silver bullet or go-faster switch if that's what you're looking for (http://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/)

    Profile the data load, identify which parts are slow and work on optimising the bottlenecks. Repeat until it's running fast enough.

    This may give you an idea where to start

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 28, 2012 at 5:47 am

    #1530725

    Gail, Thank you for your quick answer and your amazing articles. They help me a lot.

    I know that tuning is about testing and testing. But as i'm new about processing queries in parallel into SQL Server i was wondering to know if you guys from this forum with your experience could advice me about something besides the index tuning.

    Anyway, your articles answered my main doubt....

    Thanks,

    Rafael Melo - BR

Viewing 3 posts - 1 through 2 (of 2 total)

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