https://sqlmaster.mssqlplanner.com

Tag: Extended Events

  • Building Reusable Extended Events Sessions in SQL Server for Ongoing Monitoring

    Building Reusable Extended Events Sessions in SQL Server for Ongoing Monitoring

    The one-off XEvents session from the 13-Hour Delete case study was diagnostic and temporary. A production monitoring session needs to run continuously with minimal overhead — different design goals entirely.

    Designing a session that survives(not just a diagnostic one-off)RAW ACTIVITYfires on EVERY querykeep signal, drop noiseFILTERWHERE duration > 5sBOUNDED TARGET50MB cap, 5 rollover filesREAD LATERfn_xe_file_target_read_file()ALLOW_SINGLE_EVENT_LOSSnever lets monitoring block your appSTARTUP_STATE = ONsurvives a SQL Server restartGotcha: MAX_DISPATCH_LATENCY = 5 SECONDS means anevent can sit buffered for up to 5s before hitting thetarget — this is NOT a real-time feed. Don’t build alertsthat assume instant visibility the moment it happens. 📌

    Designing for Low Overhead

    CREATE EVENT SESSION LongRunningQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.sql_text, sqlserver.username, sqlserver.client_hostname)
    WHERE duration > 5000000  -- 5 seconds, in microseconds — filter aggressively
)
ADD TARGET package0.event_file (
    SET filename = N'LongRunningQueries', max_file_size = 50, max_rollover_files = 5
)
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 5 SECONDS);
GO
ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = START;

    Three choices make this production-safe rather than a diagnostic one-off: an aggressive WHERE duration > filter (only capture what actually matters), a rollover file target with a size cap (bounded disk usage), and ALLOW_SINGLE_EVENT_LOSS (never let monitoring itself become a bottleneck).

    Auto-Starting on Server Restart

    ALTER EVENT SESSION LongRunningQueries ON SERVER WITH (STARTUP_STATE = ON);

    Reading the Results Later

    SELECT event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_time,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000000.0 AS duration_sec,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS sql_text
FROM sys.fn_xe_file_target_read_file('LongRunningQueries*.xel', NULL, NULL, NULL)
CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) ed
ORDER BY event_time DESC;

    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.

  • Module 6 Exercises: SQL Server Monitoring & Tooling Labs (10 Hands-On Exercises)

    Module 6 Exercises: SQL Server Monitoring & Tooling Labs

    5 guided labs, 3 challenge scenarios, and 2 break-it labs.

    Guided Labs

    Guided1. Enable Query Store on a test database and confirm its operation mode.

    ALTER DATABASE YourTestDb SET QUERY_STORE = ON;
