https://sqlmaster.mssqlplanner.com

Tag: PerfMon

  • SQL Server PerfMon Counters That Actually Matter, Plus the Complete DMV Toolkit

    SQL Server PerfMon Counters That Actually Matter, Plus the Complete DMV Toolkit

    Windows Performance Monitor exposes hundreds of SQL Server counters. Most are noise. Here are the handful worth an actual dashboard tile, plus every DMV this course has used, in one place.

    Four tools, four different questions(reach for the right one)WHAT’SWRONG?(start here)PerfMonIs there a TRENDover time?DMVsWhat’s the stateRIGHT NOW?Query StoreHow has THIS QUERYevolved over time?ExtendedEventsCapture a SPECIFICevent as it happensCommon mistake: treating Batch Requests/sec ashaving a ‘good’ absolute number. There isn’t one —it’s workload-specific. Only the TREND vs YOUR OWNbaseline matters, not some blog’s benchmark. 📌

    The PerfMon Counters Worth Watching

    Counter What a bad value means
    Page Life Expectancy Low = buffer pool pressure, pages evicted quickly (Module 1)
    Batch Requests/sec Your baseline throughput metric — track trend, not absolute value
    Compilations/sec vs Batch Requests/sec High ratio = excessive recompiling, often ad-hoc query bloat (Module 1)
    Lock Waits/sec Rising trend = growing blocking problem (Module 5)
    Full Scans/sec Rising trend alongside stable workload = missing/degraded indexes (Module 2)

    The Complete DMV Reference From This Course

    sys.dm_os_buffer_descriptorsBuffer pool contents (M1) sys.dm_exec_cached_plansPlan cache contents (M1) sys.dm_db_missing_index_detailsIndex candidates (M2) sys.dm_db_index_usage_statsIndex read/write balance (M2) sys.dm_exec_query_statsHistorical query cost (M3/M4) sys.dm_os_waiting_tasksLive blocking/latch state (M5)

    The Right Habit: One Dashboard, Not Twenty Tools

    Query Store, Extended Events, DMVs, and PerfMon aren’t competing tools — they answer different question shapes. PerfMon: is there a trend problem over time? DMVs: what’s the current/historical state right now? Extended Events: capture specific events as they happen. Query Store: how has this specific query’s performance evolved? Combine them per the Evidence-First workflow rather than reaching for just one out of habit.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.