SQL 2014 new features-Delayed durability

Original: SQL 2014 new features-Delayed durability

ACID is the basic property of a database. D refers to "persistence": As long as the transaction has been committed, the corresponding data modification will be saved, even if there is a power outage, and so on, when the system restarts, the data submitted before the file can still be reflected in the database.

so How is the D attribute implemented in SQL Server? SQL Server uses Write-ahead logging to ensure that logging is cured to disk prior to data logging. When a transaction is committed, the successful message is returned to the client only when the log record is cured to disk, and the corresponding data record is subsequently written to disk asynchronously. If a power failure occurs during this period, the following two scenarios occur:

1. Log has been written to disk (committed) , but the data is not written:

   after the system restarts, the redo operation is performed , and the information is written to the data file without curing the data file by reading the log.

2. Some logs have been written to disk (uncommitted), data is partially written or not written

   after the system restarts, the undo operation is performed , and the data corresponding to the uncommitted transactions is purged from the data file.

This ensures that transactions that have already been committed are not lost.

Delayed Durability

A new feature, called delayed durability (also known as lazy commit), has been introduced in SQL Server 2014 to subvert the previously mentioned concepts. By delayed durability, log records can be written asynchronously to the log file in a regular manner, avoiding log disk writes too frequently. in return for performance at the expense of durability .

Application Scenarios:

The premise of using this feature is that your app can tolerate a certain amount of data loss.

A system bottleneck occurs on the log disk.

the transaction could not be committed due to a log disk performance problem, resulting in the corresponding resource (Memory,lock , etc.) cannot release the resulting resource competition

Delayed durability has the following characteristics:

2. once a transaction commits, the data changes in the transaction are visible to other transactions (including full durable transaction and delayed durability transaction) . Please refer to isolation level http://msdn.microsoft.com/en-us/library/dn133175.aspx for details.
4. The persistence of a transaction (durability) depends on whether the log records are cured to disk.
6. log records in memory are cured to disk only if any of the following conditions occur :
   1. ) Full durable transaction made the data changes and commits.
   2. ) Executes the sp_flush_log stored procedure .
   3. Log buffer is full and logging is cured to disk .

if 1) or 2) occurs two times, then SQL Server guarantees the first Delayed durability transaction Data changes have been cured to disk.

How to use Delayed durability

Delayed Durability is a database-level feature that is disabled by default, we first have to turn this option on.

ALTER DATABASE [ddtest] SET delayed_durability = forced| allowed| Disabled

if it is forced, then all transactions within the database are forced to use delayed durability; if it is allowed, then delayed durability and full durable transaction can exist simultaneously ; if it is disabled, then delayed durability cannot be used .

When this property changes, There will also be records in the errorlog

Setting database option delayed_durability to forced for database ' Ddtest '.

Setting database option delayed_durability to allowed for database ' ddtest '.

Setting database option delayed_durability to Disabled for database ' ddtest '.

if the database delayed_durability is allowed, We can control it at the statement level, Otherwise, the database settings will be followed (if the statement is set and the database-level settings conflict, SQL Server uses the database-level settings).

Case

The data is delayed_durability set to allowed

ALTER DATABASE [ddtest] SET delayed_durability = allowed

Create a table and loop through 1000 rows of data, each of which is a separate transaction

CREATE table Ta (col int)

DECLARE @N int=0

While @n<1000

Begin

BEGIN Tran

Insert TA values (1)

commit tran with delayed durability off

Set @n+=1

Print cast (@N as varchar (1000))

End

Open Process Monitor to monitor the operation of thedatabase log files.

A total of 1012 write to the log file , that is, each commit will be cured to the log file immediately

here 's A comparison of the use of delayed durability

DECLARE @N int=0

While @n<1000

Begin

BEGIN Tran

Insert TA values (1)

commit tran with delayed durability on

Set @n+=1

Print cast (@N as varchar (1000))

End

each transaction triggered only 32 writes, greatly reducing the write operation to the log file.

Precautions

2. sql Server graceful shutdown does not cure delayed durability log records to disk, It is also said that normal shutdown can also result in data loss, and it is recommended that you execute Sp_flush_log before.