SELECT actual_state_desc FROM sys.database_query_store_options;
    Guided2. Run the same query twice with different parameter selectivity, find both plans in Query Store, and force the better one.
    Guided3. Build a production-style Extended Events session filtered to duration > 2 seconds with a bounded rollover file target.
    Guided4. Add Page Life Expectancy, Batch Requests/sec, and Full Scans/sec to a PerfMon data collector set.
    Guided5. Read events back from an XEvents file target using sys.fn_xe_file_target_read_file.

    Challenge Scenarios

    Challenge6. A query performed well for months, then regressed after a deployment. Using Query Store, design a plan to find and force the pre-deployment plan without a code rollback.
    Challenge7. Design an Extended Events session to specifically catch queries causing tempdb spills, tying back to Module 4’s SpillToTempDb signature.
    Challenge8. A PerfMon dashboard shows Full Scans/sec climbing steadily over three months with no application changes. Propose which specific DMVs from this course you’d check next, in order.

    Break-It Labs

    Break-It9. Deliberately cause a query regression: force a bad plan via Query Store on a test query, observe degraded PerfMon/DMV metrics, then unforce it and confirm recovery.
    Break-It10. Deliberately create Extended Events overhead: run a session with NO duration filter capturing every statement on a busy test workload, observe the file size/overhead, then fix it with an aggressive filter and compare.

    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.

  • Module 6 Quiz: SQL Server Monitoring & Tooling (10 Questions)

    Module 6 Quiz: SQL Server Monitoring & Tooling

    1. How does Query Store differ from the plan cache?

    A) They are identical
    B) Query Store persists history per-database across restarts; the plan cache resets on restart
    C) Query Store only works on Azure
    D) The plan cache is more detailed

    Show Answer

    Answer: B

    This persistence is exactly what makes Query Store useful for tracking regression over time, not just current state.

    2. What does sp_query_store_force_plan let you do?

    A) Delete a query from history
    B) Pin a specific known-good plan for a query, without changing code
    C) Force a full index rebuild
    D) Disable Query Store

    Show Answer

    Answer: B

    This is a direct, reversible, production-safe response to parameter sniffing regressions.

    3. Why should a production Extended Events session use an aggressive WHERE duration filter?

    A) It’s not necessary
    B) To capture only what actually matters and keep overhead/disk usage low
    C) Filters are required by SQL Server syntax
    D) It has no effect on overhead

    Show Answer

    Answer: B

    Unfiltered capture on a busy server generates enormous volume — filtering aggressively is the difference between diagnostic and production-safe.

    4. What does EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS accomplish?

    A) Guarantees zero events are ever lost
    B) Prioritizes server performance over perfect event capture completeness
    C) Disables the session entirely
    D) Doubles the memory buffer

    Show Answer

    Answer: B

    This ensures monitoring itself never becomes a bottleneck — an intentional trade-off for production sessions.

    5. What does a low Page Life Expectancy counter typically indicate?

    A) Excellent buffer pool health
    B) Buffer pool pressure — pages being evicted and re-read from disk quickly
    C) A CPU bottleneck only
    D) A network issue

    Show Answer

    Answer: B

    This ties directly back to Module 1’s buffer pool concept — low PLE means data isn’t staying cached.

    6. A rising Compilations/sec relative to Batch Requests/sec often signals what?

    A) Excellent plan reuse
    B) Excessive recompiling, often from ad-hoc (non-parameterized) query bloat
    C) A hardware failure
    D) Normal, healthy behavior always

    Show Answer

    Answer: B

    This connects back to Module 1’s plan cache bloat discussion — too many unique ad-hoc statements compiling fresh plans.

    7. A rising Full Scans/sec trend alongside a stable workload most likely points to what?

    A) Everything is fine
    B) Missing or degraded indexes (fragmentation, stale stats)
    C) A network slowdown
    D) Increased RAM

    Show Answer

    Answer: B

    A trend change with stable workload is a strong signal something structural (Module 2 territory) has degraded.

    8. Which tool is best suited for answering “how has this specific query’s performance evolved over the past month”?

    A) sys.dm_os_waiting_tasks (live-only)
    B) Query Store
    C) PerfMon alone
    D) The plan cache alone

    Show Answer

    Answer: B

    Query Store’s whole design purpose is persisted, per-query historical tracking — exactly this question shape.

    9. Which tool is best for answering “what is currently blocking session 55, right now”?

    A) Query Store
    B) sys.dm_os_waiting_tasks
    C) PerfMon historical logs
    D) sys.dm_exec_query_stats

    Show Answer

    Answer: B

    This is a live, real-time question — the DMV showing current wait state is the right tool, not a historical aggregate.

    10. Why is combining multiple monitoring tools (not relying on just one) the recommended approach?

    A) It’s unnecessary, one tool does everything
    B) Each tool answers a different question shape — trend over time, current state, specific events, or per-query history
    C) More tools always means more accuracy regardless of fit
    D) This is not actually recommended

    Show Answer

    Answer: B

    PerfMon (trend), DMVs (current/historical state), XEvents (specific captured events), Query Store (per-query history) are complementary, not redundant.

    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.