Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 49 discussion

Actual exam question from Microsoft's 70-762

Question #: 49
Topic #: 1

Note: This question is part of a series of questions that use the same or similar answer choices. An Answer choice may be correct for more than one question in the series. Each question independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You observe that many deadlocks appear to be happening during specific times of the day.
You need to monitor the SQL environment and capture the information about the processes that are causing the deadlocks. Captured information must be viewable as the queries are running.
What should you do?

A. A. Create a sys.dm_os_waiting_tasks query.
B. Create a sys.dm_exec_sessions query.
C. Create a PerformanceMonitor Data Collector Set.
D. Create a sys.dm_os_memory_objects query.
E. Create a sp_configure "˜max server memory' query.
F. Create a SQL Profiler trace.
G. Create a sys.dm_os_wait_stats query.
H. Create an Extended Event.

Show Suggested Answer

Suggested Answer: F 🗳️
To view deadlock information, the Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server Profiler.
Trace Flag 1204 and Trace Flag 1222
When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each nodeinvolved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable bothtrace flags to obtain two representations of the same deadlock event.
References:
https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

by 414 at Feb. 19, 2020, 12:50 p.m.

Comments

Submit Cancel

Anette

Highly Voted 4 years, 11 months ago

It loolks like both F and H are correct. One way to capture the graph is to set up a server-side Profiler trace to capture the Deadlock Graph event, and wait for the deadlock to recur. However, a much more convenient method is to use the Extended Events. But since Extended Events is more convenient, I would choose H.

upvoted 8 times

Hoglet

4 years, 6 months ago

Upvoted you, F & H are correct I would find Profiler more convenient, but that's because my use of SQL Server pre-dates extended events.

upvoted 1 times

...

Heisenberg008

Highly Voted 5 years, 4 months ago

H might be correct

upvoted 5 times

JohnFan

5 years, 3 months ago

Using Extended Events is a lightweight approach to capturing query plans.

upvoted 2 times

...

BabyBee

Most Recent 4 years, 5 months ago

If you use SQL Server Profiler to capture a deadlock graph, you must configure the trace before deadlocks occur. In Extended Events, you can use the continuously running system_health session to discover past deadlocks. As an alternative, you can set up a new session dedicated to capturing deadlock information. The system_health session automatically captures detected deadlocks without requiring special configuration. That means you can analyze a deadlock after it has occurred.

upvoted 1 times

...

Braindripper

4 years, 5 months ago

I would choose F since book says A deadlock graph provides you with insight into the objects involved in a deadlock and identifies the terminated process. You can capture a deadlock graph by using either SQL Server Profiler to later review deadlock events "that have yet to occur" (our case) or by using Extended Events to review deadlock events that have already occurred.

upvoted 1 times

...

kimalto452

4 years, 6 months ago

F is correct, read docs... its free.

upvoted 2 times

...

SzalonyZielonyRobak

5 years, 3 months ago

In this case we are talking about CURRENT SITUATION, not PAST - so SQL PROFILER.

upvoted 4 times

Froze

5 years, 2 months ago

Come on! There is NO such thing like CURRENT. All you see is past. You will see it AFTER deadlock occurs. So both Extended Events and SQL Profiles will show you PAST. Both will provide same information. SQL Profile is depreciated since Extended Event were introduced and Extended Event are recommended to be used. So I would answer H - Extended Events.

upvoted 12 times

Nickname17

5 years, 2 months ago

Captured information must be viewable as the queries are running.

upvoted 3 times

...

JohnFan

5 years, 3 months ago

SQL Profiler trace captures deadlocks that are going to happen. Extended Events capture past deadlocks.

upvoted 2 times

databasejamdown

4 years, 8 months ago

This is not true. Extended events captures events as they are happening. JohnFan, are you trolling?

upvoted 3 times

Hoglet

4 years, 6 months ago

He does seem to provide information copied straight off the Mircosoft website which isn't actually relevant. This is one of the few times where he's just straight up wrong.

upvoted 1 times

...

lh2607

4 years, 7 months ago

Not true about Extended Events capturing past deadlocks.

upvoted 1 times

...

geekeek1

4 years, 5 months ago

STOP trolling

upvoted 2 times

...

414

5 years, 4 months ago

I think A is correct because it states "Captured information must be viewable as the queries are running."

upvoted 1 times

Twigit

5 years, 4 months ago

F is correct. I interpret "Viewable as the queries you are running" means actually seeing the queries that were run in the database, which Profiler allows you to do

upvoted 7 times

...

JohnFan

5 years, 3 months ago

Most DMOs provide information about the current state, such as currently blocked sessions in sys.dm_os_waiting_tasks, or information accumulated since SQL Server last restarted, such as sys.dm_os_wait_stats. SQL Server retains the information accessible through DMOs in memory only and does not persist it to disk. Therefore, the information is reset when SQL Server restarts. you can use the sys.dm_trans_locks DMV in conjunction with the sys.dm_os_waiting_tasks DMV to find blocked sessions

upvoted 1 times

...