I administer a datawarehouse running on SQL Server 2016. This datawarehouse has most of its ETL processing done during a nightly load, except for a few SSIS packages that run throughout the day, then SSAS processing done afterward, so when I arrive in the morning, there's no active monitoring I can perform.
The processing of this nightly load fluctuates, sometimes more than expected, but with the current monitoring setup, there is none other than a sp_whoisactive loaded into a table every 30 minutes, it makes it difficult to try and tune this process. I can obviously look to the plan cache but with the nightly ETL taking about 6 hours, the plan cache does not hold all of the plans used for the night.
Has anyone come across any performance monitoring/logging tools, the cheaper the better, that have been useful when trying to investigate and tune nightly loads like this? I'm most interested in looking for information to help tune indexes or see when/where I have CPU or memory bottlenecks.