Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What can i do to improve performance on this scenario? Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 12:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:33 PM
Points: 81, Visits: 866
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
Post #1350535
Posted Monday, August 27, 2012 12:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 42,803, Visits: 35,919
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 2008, MVP
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

Post #1350537
Posted Tuesday, August 28, 2012 5:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:33 PM
Points: 81, Visits: 866
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
Post #1350847
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse