nerdexam
Microsoft

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.

Configure and maintain an analysis services database

Question

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 requirements: - Support real-time monitoring. - Be enabled and disabled easily. - Support querying of the monitored data. What should you do?

Exhibit

70-466 question #46 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)
  • A
    73% (33)
  • B
    4% (2)
  • C
    16% (7)
  • D
    7% (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.

Aan Extended Events sessionCorrect

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.

Ba SQL Server Profiler template

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.

Clog errors by using trace flag 1204

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.

Dlog errors by using trace flag 1222

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

#Extended Events#deadlock monitoring#real-time monitoring#session management

Community Discussion

No community discussion yet for this question.

Full 70-466 Practice