Delayed Durability
SQL Server guarantees full transaction durability by default.
If the system crashes for some reason after SQL Server confirms a successful commit, the changes made by the transaction are visible after the system returns to an operable status even if the transaction operations had not been written to disk prior to the system failure.
To make this possible, SQL Server uses write-ahead logging to first hold data changes in a log buffer. Once the transaction commits, the log buffer flushes the transaction log and writes the modifications first to the data cache, and then permanently to the database on disk. The transaction log contains not only changes to data, but also page allocations and de-allocations, and changes to indexes.
Each log record includes a unique log sequence number (LSN) to that every record change that belongs to the same transaction can be rolled back if necessary. A change is never made to the database without confirming that it already exists in the transaction log. At that point, SQL Server reports a successful commit and the transaction cannot be rolled back.
Start to write transaction -> log buffer -> Transaction log (with LSN) -> Data cache (by committing) -> Database on disk
What if a failure occurs after the change is written to the transaction log, but before SQL Server writes the change to the database? In this case, the transaction is still durable because you can recreate the change from the transaction log if necessary.
SQL Server also supports delayed durable transactions, also known as lazy commits. By using this approach, SQL Server can process more concurrent transactions with less contention for log IO, thereby increasing throughput.
Once the transaction is written to the transaction log, SQL Server reports a successful transaction and any changes that it made are visible to other transactions. However, all transaction logs remain in the log buffer until the buffer is full or a buffer flush event occurs, at which point the transaction is written to disk and becomes durable.
A buffer flush occurs when a fully durable transaction in the same database commits or a manual request to execute sp_flush_log is successful. Delayed durability is useful when you are willing to trade potential data loss for reduced latency in transaction log writes and reduced contention between transactions. The benefit is that you can reduce transaction execution time by implementing delayed durability.
Start to write transaction -> log buffer -> Transaction log (Delayed durability) -> Data cache (by committing) -> Database on disk
You can enable a database to support delayed transaction durability and then force or disable delayed transaction durability at the transaction level as an option of the COMMIT statement.
When you create a memory-optimized table, you must decide how SQL Server should manage durability of the data. If you do not specify the durability option explicitly when you create a memory-optimized table, it is durable by default. To explicitly define a durable table, use the SCHEMA_AND_DATA durability option.
CREATE TABLE Examples.Order_IM_Durable
( OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
OrderDate DATETIME NOT NULL, CustomerCode NVARCHAR(5) NOT NULL )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA); GO
By choosing Non-durable, you instruct SQL Server to persist only the table schema, but not the data. This option is most appropriate for use cases in which data is transient, such as an application’s session state management, or ETL staging. SQL Server never writes a non-durable table’s data changes to the transaction log. To define a non-durable table, use the SCHEMA_ONLY durability.
CREATE TABLE Examples.Order_IM_Nondurable
( OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED, OrderDate DATETIME NOT NULL, CustomerCode NVARCHAR(5) NOT NULL )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_ONLY); GO
Because non-durable memory-optimized tables do not incur logging overhead, transactions writing to them run faster than write operations on durable tables. However, to optimize performance of durable memory-optimized tables, configure delayed durability at the database or transaction level. Just as with disk-based tables, delayed durability for a memory-optimized table reduces the frequency with which SQL Server flushes log records to disk and enables SQL Server to commit transactions before writing log records to disk.
If you set delayed durability at the database level, every transaction that commits on the database is delayed durable by default, although you can override this behavior at the transaction level. Similarly, if the database is durable, you can configure the database to allow delayed durable transactions and then explicit define a transaction as delayed durable. If you prefer, you can disable delayed durability and prevent delayed durable transactions entirely regardless of the transaction’s commit level. You can also specify delayed durability for a natively compiled stored procedure.
ALTER DATABASE DB_IMOLTP SET DELAYED_DURABILITY = FORCED; BEGIN TRANSACTION; INSERT INTO Examples.Order_IM_Hash
(OrderId, OrderDate, CustomerCode) VALUES (1, getdate(), 'cust1'); COMMIT TRANSACTION WITH (DELAYED_DURABILITY = OFF); GO
ALTER DATABASE DB_IMOLTP SET DELAYED_DURABILITY = ALLOWED; BEGIN TRANSACTION; INSERT INTO Examples.Order_IM_Hash (OrderId, OrderDate, CustomerCode) VALUES (2, getdate(), 'cust2'); COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON); CREATE PROCEDURE Examples.OrderInsert_NC_DD @OrderID INT, @CustomerCode NVARCHAR(10) WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (DELAYED_DURABILITY = ON,
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'English') DECLARE @OrderDate DATETIME = getdate(); INSERT INTO Examples.Order_IM
(OrderId, OrderDate, CustomerCode) VALUES (@OrderID, @OrderDate, @CustomerCode); END; GO ALTER DATABASE DB_IMOLTP SET DELAYED_DURABILITY = DISABLED;
Example:
Answer:
