HOTSPOT
Background
You manage a Microsoft SQL Server environment that includes the following databases: DB1, DB2,
Reporting.
The environment also includes SQL Reporting Services (SSRS) and SQL Server Analysis Services (SSAS). All
SSRS and SSAS servers use named instances. You configure a firewall rule for SSAS.
Databases
Database Name:
DB1
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are inserted
into DB1 or updated each second. Inserts are made by many different external applications that your
company’s developers do not control. You observe that transaction log write latency is a bottleneck in
performance. Because of the transient nature of all the data in this database, the business can tolerate some
data loss in the event of a server shutdown.
Database Name:
DB2
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are updated or
inserted per second. You observe that the WRITELOG wait type is the highest aggregated wait type. Most
writes must have no tolerance for data loss in the event of a server shutdown. The business has identified
certain write queries where data loss is tolerable in the event of a server shutdown.
Database Name:
Reporting
Notes:
You create a SQL Server-authenticated login named BIAppUser on the SQL Server instance to support users
of the Reporting database. The BIAppUser login is not a member of the sysadmin role.
You plan to configure performance-monitoring alerts for this instance by using SQL Agent Alerts.
You need to maximize performance of writes to each database without requiring changes to existing database
tables.
In the table below, identify the database setting that you must configure for each database.
NOTE: Make only one selection in each column. Each correct selection is worth one point.
Hot Area:

Answer:

Explanation:
DB1: DELAYED_DURABILITY=FORCED
From scenario: Thousands of records are inserted into DB1 or updated each second. Inserts are made by
many different external applications that your company’s developers do not control. You observe that
transaction log write latency is a bottleneck in performance. Because of the transient nature of all the data in
this database, the business can tolerate some data loss in the event of a server shutdown.
With the DELAYED_DURABILITY=FORCED setting, every transaction that commits on the database is
delayed durable.
With the DELAYED_DURABILITY= ALLOWED setting, each transaction’s durability is determined at the
transaction level.
Note: Delayed transaction durability reduces both latency and contention within the system because:
* The transaction commit processing does not wait for log IO to finish and return control to the client.
* Concurrent transactions are less likely to contend for log IO; instead, the log buffer can be flushed to disk in
larger chunks, reducing contention, and increasing throughput.
DB2: ALLOW_SNAPSHOT_ISOLATION ON and READ_COMMITTED_SNAPSHOT ON
Snapshot isolation enhances concurrency for OLTP applications.
Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option
before it is used in transactions.The following statements activate snapshot isolation and replace the default READ COMMITTED behavior with
SNAPSHOT:
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default
READ COMMITTED isolation level.
From scenario: The DB2 database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of
records are updated or inserted per second. You observe that the WRITELOG wait type is the highest
aggregated wait type. Most writes must have no tolerance for data loss in the event of a server shutdown. The
business has identified certain write queries where data loss is tolerable in the event of a server shutdown.

https://msdn.microsoft.com/en-us/library/dn449490.aspx
https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx