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:
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.http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-
1222/
http://msdn.microsoft.com/en-us/library/ms188396.aspx
TRACE FLAG 1222 is abou detecting deadlocks. not about following long running queries (which don’t have to neccessary end with deadlocks)
coorect answer is a)
agree
yupe!
If you remove A from the equation then E is your only closest option. Since the question calls for EXTENDED use, then profiler is NOT a good option since it is a KNOWN fact that profiler sometimes causes the server to crawl, which is bad for production situations.
no still E wont becorrect the trace flag for 1222 is for deadlocks!
.. what about answer B ?
.. as a combination of
http://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server-2005-and-2008/
and
http://www.mssqltips.com/sqlservertip/2155/getting-started-with-extended-events-in-sql-server-2008/
.. and even another hint conc. answer B
@see http://www.brentozar.com/archive/2014/03/extended-events-doesnt-hard/
.. an addition ..
USE [master]
select
p.name as package
, xo.name as object_name
, xo.description
, xo.object_type
from sys.dm_xe_objects xo
join sys.dm_xe_packages p
on xo.package_guid = p.guid
where (
xo.name like ‘%block%’
)
—
— RESULT —
* package :
sqlserver
* object_name :
blocked_process_report
* description :
Occurs when a task has been blocked longer than the time that is specified by the sp_configure blocked process threshold setting. This event is not triggered by system tasks or by tasks that are waiting for non-deadlock-detectable resources. Use this event to troubleshoot blocked processes.
(By default, blocked process reports are not generated.)
* object_type
event
—
A is correct, we do not have queries just insert stmts. “Occurs when ..they submit data…”
Correct answer is B.
E is wrong. This is only for DEADLOCKS. The question is not asking BLOCKING OR DEADLOCK.
A is wrong. Simply you can not run profiler over a extended period of time for performance reason.
B is correct.No harm to run it for extended period of time. This is not like profiler. Profiler is going to be deprecated anyway.
A note from MS:
Extended Events is the logical heir to SQL Profiler which we’re told will eventually be deprecated (no time soon, but better to get a head start!). Part of the reason for this is the heavy overhead that SQL Profiler brings, making it useful only for real time troubleshooting. By contrast, Extended Events has a lower overhead and can be run persistently, generating event data that can be written to memory or even reports for later viewing.
These stupid questions… On its face, A or B both work because it’s unclear at what point a process is too long to favor B over A. One hour, a day, over a week? They both log.
By the way,
http://blog.sqlauthority.com/2014/06/30/sql-server-find-blocking-using-blocked-process-threshold/
I think generally extended events is preferred over sql profiler trace except in instances when you want to replay the trace on another computer. Someday they’ll figure out how to have extended events do replayable traces and they can remove sql profiler
I would go for B:
https://blogs.msdn.microsoft.com/nav/2015/01/16/using-sql-server-extended-events-to-produce-a-blocked-process-report/