HOTSPOT
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database
instanced. The environments host several customer databases.
You host a local database and a Stretch database that has a table named Members for one specific customer.
You need to provide the customer with information about the space used in the databases.
In the table below, identify the query that provides the required information for each database.
NOTE: Make only one selection in each column.
Hot Area:

DRAG DROP
You are the database administrator for a Microsoft SQL Server instance. You develop an Extended Events
package to look for events related to application performance.
You need to change the event session to include SQL Server errors that are greater than error severity 15.
Which five 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:

HOTSPOT
You manage a Microsoft SQL Server environment. A server fails and writes the following event to the
application event log:
MSG_AUDIT_FORCED_SHUTDOWN
You configure the SQL Server startup parameters as shown in the following graphic:

Use the drop-down menus to select the answer choice that answers each question.
NOTE: Each correct selection is worth one point.
Hot Area:

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: