writelog_如何处理SQL Server WRITELOG等待类型

writelog

The WRITELOG wait type is one of those wait types that can often be seen quite frequently on SQL Server, and that can cause a lot of headaches for DBAs. The WRITELOG wait time represents the time that accumulates while waiting for the content of the transaction log cache to be flushed to the physical disk that stores the transaction log file. To understand better the WTITELOG wait type, there are some basics of SQL Server mechanism for storing the data in the transaction log file is to be explained first

WRITELOG等待类型是在SQL Server上经常看到的等待类型之一，这可能使DBA感到头疼。 WRITELOG等待时间表示在等待将事务日志缓存的内容刷新到存储事务日志文件的物理磁盘时所累积的时间。 为了更好地理解WTITELOG等待类型，首先将解释一些SQL Server机制的基本知识，该机制用于将数据存储在事务日志文件中

When SQL Server has to store data in transaction log file, it doesn’t do that directly by writing the data straight on the disk where the transaction log file is stored. Instead, all data is serially written to a Log cache (often referred to as a Log buffer or Log block) which is in-memory structure. Moreover, the SQL Server OS must comply with Atomicity, Consistency, Isolation, and Durability (ACID) principle, so flushes the entire log cache into the transaction log file that is stored in the disk subsystem or rolled back if required. The size of the log cache is between the 512 B and 64 KB.

当SQL Server必须将数据存储在事务日志文件中时，它不会直接通过将数据直接写入存储事务日志文件的磁盘上直接执行此操作。 而是将所有数据串行写入内存结构的日志缓存（通常称为日志缓冲区或日志块）。 此外，SQL Server OS必须遵守原子性，一致性，隔离性和持久性（ ACID ）原则，因此将整个日志缓存刷新到存储在磁盘子系统中的事务日志文件中，或者在需要时回滚。 日志缓存的大小在512 B和64 KB之间。

Often what the WRITELOG wait type is and when it starts to accumulate is misunderstood based on a belief that it accumulates when SQL Server is writing the data in the Log cache, or while data is sitting in the log cache and waiting to be flushed to the transaction log file. However, neither of those two is correct. SQL Server starts to register WRITELOG wait type at the moment when the log cache starts to be flushed to a transaction log file.

通常，基于以下信念，人们会误解WRITELOG等待类型以及它何时开始累积：SQL Server在日志服务器中将数据写入日志缓存时，或者当数据正坐在日志缓存中并等待刷新到缓存时会累积。事务日志文件。 但是，这两个都不正确。 当日志缓存开始刷新到事务日志文件时，SQL Server开始注册WRITELOG等待类型。

So WRITELOG is not related directly to SQL Server – Log cache relation, or to the Log cache itself. It is strictly related to a log cache and transaction log file communication. The moment data starts to be flushed to a transaction log file, the WRITELOG wait type is registered and its time accumulates until the Log cache completes flushing data from memory to a transaction log file on the disk drive. What is evident from this explanation is that it is I/O operation related to the physical drive is the most important parameter related to the WRITELOG wait type. That directly means that as faster the disk subsystem is, the lower data writing latency in transaction log file is.

因此，WRITELOG与SQL Server –日志缓存关系或日志缓存本身没有直接关系。 它与日志缓存和事务日志文件通信严格相关。 从开始将数据刷新到事务日志文件开始，即注册WRITELOG等待类型，并累积时间，直到日志缓存完成将数据从内存刷新到磁盘驱动器上的事务日志文件为止。 从该解释中可以明显看出，与物理驱动器相关的I / O操作是与WRITELOG等待类型相关的最重要的参数。 这直接意味着，磁盘子系统越快，事务日志文件中的数据写入延迟就越短。

Therefore, for any kind of transactional workload writing to the SQL Server transaction log, I/O performance is equally important for the data throughput and the application responsiveness. While being the most common, I/O subsystem performance is not the only cause of the excessive WRITELOG, as SQL Server engine itself has some hard limitations related to the amount of I/O operations that the Log manager can issue before receiving flushing-complete info. So, some prerequisites should be fulfilled to avoid excessive WRITELOG wait types, as well as any optimizations that should be performed

因此，对于将任何类型的事务性工作负载写入SQL Server事务日志，I / O性能对于数据吞吐量和应用程序响应性都同样重要。 虽然I / O子系统性能是最常见的，但不是导致WRITELOG过多的唯一原因，因为SQL Server引擎本身具有一些严格的限制，与日志管理器在接收到刷新完成之前可以发出的I / O操作的数量有关信息。 因此，应满足一些先决条件，以避免过多的WRITELOG等待类型，以及应执行的任何优化

磁盘子系统的优化和限制 (The disk subsystem optimization and limitation)

Transaction log related performance is strongly related to Disk subsystem I/O performance, and it is not unusual that this factor is often the cause for degraded SQL Server performance and high values of WRITELOG wait types. There are some rules for production systems that should be fulfilled, especially in situations where there are excessive WRITELOG wait types indicated

与事务日志相关的性能与磁盘子系统I / O性能密切相关，并且该因素通常是导致SQL Server性能下降和WRITELOG等待类型的高值的原因，这并不罕见。 有一些生产系统应满足的规则，特别是在指示了过多WRITELOG等待类型的情况下

The Disk subsystem must provide adequate I/O performance to ensure fast response to I/O requests issued against the transaction log

Improperly sized disk storage or inadequately configured one are the main reasons behind performance issues related to I/O operations. Quite often the transaction log file (.ldf file) is stored on the same physical drive as SQL Server data file (.mdf file), forcing those two to share the performance of the disk subsystem, causing them to affect each other. Therefore, it is recommended to separate the transaction log file on a separate physical drive from the data file. Often a misinterpretation exists that it is enough to separate the data file and the log file to some different partitions of the same physical drive. This is not a solution, though, as both files still sharing the performance limits of the same physical drive. Therefore, due to the nature of how the data flush in the transaction log file and writing to a log file is sequential in its nature, using the separate physical high-speed drives for storing the transaction log file could significantly increase the performance and thus reduce the WRITELOG wait type

However, physical I/O performance cannot be expanded without limits, so some factors implicitly affect the disk subsystem I/O such as SQL Server Replication (transactional replication), transaction log backup operations, SQL Server mirroring, etc., which makes designing and optimizing disk subsystem even more difficult. Some general recommendations are to design the disk subsystem in a way that can ensure it to sustain I/O response time below 5 milliseconds in the worst case, as a general rule of thumb.

磁盘子系统必须提供足够的I / O性能，以确保快速响应针对事务日志发出的I / O请求

大小不正确的磁盘存储或配置不充分的磁盘存储是与I / O操作相关的性能问题背后的主要原因。 通常，事务日志文件（.ldf文件）与SQL Server数据文件（.mdf文件）存储在同一物理驱动器上，从而迫使这两个共享磁盘子系统的性能，从而使它们相互影响。 因此，建议将单独的物理驱动器上的事务日志文件与数据文件分开。 通常存在一种误解，即足以将数据文件和日志文件分离到同一物理驱动器的某些不同分区。 但是，这不是解决方案，因为两个文件仍然共享同一物理驱动器的性能限制。 因此，由于在事务日志文件中刷新数据并写入日志文件的本质是顺序的，因此使用单独的物理高速驱动器存储事务日志文件可能会大大提高性能，从而降低性能。 WRITELOG等待类型

但是，无法无限扩展物理I / O性能，因此某些因素隐式影响磁盘子系统I / O，例如SQL Server复制（事务复制），事务日志备份操作，SQL Server镜像等，这使得设计和优化磁盘子系统更加困难。 一般的经验法则是，建议以某种方式设计磁盘子系统，以确保在最坏的情况下将I / O响应时间维持在5毫秒以下。

日志管理器限制 (The Log manager limitations)

As mentioned already, the SQL Server OS itself has a few hard limitations related to the amount of I/O operations. There are two particular limitations of interest for this article: Outstanding log I/O limit and Outstanding I/O limit.

如前所述，SQL Server OS本身具有一些与I / O操作数量有关的硬限制。 本文有两个受关注的特殊限制： 未完成的日志I / O限制和未完成的I / O限制 。

As already stated, both limitations are hard limits that do not allow any changes or settings by DBAs. To maintain the data integrity, the SQL Server OS imposes an I/O limit to the Log manager by limiting the amount of I/O for write to log operations that are started but not yet completed. The moment limits are reached, the Log manager must wait for acknowledging of the outstanding I/O before it is granted to issue any new I/O operations (writing) to the transaction log file. Both limitations, Outstanding log I/O limit and Outstanding I/O limit are imposed at a database level.

如前所述，这两个限制都是硬性限制，不允许DBA进行任何更改或设置。 为了保持数据完整性，SQL Server OS通过限制已开始但尚未完成的写入日志操作的I / O数量，对日志管理器施加了I / O限制。 在达到限制的时刻，日志管理器必须等待未完成的I / O的确认，然后才能授予对事务日志文件的任何新I / O操作（写入）。 这两个限制（未完成的日志I / O限制和未完成的I / O限制）都在数据库级别施加。

For an in-depth understanding of those limitations as a frequent cause of the high WRITELOG values, see the article Diagnosing Transaction Log Performance Issues and Limits of the Log Manager

要深入了解这些限制是WRITELOG值较高的常见原因，请参阅诊断事务日志性能问题和日志管理器限制

查询优化 (Query optimization)

Writing queries is not a hard task in and of itself. But when it comes to optimization of a query for performance things can become more challenging. Let’s take two elementary INSERT queries, almost identical, that both have the same final result, to help us understand the difference between the poor and optimal optimization

编写查询本身并不是一项艰巨的任务。 但是，当要优化性能查询时，事情可能会变得更具挑战性。 让我们以两个几乎相同的基本INSERT查询为例，它们都具有相同的最终结果，以帮助我们了解较差的优化和最优的优化之间的区别

Query 1

查询1

USE [AdventureWorks2014]
GO
DECLARE @c INT
SET @c = 1
   WHILE @c < 100000
   BEGIN
	INSERT INTO [HumanResources].[EmployeePayHistory]
           ([BusinessEntityID]
           ,[RateChangeDate]
           ,[Rate]
           ,[PayFrequency]
           ,[ModifiedDate])
	VALUES
           (@c
           ,'2009-03-07 00:00:00.000'
           ,53.232
           ,4
           ,'2018-06-30 00:00:00.000')
       SET @c = @c + 1
   END

The above query inserts 100,000 rows of data into a table. What is specific for this query is that it is using an implicit transaction. The time needed for that query to execute on a test machine is 528 seconds with a WRITELOG wait time of 507 seconds. Such queries are often accompanied with high wait times for the WRITELOG wait type.

上面的查询将100,000行数据插入到表中。 此查询的特定之处在于它正在使用隐式事务。 该查询在测试计算机上执行所需的时间为528秒，其中WRITELOG等待时间为507秒。 此类查询通常伴随着WRITELOG等待类型的高等待时间。

These high wait times can occur because the SQL Server OS flushes the Log cache into a transaction log file in cases when the transaction commits or when the Log cache is filled to its maximum size. In the query above, since an implicit transaction is used, the Log cache flushes into a transaction log file on every commit of data. That means the Log cache is flushed on every insert, which is 100,000 times.

这些高等待时间可能会发生，因为在事务提交或日志缓存已填充到其最大大小的情况下，SQL Server OS会将日志缓存刷新到事务日志文件中。 在上面的查询中，由于使用了隐式事务，因此每次提交数据时，日志高速缓存都会刷新到事务日志文件中。 这意味着在每次插入时都会刷新日志缓存，这是100,000次。

Query 2

查询2

USE [AdventureWorks2014]
GO
DECLARE @c INT
SET @c = 1
BEGIN TRAN
   WHILE @c < 100000
   BEGIN
	INSERT INTO [HumanResources].[EmployeePayHistory]
           ([BusinessEntityID]
           ,[RateChangeDate]
           ,[Rate]
           ,[PayFrequency]
           ,[ModifiedDate])
	VALUES
           (@c
           ,'2009-03-07 00:00:00.000'
           ,53.232
           ,4
           ,'2018-06-30 00:00:00.000')
       SET @c = @c + 1
   END 
COMMIT

