You administer a Microsoft SQL

Server 2016 database named Orders.

Users report that during peak usage periods, certain operations are taking more time than expected. Your initial analysis suggests that blocking is the cause.

You need to gather more data to be able to determine which p

rocesses are being blocked and to identify the root cause.

What should you do?

A. Start a trace using SQL Server Profiler to catch the Lock: Deadlock event.

B. Use sp_configure to set the blocked process threshold. Start a trace using SQL Server Profiler

to catch the Blocked Process Report event.

C. Schedule a SQL Agent job to run every 60 seconds and insert the results of executing the sys.dm_os_wait_stats DMV into a table.

D. Use System Monitor to catch the Lock Waits/sec event.

You administer a Microsoft SQL

Server 2016 database named Contoso on a server named Server01.

You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales.

What should you create?

A. An Alert

B. A Resource Pool

C. An Extended Event

session

D. A Server Audit Specification

E. A SQL Profiler Trace

F. A Database Audit Specification

G. A Policy

H. A Data Collector Set

You administer a Microsoft SQL S

erver 2016 server.

The MSSQLSERVER service uses a domain account named CONTOSO\SQLService.

You plan to configure Instant File Initialization.

You need to ensure that Data File Autogrow operations use Instant File Initialization.

What should you do? Cho

ose all that apply.

A. Restart the SQL Server Agent Service.

B. Disable snapshot isolation.

C. Restart the SQL Server Service.

D. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.

E. Add the CONTOSO\SQLServi

ce account to the Server Operators fixed server role.

F. Enable snapshot isolation.

How To Enable Instant File Initialization

Open Local Security Policy and go to Local Policies -> User Rights Assignment.

Double click Perform Volume

Maintenance Tasks and add your SQL Server database engine service account.

Restart the SQL Server service using SQL Server Configuration Manager and this setting should now be enabled.

References: