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.
Run the sp_who command from a query window.
D.
Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
E.
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.
Explanation:
Verified the SQL Profiler and DBCC answers as correct. However, while Profiler will show this information, the
best practice with Profiler is to use it short-term. The question specifically states “over an extended period of
time”. That means Profiler wouldn’t be the best tool for this scenario. Therefore, DBCC would be the best
answer.
Reference: http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/
Reference: http://msdn.microsoft.com/en-us/library/ms188396.aspx
Answer A ist right (SQL Server Profiler)
Answer D ist wrong. The Traceflag 1222 Shows Deadlocks, not the Duration of an query.
Confirmed Testee correct. 1222 shows deadlocks. SQL Server Profiler is the only possible answer left.
Correct answer is B. Use sp_configure to set a value for blocked process threshold. Create an extended event session.
http://technet.microsoft.com/en-us/library/ms191168.aspx
A is best choice
B is wrong because:
The SQL Server lock monitor is responsible for implemeting 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.
http://support.microsoft.com/kb/2157154
Definately A.
We can Filter queries that have a Duration value of more than 1,000(1 sec) using with the SQL profiler Or DMV’s only no other way to filter the duration.
also, DBCC TRACEON 1222 is wrong syntax.. should be DBCC TRACEON(1222)..
so must be A.
choosing answer B is wrong because blocked process threshold event is not generated for tasks that are waiting on resources that do not generate detectable deadlocks.
choosing answer A is some how wrong because the databases on the server supports a highly active OLTP application and we all know SQL Server Profiler is a graphical tool that has a significant performance impact on the server being traced. And also it is not advisable to use it for extended period of time.
Answer D is wrong DBCC TRACEON 1222 used to log deadlock in error log
C and E are very wrong too…
I rather choose A…but the question is very tricky
so what is the correct answer?
A
Remember, just choose the answer that fits the requirement.
There is no mention to use best practices, or what experts recommend.
A process can run for longer than 1 sec, without blocking or causing a deadlock.
Answer: 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.
It cannot be A because question clearly states “over an extended period of
time” and profiler is not good for extended periods of time