What should you do?

You administer a Microsoft SQL Server database 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.

View Answer

Answer: A

Explanation:

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.

Incorrect:

Not B: The SQL Server lock monitor is responsible for implementing the logic to detect a blocking scenario if the ‘blocked process threshold’ value is greater than 0.

However, the lock monitor only wakes up every 5 seconds to detect this condition (it is also looking for other conditions such as deadlocks). Therefore, if you set a ‘blocked process threshold’ value to 1, it will not detect a process that has been blocking for 1 second. The minimum time it can detect a blocked process is 5 seconds.

Not E: The Traceflag 1222 Shows Deadlocks, not the Duration of an query.

References: https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler