Question

You manage a SQL Server 2022 instance running on an Azure VM. Users report intermittent slowdowns, but there is no historical performance data to compare against. Before making tuning changes, you want to continuously collect OS-level and SQL-specific counters (Processor % Processor Time, SQL Server:Buffer Manager Page life expectancy, SQL Server:SQL Statistics Batch Requests/sec) over several weeks to characterize normal workload patterns. Which approach best establishes this baseline with minimal custom scripting?

Accepted Answer

Configure a Windows Performance Monitor (PerfMon) Data Collector Set on the VM that logs the required OS and SQL Server object counters to a file on a schedule.

Answer

Enable Query Store on each user database and rely on its runtime statistics to reconstruct CPU and memory counters.

Answer

Create an Extended Events session that captures the sql_batch_completed event and query it for counter values.

Answer

Run sp_who2 periodically and save the output to a table for later trend analysis.