You administer a Microsoft SQL Server 2016 server.

When transaction logs grow, SQL Server must send an

email message to the database administrators.

You need to configure SQL Server to send the email messages.

What should you configure?

A. SQL Mail

B. An Extended Events session

C. Alerts and operators in SQL Server Agent

D. Policies under Policy-Based Ma

nagement

Operators are aliases for people or groups that can receive electronic notification when jobs have completed or alerts have been raised. The SQL Server Agent service supports the notification of administrators through operators.

Operators enable notification and monitoring capabilities of SQL Server Agent.

References: https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail

You administer a Microsoft SQL Server 2016 server.

One of the databases on the server supports a highly active OLTP application.

Users report abnormally long wait times when they submit data into the application.

You need to identify which queries are taking longer than 1 second to run over an extended p

eriod of time.

What should you do?

A. use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000.

B. Use sp_configure to set a value for blocked process threshold. Create an

extended event session.

C. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step.

D. Run the sp_who command from a query window.

E. Run the DBCC TRACEON 1222 command fro

m a query window and review the SQL Server event log.

You administer a Microsoft SQL Server 2016 server.

You plan to deploy new features to an application.

You need to evaluate existing and potential cluster

ed and non-clustered indexes that will improve performance.

What should you do?

A. Query the sys.dm_db_index_usage_stats DMV.

B. Query the sys.dm_db_missing_index_details DMV.

C. Use the Database Engine Tuning Advisor.

D. Query the sys.dm_db_missing_inde

x_columns DMV.

The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. You can use the Database Engine Tuning Advisor to select and create an optimal set of

indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL Server. Using the DTA, you can perform the following tasks.

Troubleshoot the performance of a specific problem query

Tune

a large set of queries across one or more databases

Perform an exploratory what-if analysis of potential physical design changes

Manage storage space

References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/database-engine-tuni

ng-advisor