• This type of performance tuning and analytics is what I (and a few others here on the forums) do for a living. There are a WIDE range of potential issues that can be causing problems - not just the hardware. But clearly that hardware is SIGNIFICANTLY underpowered for the size of database being processed. Sounds like you may even be doing the ETL from the same server.

    A few of the important things to look at are perf mon avg disk sec/read and avg disk sec/write as well as CPU utilization. From within SQL Server you need to do a file IO stall and wait stats analysis. Queries should be tuned, index fragmentation evaluated, statistics freshness checked (including on the source system), the ETL methodology itself reviewed/refactored, etc, etc, etc, etc.

    I HIGHLY recommend getting a professional on board to help you with this project. That will be a win-win. Client gets a MUCH better set of work in a shorter period of time and you get some mentoring on how to tune/monitor/maintain a data warehouse.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service