You are troubleshooting an application that runs a query. The application frequently causes
deadlocks.
You need to identify the isolation level used by the query when a deadlock occurs.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A.
Query the sys.dm_exec_requests dynamic management view.
B.
Create a trace in SQL Server Profiler that contains the Deadlock graph event.
C.
Query the sys.dm_exec_sessions dynamic management view.
D.
Enable trace flag 1222, and then view the SQL Server error log.
Explanation:
* sys.dm_exec_sessions
Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a
server-scope view that shows information about all active user connections and internal
tasks.
Include the column:
transaction_isolation_level
smallint
Transaction isolation level of the session.
0 = Unspecified
1 = ReadUncomitted
2 = ReadCommitted
3 = Repeatable
4 = Serializable
5 = Snapshot
Is not nullable.
Both A and C would provide the correct information. I’m not sure why C is the BEST answer though
0
0
I Think questions says “Application level” and in dm_exec_sessions we have on consolidated row for this application and its isolation level instead of dm_exec_requests where we have many transaction_ids..
so it looks like C is best
0
0
Why not a trace flag?
https://technet.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx
0
0
I should be ‘A’ because from there we can get transaction id.
0
0
Maybe “C” because sys.dm_exec_sessions contains information about program name (program_name)? In question says “application”.
0
0
I agree with jml ,ans is option D ,trace flag 1222
Just trace flag can log the queries information when the deadlock “occurs”.
Both DMV of A and C can not log (or Show) the deadlock information occur immediately ,even select DMV by Agentjob frequently.
0
0
Option C Right
0
0
Answer is D.
Trace flag 1222 will record the isolation level as part of the log when a deadlock occurs.
A/C are impractical because you do not know when the deadlock will happen.
0
0
Option C is the right (and best) answer. Trace flags will not return the ISOLATION LEVEL info with the deadlock report.
0
0
C is the best answer.
You may check the link below.
https://msdn.microsoft.com/en-sg/library/ms176013.aspx
mahikero@yahoo.com
0
0
@Skippo: Will so too 😛 Take a look at the sample at https://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/ It clearly shows the isolation level recorded for the queries involved.
I’d personally prefer to have the nice deadlock graph so would rather capture deadlocks with an extended events session but expect that just logging the events is lower impact. I reckon D. Enable trace flag 1222, and then view the SQL Server error log
As for viewing deadlocks using a DMV, they can happen very quickly and I’d rather not be hanging around pressing f5 all day and hoping that I can capture the event when there are easier more reliable methods available which will provide much better information.
0
0
Thank you for sharing these kinds of wonderful posts. In addition, the perfect travel and medical insurance plan can often ease those fears that come with traveling abroad. Some sort of medical emergency can quickly become costly and that’s absolute to quickly impose a financial weight on the family finances. Having in place the best travel insurance package prior to leaving is well worth the time and effort. Thanks a lot
0
0