Perfmon Resource Utilisation for PROD SQL Server

Question

I want to create a new Data collector set to find out my Memory utilization in my PROD SQL Server for at least 2-3 weeks.

Can anyone let me know if this takes too much resource utilization which in turn reduces Performance.

Below are the performance counters I am planning to create as adviced :

Memory – Available M Bytes

SQL Server: Buffer Manager – Page Life Expectancy (Higher is good)

SQL Server: Memory Manager – Memory Grants Pending (Lower is good)

SQL Server: Buffer Manager, Buffer Cache Hit Ratio (higher is better)

SQL Server: Memory Manager – Target Server Memory

SQL Server: Memory Manager – Total Server Memory

SQL Server: SQL Statistics – Batch Requests/sec

SQL Server: SQL Statistics – Compilations/sec

Answer 1

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.