Provisioning SQL Databases — Question 62

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?

Answer options

• 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.

Correct answer: A

Explanation

Option A is correct because SQL Profiler allows you to capture and filter queries based on their execution time, specifically targeting those that exceed 1 second. The other options either do not provide a way to specifically identify slow queries or focus on different aspects of SQL Server management that are not directly related to measuring query duration.