• Couple things...

    Nolock is not a good thing to add everywhere. Are you aware of the problems it may cause and do you feel that the risks of bad data are acceptable?

    What's SQL's max memory set to? From the sound of things, it should perhaps be lowered.

    Are the log files also on M? If so, why?

    What's the RAID level of the SAN drives and are they dedicated?

    Is it 64 bit SQL? Enterprise edition? Standard edition? If enterprise, have you considered partitioned tables, rather than partitioned views?

    As for advice, profile the ETL process, find the slowest portion and then look at optimising that. Looking at the entire thing in one go is not a good idea. You need to identify the pain points and fix those, then find the next most problematic point and fix that, etc, etc

    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