Exam 70-764 topic 1 question 81 discussion

Actual exam question from Microsoft's 70-764

Question #: 81
Topic #: 1

You administer a Microsoft SQL Server 2016 database named Orders.
Users report that during peak usage periods, certain operations are taking more time than expected. Your initial analysis suggests that blocking is the cause.
You need to gather more data to be able to determine which processes are being blocked and to identify the root cause.
What should you do?

A. Start a trace using SQL Server Profiler to catch the Lock: Deadlock event.
B. Use sp_configure to set the blocked process threshold. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.
C. Schedule a SQL Agent job to run every 60 seconds and insert the results of executing the sys.dm_os_wait_stats DMV into a table.
D. Use System Monitor to catch the Lock Waits/sec event.

Show Suggested Answer

Suggested Answer: B 🗳️

by TheSwedishGuy at Jan. 24, 2020, 3:04 p.m.

Comments

Submit Cancel

TheSwedishGuy

5 years, 4 months ago

Use the blocked process threshold option to specify the threshold, in seconds, at which blocked process reports are generated. The threshold can be set from 0 to 86,400. By default, no blocked process reports are produced.

upvoted 2 times

...

TheSwedishGuy

5 years, 5 months ago

While deprecated, SQL Server Profiler is still sometimes used.

upvoted 1 times

Zikato

4 years, 5 months ago

While sadly true, the SQL Server 2016 is already capable of extended events (blocked_process_report)

upvoted 1 times

...