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
Best Answer
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 hereYou 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.