Using Extended Events and Query Store - Exercise

This exercise is auto-scored. You will receive 15 points for completing the exercise and 5 points for each correct answer. When you are finished, click Submit to view the results page and explanations.

You are the DBA for the XYZ Rental Car Corporation. Users have reported that the database feels “slow” at certain times of day, and your job is to determine what the server was doing when the slowdown occurred.

In older SQL Server environments, DBAs often captured a heavy trace using SQL Trace/Profiler and then searched through a .trc file. In modern SQL Server (including SQL Server 2025), the workflow has shifted:

• Extended Events (XEvents): event-driven “sensors” used for targeted, point-in-time troubleshooting (for example: a deadlock at 2:00 AM).
• Query Store: aggregated, historical performance analysis over hours/days/weeks (top queries, regressions, wait stats, plan changes).

Key takeaway: if legacy Trace was like a video recording of everything, an Extended Event is a sensor that records only when a relevant condition occurs.

Review the capture below and answer the questions that follow. Treat the image as either:

1. an Extended Events live view (XEvent Profiler style), or
2. an XEL capture loaded into the SSMS viewer from an event session.

Your answers should focus on the SQL you would run to identify the top resource consumers, using:

• Query Store for historical totals and trends, and
• Extended Events when you need exact event-level context (statement text, duration, CPU, reads, user/session context).

Write the SQL statement(s) you would use to answer each question. You may assume the database uses Query Store and that you have access to sys.query_store_* views.

1. Longest single execution: Which user (or application login/session context) has the longest duration for a single query execution, and what was that duration?
   Hint: Query Store can show high duration outliers; Extended Events can capture the exact statement and session context.
2. Highest CPU consumer: What is the highest CPU consumed by a single command (or execution), and who ran it?
   Hint: Use Query Store runtime stats for CPU aggregates; use XEvents if you must confirm a point-in-time spike with statement text.
3. Highest logical reads / I/O: Which user generated the most I/O for a query, and how much I/O was consumed?
   Hint: In Query Store, use logical reads as the common proxy; in XEvents, capture reads/writes when troubleshooting a specific incident window.

Type or paste your SQL into the text box below. Then click Submit to view the results page.