DRAG DROPNote: This question is part of a series of questions that use the same scenario. For your convenience, the
scenario is repeated in each question. Each question presents a different goal and answer choices, but the text
of the scenario is exactly the same in each question in this series.
You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server
instance. The topology for the environment is shown in the following diagram.
You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is
four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is
read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty
table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and
perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log
backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 andSite2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \\\\SQLBackup\\. A
separate process copies backups to an offsite location. You should minimize both the time required to restore
the databases and the space required to store backups. The recovery point objective (RPO) for each instance
is shown in the following table.
Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the
keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1
that is part of AG1.
Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a
member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make
no changes to the data. The queries must be load balanced over variable read-only replicas.
Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is
a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the
database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table.
You need to analyze the wait type and statistics for specific instanced in the environment.
Which object should you use to gather information about each instance? To answer, drag the appropriate
objects to the correct instances. Each object may be used once, more than once, or not at all. You may need to
drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:
Answer: 
Explanation:
Instance 1: sys.dm_exec_query_stats
From Scenario: Instance1 requirement: Aggregate statistics since last server restart.
sys.dm_exec_query_stats returns aggregate performance statistics for cachedquery plans in SQL Server.
Instance 4: sys.dm_os_wait_stats
sys.dm_os_wait_statsreturns information about all the waits encountered by threads that executed.
From Scenario: Instance4 requirement: Identify the most prominent wait types.
Instance 5:sys.dm_exec_session_wait_stats
From Scenario: Instance5 requirement: Identify all wait types for queries currently running on the server.
sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed
for each session.
Instance 1 Aggregate wait statistics since the last server restart
Proposed answer Sys.dm_exec_query_stats
Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
I believe Sys.dm_os_wait_stats
Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches
Instance 4 Identify the most prominent wait types for all the commands originating from a session , between session connections , or between application pool resets
Proposed answer Sys.dm_os_wait_stats
I think 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. 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.
Also it has the waiting task count where you can see the most prominent wait types
Instance 5 Identify all the wait types for Queries Currently Running on the server
Proposed answer sys.dm_exec_sessions
Where I have run and I cannot find anything about wait stats
I would say Sys.dm_exec_requests
Returns information about each request that is executing within SQL Server and has a column with wait type “If the request is currently blocked, this column returns the type of wait “
I completely agree with all remarks made by fnmag. And I like to add that in the “answer area” the answer with instance 5 is now (sys.dm_exec_sessions) different from the answer (sys.dm_exec_session_wait_stats) given in the provided explanation. But both are incorrect, correct answer for instance5 should be sys.dm_exec_requests.