You need to optimize query performance solution you

You need to optimize query performance.
Solution: You rebuild all indexes.
Does the solution meet the goal?
A. Yes
B. No
Answer: A
12.HOTSPOT
You have a Microsoft SQL Server instance that hosts a database
named DB1 that contains 800 gigabyte (GB) of data. The database is
used 24 hours each day. You implement indexes and set the value of
the Auto Update Statistics option set to True.
Users report that queries take a long time to complete.
You need to identify statistics that have not been updated for a week
for tables where more than 1,000 rows changed.
How should you complete the Transact-SQL statement? To answer,
configure the appropriate Transact-SQL segments in the answer area.
page 18 / 39

Answer:
page 19 / 39

13.DRAG DROP
Note: 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.
page 20 / 39

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.
page 21 / 39

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 and Site2. 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.