The second query performs the same as the first, but in this case, the query is written to use an explicit transaction. The whole WHILE loop is now folded in an explicit transaction (BEGIN TRAN – COMMIT is highlighted), meaning that commit has completed once after the query executes entirely. That also means that the Log cache flushes into the transaction log file only after it became full. By optimizing the query in this way, the number of Log cache flushes to transaction log file are significantly reduced which has as a consequence much faster query execution with significantly reduced WRITELOG wait type time. In this particular case, the query executes in 4 seconds with a slightly above 1 second of WRITELOG wait time.

第二个查询的执行与第一个查询相同，但是在这种情况下，该查询被编写为使用显式事务。 现在，整个WHILE循环都折叠在一个显式事务中（BEGIN TRAN – COMMIT突出显示），这意味着在查询完全执行之后，提交已完成一次。 这也意味着日志高速缓存仅在变得满后才刷新到事务日志文件中。 通过以这种方式优化查询，可以大大减少刷新到事务日志文件的日志缓存的次数，从而大大加快查询的执行速度，并显着减少WRITELOG等待类型的时间。 在这种情况下，查询将在4秒内执行，稍稍超过WRITELOG等待时间的1秒。

Therefore, it is now evident that when high WRITELOG values are experienced on SQL Server, the knee-jerk reaction is often that it is related to something with the disk subsystem. But as we’ve learned, this is not always the case. Check the queries that are causing high WRITELOG wait times and optimize them if possible to avoid committing data too often

因此，现在很明显，当在SQL Server上遇到较高的WRITELOG值时，弯腰的React通常是与磁盘子系统有关。 但是，据我们了解，情况并非总是如此。 检查导致较长WRITELOG等待时间的查询，并在可能的情况下对其进行优化，以避免过于频繁地提交数据

SQL Server延迟的持久性 (SQL Server Delayed durability)

Starting with SQL Server 2014 the DELAYED_DURABILITY is added as a new option for transaction commits with one single aim: a tradeoff of transaction durability for better performance. Using this option in SQL Server where excessive WRITELOG wait type is present could be significant.

从SQL Server 2014开始，将DELAYED_DURABILITY作为一种新的事务提交选项添加，以实现一个目标：权衡事务持久性以获得更好的性能。 在存在过多WRITELOG等待类型SQL Server中使用此选项可能很重要。

To understand delayed durability, let’s first provide a short background. SQL Server uses write-ahead transaction log (WAL) to record data modifications to disk, and WAL grants ACID properties that data modifications are not written to the physical disk before it writes the accompanied log record to disk. As data modifications are never made directly to disk, when data modification occurs, it performs on data stored in the buffer cache. A page with modified data that is stored in the buffer cache and is not yet flushed to the disk is called a “dirty page.” The page stays there until the database checkpoint occurs or to allow the log cache to be used by the new data page. When the data modifications occur in the buffer cache, the associated data page that contains modification creates in the Log cache as well. The data page created in the Log cache must always be flushed to disk before flushing the dirty page to disk to maintain the SQL Server ability to roll back data in case of failure. That means that all data must be written to the transaction log file first, before allowing those data to be committed and flushed into the data file on the disk

为了了解延迟的耐用性，让我们首先简要介绍一下背景。 SQL Server使用预写事务日志（WAL）将数据修改记录到磁盘，并且WAL授予ACID属性，即在将附带的日志记录写入磁盘之前，数据修改不会写入物理磁盘。 由于永远不会直接对磁盘进行数据修改，因此，发生数据修改时，它将对存储在缓冲区高速缓存中的数据执行操作。 存储在缓冲区高速缓存中但尚未刷新到磁盘的具有已修改数据的页面称为“脏页面”。 该页面将一直停留在那里，直到出现数据库检查点或允许新数据页面使用日志高速缓存为止。 当数据修改发生在缓冲区缓存中时，包含修改的关联数据页也会在日志缓存中创建。 在将脏页刷新到磁盘之前，必须始终将在日志缓存中创建的数据页刷新到磁盘，以保持SQL Server在发生故障时回滚数据的能力。 这意味着必须先将所有数据写入事务日志文件，然后再将这些数据提交并刷新到磁盘上的数据文件中

