Shulou(Shulou.com)06/03 Report--

The primary purpose of SQL Server to record transaction logs is to restore failed or cancelled operations to the original state. However, not all operations need to fully record transaction logs, such as placing exclusive locks on an empty table and inserting a large amount of data into the empty table. Even if the insert operation fails at any time, you only need to empty the table and restore the table without recording the details of the insertion at all. The purpose of placing an exclusive lock on the table is to prevent others from updating the table, and when the insert fails, you only need to empty the table to return to its original state.

Minimizing logging records only the information needed to restore the transaction, and does not support any point-in-time recovery, that is, when minimizing logging operations, SQL Server also logs transactions, but records only the limited information needed to roll back the transaction. " Limited information "means that only the allocated pages are recorded in the transaction log, not the actual data contained in those pages, thus maintaining a small transaction log file size.

1. Minimum log operation

In FULL restore mode, transaction logs are fully recorded in all bulk operations. Minimizing logging is more efficient in bulk data insertion, reducing the possibility of transaction log space exploding during bulk operations. However, if the database is damaged or lost when minimized logging takes effect, the database cannot be restored to the point of failure.

Bulk data inserts are performed during minimized logging, and although data inserts are not recorded in the transaction log, each extent allocated to the table (Page with 8 consecutive physical addresses) is recorded in the transaction log. Not all operations can minimize logging and minimize the types of logging operations:

Bulk import operations (Bulk Import Operations) include BULK INSERT, BCP, and INSERT SELECT

SELECT INTO

Index operations: CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX

Interestingly, TRUNCATE does not minimize logging operations. In any restore mode, TRUNCATE fully records the transaction log and can restore to any point in time, but TRUNCATE logging is more efficient, using the deferred-drop mechanism to log.

Second, the conditions for triggering the minimum log

The environment of the test case is SQL Server 2017, which is tested in SIMPLE or BULK_LOGGED restore mode.

In fact, to minimize logging when performing a bulk insert, five conditions must be met:

The database is in SIMPLE or BULK_LOGGED restore mode

Table-level locking, it is recommended to use table hint explicit locking: with (tablock)

It is not a copy table

Not a memory optimization table

On the basis of meeting the first four conditions, the conclusions are as follows:

Whether a table can be minimized for logging also depends on whether the table is indexed, and if so, on whether the table is empty.

Conclusion 1: the table does not have an index, and Data Page performs minimal logging.

Conclusion 2: the table does not have a clustered index, but it has a nonclustered index, and Data Page performs minimal logging.

Index Page performs minimized logging when the table is empty

When the table has data, Index Page performs full logging

In the case of sub-Batch inserts, when the table is empty, both Data Page and Index Page perform minimized logging for the first Batch insert; starting with the second Batch, Data Page performs minimized logging and Index Page performs full logging.

Conclusion 3: the table has a clustered index

Both Data Page and Index Page perform minimized logging when the table has a clustered index and is an empty table.

When the table has a clustered index and has data, both Data Page and Index Page perform full logging.

In the case of sub-Batch inserts, when the table is empty, both Data Page and Index Page perform minimized logging for the first Batch insert; starting with the second Batch, Data Page performs minimized logging and Index Page performs full logging.

Conclusion 4, as can be seen from the table:

The allocation of index pages is all Fully Logged

The data page updates of the heap table are all Min Logged.

The update of the data page is Fully Logged only if the table is a clustered index. In fact, the BTree table is the index itself.

Third, minimize logs in index operations

From conclusion 4 in the previous section, we know that the allocation of index pages is Fully Logged, and the deallocation of index pages is Fully Logged. In certain cases, performing CREATE INDEX, ALTER INDEX REBUILD, and DROP INDEX can stimulate minimized logging of data pages, and index REBUILD is equivalent to deleting the index before creating it.

For example, creating an index is equivalent to inserting data into a table with data. The index page is Full Logged, and the data table determines whether the data page is Full Logged or Min Logged based on conclusion 4.

Fourth, delay deletion

For TRUNCATE TABLE, the profile removes data by recycling allocated data pages, and only the recycled data pages are recorded in the transaction log.

Both DROP TABLE and TRUNCATE TABLE are full logging operations, but the logs are not created immediately, but are deferred, which is achieved by the delayed deletion (deferred drop) mechanism. When a table is drop or truncate, all data pages belonging to the table are marked as recycled by the system, and the data pages and extents marked as recycled are placed in the delayed deletion queue (deferred-drop queue). The data pages or extents are not actually released, but are only marked as deallocation. The deferred deletion mechanism simulates the effect of an immediate completion of a drop or truncate operation by retrieving the data pages of the table, a process that produces very little logging.

But the deferred deletion background processor (deferred-drop background task) executes every few seconds and reclaims all Page and Extent placed in the deferred delete queue (deferred-drop queue) in small batches, ensuring that the operation does not run out of memory. The operation of reclaiming space is fully logged, but releasing a page full of data or index records does not record the deletion of individual rows of data. Instead, the entire page is simply marked as unallocated in the associated PFS (Page Free Space) allocation byte graph.

Starting with SQL Server 2000 SP3, when you execute the DROP or TRUNCATE of a table, you will only see some log records that are being generated. If you wait a minute or so and then look at the transaction log again, you will see that thousands of log records have been generated by the deferred-drop operation, each of which represents the recycling of a Page or Extent.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.