You are experiencing performance issues with the database server. You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems. What should you create?
A.
a System Monitor report
B.
a sys.dm_exec_query_stats dynamic management view query
C.
a sys.dm_exec_session_wait_stats dynamicmanagement view query
D.
an Activity Monitor session in Microsoft SQL Management Studio.
Suggested Answer:C🗳️
sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches. Note: SQL Server wait stats are, at their highest conceptual level, grouped into two broad categories: signal waits and resource waits. A signal wait is accumulated by processes running on SQL Server which are waiting for a CPU to become available (so called because the process has "signaled" that it is ready for processing). A resource wait is accumulated by processes running on SQL Server which are waiting fora specific resource to become available, such as waiting for the release of a lock on a specific record.
I think that option D is the right answer. Based on the requirements:
- locking issues = Resource Waits pane in Activity Monitor
- plan cache memory pressure points = Recent Expensive Queries
- Backup I/O - Resource waits Buffer I/O and Data File I/O
no, D would give a high level overview of what is happening, but to investigate in more details you need to use dynamic management views, so C is the correct answer.
Exam Ref 70-762 Developing SQL Databases (Louis Davidson) - Pag 449 searching by "dm_exec_session_wait_stats "
You use the sys.dm_os_wait_stats, sys.dm_exec_session_wait_stats, or
sys.dm_os_waiting_tasks DMVs to gather information about the amount of time that
threads must wait on resources, determine whether the server is experiencing CPU,
memory, or IO pressure, or find out which resources are causing excessive waits.
Okay, study guide! Pages 423 and 424. Read carefully - sys.dm_os_wait_stats - CPU Pressure, IO Issues, Memory Issues.
"sys.dm_exec_session_wait_stats (!!!! option C) - This DMV is new in SQL Server 2016 and is identical in structure to sys.dm_os_wait_stats, but has an additional column for session ID. However, it is important to note that this new DMV only includes information for sessions that are currently connected."
study guide pg 424 sys.dm_exec_session_wait_stats can uncover some of the
following potential issues on your server:
CPU pressure
IO Issues
Memory pressure
Here is the more explanation, it provides session id as well for each batch or query :
Returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches. This view returns session the same information that is aggregated for sys.dm_os_wait_stats (Transact-SQL) but provides the session_id number as well.
Yes you all are right, D is also correct answer but it limits to session level means it will encounter for current session meanwhile sys.dm_exec_session_wait_stat view information for all encountered issues regardless of current session. so in this scenario D is not correct answer bucause it goes away when you disconnect session from activity monitor.
might be im wrong but its my understanding. :-)
sys.dm_exec_query_stats In combination with sys.dm_exec_sql_text, find queries that use a lot of resources, such as CPU time or IO.
sys.dm_exec_session_wait_stats View information about waits at the session level. However, it is important to note that this new DMV only includes information for sessions that are currently connected. When a session disconnects, its wait statistics are cleared from the DMV. Nonetheless, it can be helpful when you need to diagnose the workload for a specific session.
Agree. look at question 75 (same) there is the correct answer as should have been here: Activity Monitor
upvoted 3 times
...
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Nelly100
Highly Voted 4 years, 8 months agoAnette
4 years, 5 months agoq24
Highly Voted 5 years, 8 months agokiri2020
4 years, 1 month agoLuzix
Most Recent 4 years agoAndy7622
4 years, 2 months agoCococo
4 years, 3 months agoCococo
4 years, 3 months agostm22
4 years, 4 months agoJawwadAK
4 years, 8 months agoJawwadAK
4 years, 8 months agoJohnFan
4 years, 9 months agoitdoesntmatter
4 years, 9 months agoCageman
4 years, 11 months agoDieter
5 years, 3 months ago