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.
Hot Area:

DRAG DROP
You administer a database that is used for reporting purposes. The database has a large fact table that
contains three hundred million rows. The table includes a clustered columnstore index and a nonclustered
index on the ProductID column. New rows are inserted into the table every day.Performance of queries that filter the Product ID column have degraded significantly.
You need to improve the performance of the queries.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of
actions to the answer area and arrange them in the correct order.
Select and Place:

HOTSPOT
You are the database administrator of a Microsoft SQL Server instance. Developers are writing stored
procedures to send emails using sp_send_dbmail. Database Mail is enabled.You need to configure each account’s profile security and meet the following requirements:
Account SMTP1_Account must only be usable by logins that have been given explicit permissions to use
the SMTP1_profile.
Account SMTP2_Account must only be usable by logins who are a member of the [DatabaseMailUserRole]
role in msdb.
In the table below. identify the profile type that must be used for each account.
NOTE: Make only one selection in each column.
Hot Area:

HOTSPOT
You deploy a Microsoft SQL Server instance to support a global sales application. The instance includes the
following tables: TableA and TableB.
TableA is a partitioned table that uses an incrementing integer number for partitioning. The table has millions of
rows in each partition. Most changes to the data in TableA affect recently added data. The UPDATE
STATISTICS for TableA takes longer to complete than the allotted maintenance window.Thousands of operations are performed against TableB each minute. You observe a large number of Auto
Update Statistics events for TableB.
You need to address the performance issues with each table.
In the table below, identify the action that will resolve the issues for each table.
NOTE: Make only one selection in each column.
Hot Area:

You have a database named DB1 that stores more than 700 gigabyte (GB) of data and serves millions of
requests per hour.
Queries on DB1 are taking longer than normal to complete.
You run the following Transact-SQL statement:
SELECT * FROM sys.database_query_store_options
You determine that the Query Store is in Read-Only mode.
You need to maximize the time that the Query Store is in Read-Write mode.
Which Transact-SQL statement should you run?

You manage a Microsoft SQL Server environment. You plan to encrypt data when you create backups.
You need to configure the encryption options for backups.
What should you configure?

DRAG DROP
You administer a Microsoft SQL Server database named Contoso. You create a stored procedure named
Sales.ReviewInvoice by running the following Transact-SQL statement:

You need to create a Windows-authenticated login named ContosoSearch and ensure that ContosoSearch canrun the Sales.ReviewInvoices stored procedure.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate
Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the
correct order.
Select and Place:

You have a database that stores information for a shipping company. You create a table named Customers by
running the following Transact-SQL statement. (Line numbers are included for reference only.)

You need to ensure that salespeople can view data only for the customers that are assigned to them.
Which Transact-SQL segment should you insert at line 07?

HOTSPOT
You manage a Microsoft SQL Server instance. You have a user named User1.
You need to grant the minimum permissions necessary to allow User1 to review audit logs.
For each action, which option should you use? To answer, select the appropriate options in the answer area.
Hot Area:

HOTSPOT
You are planning to deploy log shipping for Microsoft SQL Server and store all backups on a dedicated
fileshare.
You need to configure the servers to perform each log shipping step.
Which server instance should you configure to perform each action? To answer, select the appropriate server
instances in the dialog box in the answer area.
Hot Area: