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.
Explanation:
http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/
http://msdn.microsoft.com/en-us/library/ms188396.aspx
I’m leaning towards A on this one. Reading the explanation, DBCC Traceon 1222 is for identifying the sessions involved in deadlock situations and while queries running for more than a second may well indicate they are being blocked, it does not necessarily mean there is a deadlock situation.
+1 for answer A. DBCC Traceon 1222 is (as far as I know) used for the sessions involved in deadlock situations
A is the answer
A is correct. You need to find queries running more than 1sec, not deadlocks.
Agreed to answer A.
I beg to differ. Answer is B. Extended events can be run for an extended period of time without impacting the performance of the server which is already under pressure. A would also be OK had they not said that it can be run for an extended period of time and D is hopelessly wrong because we are not sure that the problem is deadlocks.
I agree with Answer B. If A had been “create a server-side trace” I would have gone with that but the Profiler is too resource intensive to let run over an extensive period of time.
TF1222 relates only to deadlocks and not locks…..
Is it a problem that the db is “highly active”? SQL Server profiler requires restart.