Having that in mind, for systems that are experiencing performance issues caused by writes to the transaction log file, SQL Server delayed Durability provides an option to drop the Durability from the ACID requirements for some data by allowing the dirty pages to be flushed to disk before flushing the associated log cache. That practically means that SQL Server now maintains some level of tolerance for some moderate data loss.

考虑到这一点，对于遇到由于写入事务日志文件而导致的性能问题的系统，SQL Server延迟持久性提供了一个选项，可以通过允许将脏页刷新到磁盘之前，将某些数据的持久性从ACID要求中删除。刷新关联的日志缓存。 实际上，这意味着SQL Server现在对某些中等程度的数据丢失保持了一定的容忍度。

So the SQL Server Delayed Durability option allows dirty pages to be flushed to disk as if the Log cache is flushed before them. The logic behind abandoning durability in favor of performance is the optimistic judgment that nothing deleterious could happen and that the Log cache is going to be flushed eventually. Therefore, instead of flushing the Log cache on every commit, the data continues to be stored in the Log cache until it reaches the maximum size of 60 KB or when the explicit sys.sp_flush_log is issued. Then it is flushed to disk reducing the I/O operations in this way, significantly in some cases. By reducing the log I/O contention, a significant reduction of the WRITELOG waits could be expected in some cases, such as committing data too often query design

因此，SQL Server的“延迟持久性”选项允许将脏页刷新到磁盘，就好像在其之前刷新日志缓存一样。 放弃耐用性而改用性能背后的逻辑是，乐观的判断是不会发生有害的事情，并且Log缓存最终将被清空。 因此，数据将继续存储在日志缓存中，直到达到60 KB的最大大小为止，或者在发出显式sys.sp_flush_log时不再刷新每次提交的日志缓存。 然后，将其刷新到磁盘，以这种方式减少I / O操作，在某些情况下会大大减少。 通过减少日志I / O争用，在某些情况下可以预期WRITELOG等待的显着减少，例如过于频繁地提交数据而查询设计

What have to be noted here is that when using the delayed transaction option, there is no guarantee that some data won’t be lost in case of a catastrophic event, like power outage or SQL Server crash

这里必须注意的是，当使用延迟事务选项时，不能保证在发生灾难性事件（例如停电或SQL Server崩溃）时不会丢失某些数据。

The delayed durability option allows control at the three different levels, at the database level, at the transaction (COMMIT) level and at an Atomic block level (In-memory OLTP Natively Compiled Stored Procedures)

延迟持久性选项允许在三个不同级别上进行控制，分别在数据库级别，事务（COMMIT）级别和原子块级别（内存OLTP本地编译的存储过程）中进行控制

Starting with SQL Server 2014 and newer, there are two levels of controlling the transaction durability:

从SQL Server 2014及更高版本开始，控制事务持久性有两个级别：

• Full transaction durability – This is the standard (default) SQL Server settings that grants full durability for all transactions in the database. This setting matches the settings of all pre-SQL Server 2014 versions 完全事务持久性 –这是标准（默认）SQL Server设置，可授予数据库中所有事务的完全持久性。 此设置与所有SQL Server 2014之前版本的设置匹配
  
  
  This option is recommended option especially if there is a zero tolerance for data loss or when the system is not clogged anyhow with transaction log write latency
  建议使用此选项，尤其是在数据丢失容忍度为零或系统未因事务日志写入延迟而阻塞的情况下
  
  
  It must be stated that some transactions are hardcoded as fully durable transactions, and the delayed durability option cannot affect those transactions regardless of the settings. So delayed durability is not applicable in any way to any cross-database transactions, Microsoft Distributed Transaction Control (MSDTC) transactions, transactions related to Change Tracking, Change Data Capture, Transaction Replication and File tables operations, Log shipping and Log backup, and system transactions regardless of the delayed durability settings
  必须指出，某些事务被硬编码为完全持久的事务，并且无论设置如何，延迟持久性选项都不会影响这些事务。 因此，延迟的持久性不适用于任何跨数据库事务，Microsoft分布式事务控制（MSDTC）事务，与变更跟踪，变更数据捕获，事务复制和文件表操作，日志传送和日志备份以及系统相关的事务交易，无论延迟设置如何
  
  
