70-466 · Question #46
You have a database named database1. Database developers report that there are many deadlocks. You need to implement a solution to monitor the deadlocks. The solution must meet the following requireme
The correct answer is A. an Extended Events session. An Extended Events session satisfies all three requirements - real-time monitoring, easy enable/disable via T-SQL, and structured queryable output.
Question
Exhibit
Options
- Aan Extended Events session
- Ba SQL Server Profiler template
- Clog errors by using trace flag 1204
- Dlog errors by using trace flag 1222
How the community answered
(45 responses)- A73% (33)
- B4% (2)
- C16% (7)
- D7% (3)
Why each option
An Extended Events session satisfies all three requirements - real-time monitoring, easy enable/disable via T-SQL, and structured queryable output.
Extended Events sessions can track deadlocks using the xml_deadlock_report event, which streams live data for real-time visibility through the SSMS Live Data viewer. Sessions are started and stopped with simple ALTER EVENT SESSION statements, meeting the easy enable/disable requirement. Data captured in ring buffer or file targets can be queried with T-SQL using XQuery or system functions, fulfilling the queryable data requirement.
SQL Server Profiler is a deprecated GUI-based tool that requires manual interaction to start and stop, does not expose captured data as directly queryable structured output, and carries higher overhead.
Trace flag 1204 writes basic deadlock information to the SQL Server error log, which is a flat text file rather than a queryable data store, and requires DBCC TRACEON/TRACEOFF or server restart to toggle.
Trace flag 1222 provides more detailed deadlock output than 1204 but writes only to the error log, shares the same lack of queryability, and is not as easily enabled or disabled programmatically.
Concept tested: Extended Events session for deadlock monitoring
Source: https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-ssms-xe-profiler
Topics
Community Discussion
No community discussion yet for this question.
