nerdexam
Microsoft

70-466 · Question #73

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. Extended Events is the best deadlock monitoring solution because it satisfies all three requirements - real-time capture, easy enable/disable via T-SQL, and queryable data targets.

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 implement? More than one answer choice may achieve the goal. Select the BEST answer.

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

(21 responses)
  • A
    71% (15)
  • B
    5% (1)
  • C
    5% (1)
  • D
    19% (4)

Why each option

Extended Events is the best deadlock monitoring solution because it satisfies all three requirements - real-time capture, easy enable/disable via T-SQL, and queryable data targets.

Aan Extended Events sessionCorrect

Extended Events sessions capture the deadlock_graph event in real time and can be started or stopped with a single ALTER EVENT SESSION T-SQL statement, making them easy to enable and disable. Data is stored in queryable targets such as ring_buffer or event_file, accessible via DMVs like sys.dm_xe_session_targets, satisfying all three stated requirements.

Ba SQL Server Profiler template

SQL Server Profiler requires a GUI to configure and run, cannot be easily toggled via T-SQL, and does not support querying captured trace data through standard SQL statements.

Clog errors by using trace flag 1204

Trace flag 1204 writes deadlock information to the SQL Server error log in a less detailed format, requires a server restart to enable or disable, and stores data in plain text that is not directly queryable.

Dlog errors by using trace flag 1222

Trace flag 1222 logs more detailed deadlock XML to the error log than 1204 but still requires a server restart to toggle and does not support structured querying of the captured deadlock data.

Concept tested: Extended Events session for real-time deadlock monitoring

Source: https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server

Topics

#deadlock monitoring#Extended Events#real-time monitoring#SQL Server tracing

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice