You need to optimize query performance Solution You rebuild all indexes Does

You need to optimize query performance solution you

This preview shows page 18 - 23 out of 39 pages.

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
Image of page 18
Answer: page 19 / 39
Image of page 19
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
Image of page 20
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
Image of page 21
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.
Image of page 22
Image of page 23

You've reached the end of your free preview.

Want to read all 39 pages?

  • Spring '18
  • E. J Janet Smith
  • Relational model, Microsoft SQL Server, Transact-SQL

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture