Introduction

When SQL Server commits a transaction, it doesn’t just update data in memory. It also writes a record to the transaction log on disk. Only after this log is flushed does SQL Server confirm the commit to the client. This extra step ensures that your data is safe. Even if the server crashes a second later, the transaction can still be recovered because it was saved to disk.

That protection comes with a cost. Writing to disk is slower than working with memory. On systems that handle thousands of small transactions per second — like event logging or telemetry — waiting for each log flush adds delay. These delays pile up. Even when your CPU and memory are idle, the disk can become a bottleneck. As more commits wait for the log to flush, the entire system starts to feel sluggish. The slowdown is often hard to trace because everything else looks healthy.

To solve this, SQL Server includes a feature called delayed durability, introduced in SQL Server 2014. This setting changes how commits work behind the scenes. Instead of waiting for the log to be written to disk, SQL Server places it in memory and returns a commit confirmation right away. The log is flushed later — either after a short delay or when memory fills up. This simple change can dramatically improve performance on busy systems, especially those not optimized for fast disk writes. Delayed durability isn’t turned on by default because it carries a small risk of data loss during a crash. But for many workloads, that trade-off is worth it. Knowing when and how to use it lets you unlock better performance without rewriting code or buying new hardware.

What is Delayed Durability?

Delayed durability is a feature in SQL Server that speeds up transaction commits. Normally, when a transaction commits, SQL Server writes its log entry to disk before telling the client it’s done. This makes the transaction durable. It means the change is safe even if the system crashes right after. But writing to disk takes time. On a busy system, that wait can slow everything down.

When delayed durability is turned on, SQL Server changes that behavior. It writes the log entry to memory first and confirms the commit right away. The disk write happens a moment later or when the memory buffer gets full. This avoids waiting on slow disk IO and helps the system handle more transactions per second.

There is a trade-off. If the server crashes before the log flush, you may lose some recent transactions. SQL Server told the client they were done, but they never made it to disk. That risk is small, but real. This feature works best for systems where you can afford to lose a few seconds of data — like logging systems, telemetry, or other non-critical workloads. Delayed durability doesn’t break correctness. It only changes when data is made permanent. If used carefully, it offers a simple way to boost performance without changing your database design.

Normal Durability Path (Default behavior):

1. Transaction generates log records.
2. These are placed in the log buffer.
3. SQL Server waits until these log records are flushed to the log file on disk.
4. Only after flush, the commit is acknowledged to the client (Commit complete).

Delayed Durability Path (When enabled):

1. Transaction generates log records.
2. They're placed in the log buffer.
3. But SQL Server does NOT wait for log flush. It immediately confirms commit to the client.
4. The flush happens later (asynchronously), either when the buffer is full, or after a timeout, or a manual flush is issued by sp_flush_log command

How to Enable delayed durability

Delayed durability can be configured either at the database level or at the transaction level. If you want to enable it for all transactions in a database, you can run a simple ALTER DATABASE command and set the durability to FORCED. This means every COMMIT in that database becomes delayed by default. Alternatively, if you prefer a more targeted approach, you can leave the database setting as ALLOWED and apply delayed durability only to specific transactions. This is done by appending the WITH (DELAYED_DURABILITY = ON) clause to the COMMIT statement. This flexibility allows you to optimize just the critical parts of your workload — for example, log insertions or event telemetry — while leaving other transactions fully durable and safe.

This is the command to enable it at database level.

ALTER DATABASE DelayedDurabilityTest
SET DELAYED_DURABILITY = DISABLED|ALLOWED|FORCE;

DISABLED

This is the default setting. It means delayed durability is completely turned off for the database. Even if you try to use it in a specific transaction (like using WITH (DELAYED_DURABILITY = ON)), SQL Server will ignore that and use normal durability. Every commit will wait for the log to be flushed to disk. Use this when data safety is more important than performance.

ALLOWED

This allows you to decide at the transaction level whether to use delayed durability or not. Some transactions can use it, others can skip it. You control this with the WITH (DELAYED_DURABILITY = ON) option during commit. It gives flexibility. Use this if you want to speed up only selected operations like event logging or background inserts, but keep critical data fully durable.

FORCED

This makes all transactions in the database use delayed durability — no matter what. Even if you don’t specify anything at the transaction level, SQL Server will treat all commits as delayed durable. Use this only when performance is a top priority and you’re okay with a small risk of data loss in case of a crash.

If you want to set it at transaction level, use COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON)

BEGIN TRANSACTION

-- Do your inserts, updates, etc.

COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON);

You cannot use ROLLBACK TRANSACTION WITH (DELAYED_DURABILITY = ON).

What You Gain By Enabling Delayed Durability

The main benefit of delayed durability is reduced latency at the point of COMMIT. In normal durability mode, every transaction must wait until its log records are physically written to disk. That introduces a delay — even for the smallest of transactions. On a quiet system, that delay might be just a few milliseconds. But on a busy system, with thousands of small transactions per second, that wait time starts stacking up fast.

With delayed durability, SQL Server changes this game. It acknowledges the commit as soon as the log record is in memory, without waiting for it to reach disk. This means transactions finish faster. The user or application sees an immediate response, and the server is free to handle more work. It’s like skipping the queue at the bank — you’re done before others even get to the counter.

This benefit is especially noticeable in workloads that involve frequent small writes — things like telemetry data, event logging, IoT sensor tracking, or financial tick data. In such systems, each insert or update is tiny, but happens thousands of times per second. Waiting for a disk flush on every one of them creates a huge bottleneck. By removing that disk dependency from the critical path, delayed durability unlocks a level of throughput that would otherwise require expensive storage upgrades.

Some clear wins you get:

• Faster commits — especially on write-heavy, disk-bound systems

• Higher throughput under concurrency — many writers don’t have to wait

• Better resource utilization — reduces pressure on the I/O subsystem without upgrading storage

What Are the Risks?

The biggest risk of using delayed durability is potential data loss. When you enable this feature, SQL Server confirms that a transaction is committed as soon as the log record is written to memory — not to disk. If the server crashes or loses power before that memory buffer is flushed, those transactions are gone. There’s no way to recover them because they were never persisted to stable storage.

This creates a small but real window of vulnerability, typically lasting a few milliseconds to a few seconds, depending on system load, memory pressure, and the size of the log buffer. During this period, you are trusting that the system will remain stable long enough to flush the log. In most cases, this works out fine. But if absolute consistency is your goal, you have to account for the risk.

For this reason, SQL Server delayed durability is not recommended for critical systems where every row matters. Think of banking systems, payment processors, trading ledgers, inventory control, or any workload where one lost transaction could result in compliance issues, financial loss, or customer impact. In these environments, durability must be guaranteed, even if it comes at the cost of performance.

That said, many modern applications are designed to be resilient by nature. If you’re logging telemetry from IoT devices, tracking page views, capturing metrics from an API, or handling social media events, you likely don’t need perfect durability. A few lost messages won’t break the system — and the performance gains from skipping disk I/O could be massive. In these use cases, delayed durability becomes not just acceptable, but a smart optimization.

Here’s a quick recap of the risks:

• Data loss possible if SQL Server crashes before flushing the log buffer

• Risk window ranges from milliseconds to a few seconds, based on system activity

• Not suitable for financial systems, audits, or anything with strict durability requirements

Monitoring and Expectations

Once delayed durability is enabled, monitoring becomes essential to verify how effectively it's working in your SQL Server environment. The simplest and quickest way to check the current delayed durability setting is by querying the sys.databases DMV. It includes the column delayed_durability_desc, clearly indicating if the setting is DISABLED, ALLOWED, or FORCED.

For real-time monitoring, SQL Server provides dedicated performance counters under the SQLServer:Database Replica category (in Availability Groups) and SQLServer:Databases for standalone databases. Specifically, look at counters such as "Log Flushes/sec", "Log Flush Waits/sec", and "Transaction Delay". A noticeable reduction in "Log Flush Waits/sec" or "Transaction Delay" indicates that delayed durability is positively impacting commit latency.

Additionally, monitoring wait statistics via the DMV sys.dm_os_wait_stats can provide useful indirect evidence. If delayed durability is effective, the WRITELOG wait type should decrease significantly, confirming that transactions spend less time waiting for the transaction log flush to disk.

Extended events aren't directly helpful here because SQL Server does not provide an extended event specifically named for delayed durability lost commits. Instead, performance counters and wait stats remain your most reliable and practical options for observing how delayed durability impacts your environment.

Combining these tools—DMVs, performance counters, and wait stats—gives a comprehensive picture of the performance gains and the associated risks, allowing you to confidently manage your delayed durability strategy.

When Should You Use It?

Delayed durability is best suited for workloads where speed is more critical than guaranteed persistence. If your system ingests large volumes of data from sensors, logs, or user events, and you already have mechanisms to replay or re-ingest in the event of a failure, delayed durability can give you tremendous performance gains. On the other hand, if you're dealing with transactional data, such as orders, payments, or account balances, then even a small risk of loss is unacceptable. In such cases, it's better to keep durability fully enforced. Delayed durability is not a blanket feature to boost performance everywhere — it should be applied deliberately to places where durability is already being handled elsewhere or where the data loss impact is negligible.

Summary

Delayed durability is one of those SQL Server features that few people talk about, yet it has the potential to unlock serious performance wins in the right scenarios. It’s easy to enable, safe when used properly, and incredibly effective for write-heavy systems that can’t afford to wait on every single disk flush. But like many performance features, it requires understanding and intent. Knowing what it does and where it fits gives you an additional lever to tune SQL Server beyond indexes and query plans. If you’re building a high-throughput system and have non-critical data to ingest quickly, delayed durability might be the hidden switch you didn’t know you needed.