Perfmon Resource Utilisation for PROD SQL Server
Perfmon is pretty light weight when it comes to data collection. Collect the data in blg files and set a sensible collection frequency. Jonathan has written about Essential PerfMon counters for DBA that will give you a good start.

For automation part you can use logman and load data into sql server or as shown here

You can even Setup Perfmon with PowerShell and Logman. 

Alternatively, you can use PAL or Get-PerfMonSummaryStats.ps1 - Powershell script from MSFT

sys.dm_os_performance_counters exposes perfmon counters in raw form.