Minimum log record in derivative: Background and theory _ MySQL

What is MinimalLogging )? When the recovery mode of the database is SIMPLE or BULK_LOGGED, for operations that minimize the log type, the transaction log does not record the logs of each individual data row, but records the modification logs of the corresponding page and partition structure. This significantly reduces what is Minimal Logging )?

When the recovery mode of the database is SIMPLE or BULK_LOGGED, for operations that minimize the log type, the transaction log does not record the logs of each individual data row, but records the modification logs of the corresponding page and partition structure.

This significantly reduces the number of transaction logs generated by the operation. For example, if you Insert 200 rows of data to a data page and minimize the number of log records, only one log that changes the data page, instead of 200 Insert logs, is recorded.

Operation to minimize the log type

SELECT

Bulk derivative operations, including bulk insert and BCP

Insert into... SELECT:

A) use OPENROWSET (BULK...) in SELECT ...)

B) if the target table does not have a non-clustered index, it inserts more than eight pages of data into it and uses TABLOCK. If the target table is empty, there can be clustered indexes. if not empty, no.

Partially update columns of the sequence type

When using. WRITE in UPDATE to insert or append data

Use WRITETEXT and UPDATETEXT for LOB fields to insert or append new data, excluding updates.

INDEX operations, including create index, alter index rebuild, dbcc dbreindex, and drop index on tables/views)

Minimal log record in data import

This article focuses on the minimal log record of data import, which refers to bulk insert derivative operation. Many theories are common in other types of operations.

1. normal INSERT

SQL Server uses locks and logging to ensure ACID properties of database transactions. During the entire transaction of inserting a row of data, this row will be locked to avoid concurrent transaction access;

This line will also be written into log records. To insert a data row, follow these steps:

Locks a row.

Write log records. A log record contains the complete data of the inserted row.

Data rows are written to the data page.

When multiple rows are inserted, each row repeats the preceding steps. This refers to the approximate operation prototype. the actual processing is much more complicated, such as lock upgrade and constraints check.

2. BULK import

When BULK imports and commits a transaction, all data pages used by the transaction are written to the disk to ensure the atomicity of the transaction. It is equivalent to performing a CHECKPOINT every time a transaction is committed. If you need to roll back the BULK transaction, SQL Server will retrieve the log to obtain the page or zone information involved in the transaction and remark it as unused. When you back up transaction logs, both the data pages and index pages involved in BULK are backed up to the log backup. Restoring a log backup that contains a BULK transaction does not support restoring to a specified time point.

The eighth page of each data file is the BCM page (BULK Chandged Map), and a BCM page will be displayed on every 511230 pages. Each Bit in BCM represents a zone. if this Bit is 1, it indicates that this zone has been modified by BULK operations since the last backup log. During the next log backup, these modified zones will be copied to the log backup.

3. conditions to be met when using the minimum log record to import data

The minimum log derivative is not always possible. The judgment logic is as follows (from Itzik Ben-Gan)

A) SQL Server 2008 versions earlier than version judgment logic:

Non-FULL recovery model

And not replicated

AND TABLOCK

AND (

Heap

OR (B-tree AND empty)

)

B) SQL Server 2008 and later versions:

Non-FULL recovery model

And not replicated

AND (

(Heap and tablock)

OR (B-tree AND empty AND TABLOCK)

OR (B-tree AND empty AND TF-610)

OR (B-tree AND nonempty AND TF-610 AND key-range)

From SQL 2008, you can use the trace tag 610 and the row key range lock to minimize log operations for empty/non-empty clustered index tables.

Example of exclusive Key range lock: clustered index table tb (id INT), which currently has four rows of data. Now we need to insert 500 rows of data into the table. the value range of the data is [].

When inserting an index, SQL Server does not need to obtain the overall exclusion lock of the clustered index (such as tablock), but only obtains the exclusive Key range lock of the original key value range. Here we get the x key-range lock in the RANGE. Data that is not in this range can still be accessed by other processes. To minimize the log record derivative of a non-empty index table, sort the imported data by the index key-value column of the target table in advance, and enable the trace tag 610.

From the above judgment logic, we can see that the premise for achieving the minimum log record is that the database is not in the full recovery mode and the table is not marked as a copy. TABLOCK is always required for heap tables. For an index table, it can be divided into two types: empty table and non-empty table. This part is further explained in the following example.

Observe the BULK import log

Use the undisclosed system function sys. fn_dblog to find related logs. Fn_dblog uses two parameters to specify the log range to be queried, indicating the start and end LSNS respectively. In the output field, Operation, Context, Log Record Length, and AllocUnitName are important in this article. Because it is an undisclosed function, the meaning of the output content must be interpreted based on personal experience and "consensus.

Operation (LOP): indicates the log Operation to be performed, such as LOP_MODIFY_ROW and LOP_SET_BITS when the bitmap page is set.

Context (LCX): Context of a log operation, which generally indicates the affected object type. For example, LCX_GAM, LCX_HEAP, and LCX_PFS.

Log Record Length: the Log Length in bytes.

AllocUnitName: indicates the affected object.

Use the following script for analysis. the script is from Jakub K.

-- Select count (*) AS numrecords, CAST (COALESCE (SUM ([Log Record LENGTH]), 0)/1024. /1024. as numeric (12, 2) AS size_mbFROM sys. fn_dblog (NULL, NULL) as dwhere AllocUnitName = 'dbo. tableName 'OR AllocUnitName LIKE 'dbo. tableName. % '; -- Average LENGTH and quantity of logs of various types SELECT Operation, Context, AVG ([Log Record LENGTH]) AS AvgLen, COUNT (*) AS CntFROM sys. fn_dblog (NULL, NULL) as dwhere AllocUnitName = 'dbo. tableName 'OR AllocUnitName LIKE 'dbo. tableName. % 'group BY Operation, Context, ROUND ([Log Record LENGTH],-2) order by AvgLen, Operation, Context;