You have a database that is accessed by 300 concurrent users.
You need to log all of the queries that become deadlocked. The solution must meet the
following requirements:
Provide a representation of the deadlock in XML format.
Minimize the impact on the server.
What should you create?
A.
A SQL Server Profiler trace
B.
A script that enables trace flags
C.
A SQL Server Agent job that retrieves information from the
sys.dm_tran_active_transactions dynamic management views
D.
A SQL Server Agent job that retrieves information from the
sys.dm_tran_session_transactions dynamic management views
Explanation:
Analyze Deadlocks with SQL Server Profiler
Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there
is a cyclic dependency between two or more threads, or processes, for some set of
resources within SQL Server. Using SQL Server Profiler, you can create a trace that
records, replays, and displays deadlock events for analysis.
To trace deadlock events, add the Deadlock graph event class to a trace. This event class
populates the TextData data column in the trace with XML data about the process and
objects that are involved in the deadlock. SQL Server Profiler can extract the XML document
to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio.
The answer could be B because it has less impact on server performance.
Using SQL Server Profiler has an impact on the server performance; trace flags are server-side tracing and the impact can be minimized.
Flag 1222 can give an xml like output.
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
https://www.mssqltips.com/sqlservertutorial/252/tracing-a-sql-server-deadlock/
B is the right answer
Is xml like output = xml format?
yes, B is correct
A. The output from flag 1222 has nothing to do with xml. It is plain text with indentation. I have no idea why some sources call it “xml like” format – this is very misleading. The Profiler remains our only choice to “provide a representation of the deadlock in XML format” despite its performance impact.
A tricky question.
What does “Minimize the impact on the server” mean? Is it about performance or about gathering information about deadlocks?
If I’m not mistaken, trace flags works in the server scope. So, if I enable trace flags, I’ll get info about all deadlocks on server, not in my database only. It impacts on server.
I chose A in the exam. But i got less than 60% in for this section. So, I am inclined to think that the answer is NOT A. Should be B.