• 
  
  
• Delayed transaction durability – This option allows asynchronous mode for transactions allowing the data stored in the buffer to be flushed on disk before flushing the Log cache 延迟的事务持久性 –此选项允许事务的异步模式，从而允许在刷新日志缓存之前将缓冲区中存储的数据刷新到磁盘上
  
  
  This option must be used carefully and only in systems that can afford a certain level of data loss at first place, when transaction log writes latency is causing the performance bottleneck or in situations where high contention level of workload is encountered to allow faster releasing of the acquired locks
  必须谨慎使用此选项，并且仅在首先会造成一定程度的数据丢失的系统中使用，当事务日志写入延迟导致性能瓶颈时，或者在遇到工作负载争用级别高的情况下，这样才能更快地释放数据。获得的锁

使用DELAYED_DURABILITY选项控制事务的持久性 (Controlling transactions durability using the DELAYED_DURABILITY option)

Database level

数据库级

To control transaction durability at the database level, the DELAYED_DURABILITY option must be used with the ALTER DATABASE command

为了控制数据库级别的事务持久性，必须将DELAYED_DURABILITY选项与ALTER DATABASE命令一起使用

ALTER DATABASE AdwentureWorks2014 SET DELAYED_DURABILITY = |DISABLED|ALLOWED|FORCED

DISABLED – This is the default option that grants full durability of all transactions. It is the highest-level option that overrides any delayed durability option set at the transaction (COMMIT) or Atomic block level

禁用 –这是默认选项，可授予所有事务完全持久性。 它是最高级别的选项，它将覆盖在事务（COMMIT）或原子块级别设置的任何延迟的持久性选项

ALLOWED – When this option is turned on, the transaction durability decision transfers to the lower Transaction and Atomic block level. This option allows the transaction with the explicitly delayed durability settings imposed at the Transaction and Atomic block level to be met. So, it doesn’t force the delayed durability anyhow but rather delegates the decision directly to the lower level

允许 – 启用此选项后，事务持久性决策将转移到较低的事务和原子块级别。 通过此选项，可以满足在事务和原子块级别施加的具有显式延迟的持久性设置的事务。 因此，它不会强制延迟延迟，而是将决策直接下放到较低级别

FORCED – When this option is turned on, the delayed durability is forced to all transactions in the database, except those above mentioned cannot be set to be with delayed durability. This option overrides any explicitly or non-explicitly determined delayed durability at the Transaction and Atomic block level. Using this option is particularly useful when there are no easy options to control durability at the application level or changing the application code is not an option

强制 –启用此选项后，将对数据库中的所有事务强制执行延迟的持久性，除非上述不能设置为具有延迟的持久性。 此选项将覆盖“事务”和“原子”块级别上任何明确或非明确确定的延迟耐久性。 如果没有简单的选项可以在应用程序级别上控制持久性，或者无法更改应用程序代码，则使用此选项特别有用

Transaction (COMMIT) level

交易（COMMIT）级别

The delayed durability at the explicit transaction level applies via the extended syntax of the COMMIT command

显式事务级别的延迟持久性通过COMMIT命令的扩展语法来应用

COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON|OFF);

ON – When the option is set to ON, committing of the transaction is set to delayed durability, and it follows that setting except in situations where the delayed durability at the database level is set to DISABLED. In case that DISABLED is set at the database level, the transactions are set in synchronous COMMIT mode, and the ON option doesn’t have any effects

ON –该选项设置为ON时，事务的提交设置为延迟的持久性，并且遵循该设置，除非数据库级别的延迟的持久性设置为DISABLED。 如果在数据库级别设置了DISABLED，则在同步COMMIT模式下设置事务，并且ON选项没有任何作用。

OFF – This is a default value that is valid except in situations where the delayed duration at the database level is set to FORCED. In such cases, where FORCED is set at the database level, asynchronous COMMIT is imposed, and the OFF option (as well as default delay durability settings) doesn’t have any effects

OFF –这是一个默认值，除非在数据库级别的延迟持续时间设置为FORCED的情况下有效。 在这种情况下，在数据库级别设置了FORCED的情况下，将施加异步COMMIT，并且OFF选项（以及默认的延迟持久性设置）不起作用。

