Exam 70-765 topic 3 question 14 discussion

Actual exam question from Microsoft's 70-765

Question #: 14
Topic #: 3

You administer a Microsoft SQL Server 2012 server. One of the databases on the server supports a highly active OLTP application.
Users report abnormally long wait times when they submit data into the application.
You need to identify which queries are taking longer than 1 second to run over an extended period of time.
What should you do?

A. use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000.
B. Use sp_configure to set a value for blocked process threshold. Create an extended event session.
C. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step.
D. Run the sp_who command from a query window.
E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.

Show Suggested Answer

Suggested Answer: A 🗳️
Incorrect Answers:
E: DBCC TRACEON 1222 is used to detect deadlocks, not to detect long running queries.
References:
http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/ http://msdn.microsoft.com/en-us/library/ms188396.aspx

by Slava_bcd81 at Sept. 29, 2020, 9:46 a.m.

Comments

Submit Cancel

HenrikFFM

4 years, 7 months ago

In other Tests the Anser A talks from a extend Events Session. Which is the best an correct Answer

upvoted 2 times

...

Slava_bcd81

4 years, 9 months ago

B is applicable too, and its good to set blocked processes threshold to minimum to exclude waits on locks

upvoted 1 times

Slava_bcd81

4 years, 9 months ago

because if there are locks issues almost any query can have long run and often there is no need to measure that time

upvoted 1 times

...

RohitRaj2311

4 years, 8 months ago

You cannot use blocked process threshold because its minimum value is 5. it wakes up every 5 seconds and checks for blocking. Question is asking about 1 second. So answer is A.

upvoted 3 times

...

Hoglet

4 years, 5 months ago

It's not asking to detect blocked, but duration over 1 sec. You can have a long running query without blocking

upvoted 1 times

...