You have a query that is used by a reporting dashboard.
Users report that the query sometimes takes a long time to run.
You need to recommend a solution to identify what is causing the issue.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A.
Set the blocked process threshold, and then run SQL Server Profiler.
B.
Set the blocked process threshold, and then create an alert.
C.
Enable trace flag 1204, and then create an alert.
D.
Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.
Explanation:
Step 1: Turn on the blocked process report. This will look for any blocking taking 20 seconds
or longer.
–Make sure you don’t have any pending changes
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
exec sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE
GO
exec sp_configure ‘blocked process threshold (s)’, 20;
GORECONFIGURE
GO
Step 2: Set up a trace to capture the blocked process report. Run it as a server side trace.
Explanation talks about ‘A’
0
0
I agree with you I think A is the logic right answer
0
0
Thanks for your personal marvelous posting! I definitely enjoyed reading it, you may be a great author.I will always bookmark your blog and may come back at some point. I want to encourage you to definitely continue your great writing, have a nice afternoon!|
https://jacksilva.wordpress.com/
0
0
Post Scheduling – The search engines, Google for instance, can efficiently fish out automated weblogs if a significant number of articles or blog posts are created at once. Key terms can do miracles to a site and build stimulation in the minds of visitors. You can choose what posts your supporters watch on what days and on what occasions.
http://www.ifansdellabellezza.com/component/k2/itemlist/user/279090.html
0
0
A and B
Benefits of SQL Server Profiler
Monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly. SQL Server Profiler is used for activities such as:
•Stepping through problem queries to find the cause of the problem.
•Finding and diagnosing slow-running queries.
•Capturing the series of Transact-SQL statements that lead to a problem. The saved trace can then be used to replicate the problem on a test server where the problem can be diagnosed.
•Monitoring the performance of SQL Server to tune workloads. For information about tuning the physical database design for database workloads, see Database Engine Tuning Advisor.
•Correlating performance counters to diagnose problems.
SQL Server Profiler also supports auditing the actions performed on instances of SQL Server. Audits record security-related actions for later review by a security administrator.
Alerts
Applies To: SQL Server 2016 Preview
Events are generated by SQL Server and entered into the Microsoft Windows application log. SQL Server Agent reads the application log and compares events written there to alerts that you have defined. When SQL Server Agent finds a match, it fires an alert, which is an automated response to an event. In addition to monitoring SQL Server events, SQL Server Agent can also monitor performance conditions and Windows Management Instrumentation (WMI) events.
To define an alert, you specify:
•The name of the alert.
•The event or performance condition that triggers the alert.
•The action that SQL Server Agent takes in response to the event or performance condition.
0
0
I mean A is correct
0
0
Nope!! Option B is the correct one. Why would anyone want to start a SQL Profiler Trace for a long-running query?!
0
0
It says ‘sometimes’ takes a long time which implies in normal time it’s OK. So you just let the profiler running excessive long time hoping to catch the problem when it happens?
I would vote for B. Create an Alert, for long running queries, choose Object “MSSQL$InstanceName:Transactions” and Counter: Longest Transaction Running Time. Configure the values, and the alert notification options. When it happens again, you will get email or pager, then you log on to the server to check DMV or whatever to see what causing the problem.
0
0
The question is assuming that blocking is the problem which isn’t always the case. That said I think A) is the correct answer. I don’t think you can use the blocked process report via an alert can you? I just checked… No. You seem to be able to.
I’m surprised that they don’t offer Extended events. That’s probably a much better way to go.
0
0
YOU seem not be able to, others are… 😉
https://msdn.microsoft.com/en-us/library/ms181150(v=sql.120).aspx
“A” is only a good idea, when you try to force the issue of a slow server. So the BEST answer should be “B”.
0
0
*No. You seem not to be able to ^^^^^
0
0
This should be D)
0
0