Atomic block level

原子块级

Managing of delayed durability at the Atomic block level can be done via the BEGIN ATOMIC syntax that is extended with the DELAYED_DURABILITY = ON|OFF option

可以通过BEGIN ATOMIC语法来管理Atomic块级别的延迟耐久性，该语法已使用DELAYED_DURABILITY = ON | OFF选项扩展

CREATE PROCEDURE dbo.Test_t1 @p1 bigint not null, @p2 bigint not null  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS  
BEGIN ATOMIC  
WITH (DELAYED_DURABILITY = ON,TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
 
  INSERT dbo.TestTable VALUES (@p1)  
  INSERT dbo.TestTable VALUES (@p2)  
 
END  
GO

ON – When this option is set to ON, committing of the transaction is set to delayed durability, and it follows that setting except in situations where the delayed durability at the database level is set to DISABLED. In case that DISABLED is set at the database level, the transactions are set in synchronous committing mode, and the ON option doesn’t have any effects

ON –当此选项设置为ON时，事务的提交设置为延迟的持久性，并且遵循该设置，除非数据库级别的延迟的持久性设置为DISABLED。 如果在数据库级别设置了DISABLED，则将事务设置为同步提交模式，并且ON选项不起作用。

OFF – This is a default value that is valid always, except in situations where the delayed duration at the database level is set to FORCED. In such case, the asynchronous committing mode imposes, and the OFF option (as well as default delay durability settings) doesn’t have any effects

OFF –这是一个始终有效的默认值，除非在数据库级别将延迟持续时间设置为FORCED的情况下。 在这种情况下，将采用异步提交模式，并且OFF选项（以及默认的延迟持久性设置）没有任何效果。

I case of the Atomic block; the ON and OFF option behaves differently depending on whether the transaction the processing is active, or there is no active transaction:

我以原子块为例； ON和OFF选项的行为会有所不同，具体取决于正在处理的事务是活动的还是没有活动的事务：

DELAYED_DURABILITY = OFF

DELAYED_DURABILITY = OFF

There is no active transaction – The Atomic block initiates a new transaction that is set as fully durable

没有活动的交易 –原子块启动一个新交易，该交易被设置为完全持久

The transaction processing is active – A save point creates in the ongoing transaction (fully or delayed durable) by the Atomic block, and then it starts the new fully durable transaction

事务处理处于活动状态 –通过原子块在正在进行的事务中创建保存点（完全或延迟的持久性事务），然后启动新的完全持久性事务

DELAYED_DURABILITY = OFF

DELAYED_DURABILITY = OFF

There is no active transaction – The Atomic block initiates a new transaction that is set with delayed durability

没有活动的交易 – Atomic块启动了新交易，并设置了延迟的持久性

The transaction processing is active – A save point creates in the ongoing transaction (fully or delayed durable) by the Atomic block, and then it starts the new transaction with delayed durability

事务处理处于活动状态 –通过原子块在进行中的事务（完全或延迟的持久性）中创建一个保存点，然后以持久性为延迟来启动新事务

Finally, one important note that is often overlooked. When delayed durability is turned on, any normal and planned SQL Server restart or shut down is treated in the same way as any other catastrophic event. So for any SQL Server maintenance that requires planned restart or shut down of SQL Server, data loss should be planned. While it is possible that data loss might not occur in some specific scenarios, any planned or unplanned restart or shut down of SQL Server should be treated as a catastrophic event when active delayed durability is engaged

最后，一个经常被忽视的重要注意事项。 打开延迟的持久性后，任何正常的和计划SQL Server重新启动或关闭都将与任何其他灾难性事件一样对待。 因此，对于需要计划重新启动或关闭SQL Server的任何SQL Server维护，都应该计划数据丢失。 虽然在某些特定情况下可能不会发生数据丢失，但是在采用活动的延迟持久性时，任何计划的或计划外SQL Server重新启动或关闭都应视为灾难性事件。

翻译自: https://www.sqlshack.com/how-to-handle-the-sql-server-writelog-wait-type/

writelog