How can I lose weight for your database transaction logs?

How can I lose weight for your database transaction logs?

In most SQL Server work environments, especially in OLTP environments, when the transaction log performance of the database encounters a bottleneck, it usually takes more time to complete the transaction, at this time, many people attribute the cause to the I/O subsystem because it cannot support a large number of transaction logs generated by the workload, but this is not the case in reality.

Transaction Log write wait time

For transaction logs, you can use sys. dm_id_virtual_file_stats and the system event writelog to monitor the write wait time. If the write wait time is higher than the expected I/O subsystem, the I/O subsystem cannot support it. This is a general assumption, but it does not mean you need to upgrade your I/O sub-system.

In many systems, you will find a considerable proportion of redundant log records. If you can reduce unnecessary log records, correspondingly, the number of transaction logs written to the disk is reduced, and the write wait time is reduced accordingly. Therefore, the Transaction completion time is reduced.

There are two main causes for redundant log records:

Unused nonclustered indexes

Increasing index fragmentation

Unused Index

When a record is inserted into a table at any time, a record is also inserted into each noncluster index defined in the table (note that filetered index may be an exception ), this means that redundant log records are generated; the same is true for deleting records in the table, and the corresponding records in the noncluster index must also be deleted, updating data also modifies records in noncluster index. These operations are necessary to maintain the correct relationship (true reflection) between each noncluster index and the relevant table. However, if noncluster index is not necessarily used in the query plan, however, the operation and log records generated to maintain them will also be redundant. As noncluster index fragmentation increases, they need to be regularly maintained, maintenance also generates more log records, which are completely unnecessary.

An unused index may be an index that you mistakenly created on the table, or created according to the advice of DMV for SQL Server's missing index, or by the database optimization consultant, it may also be caused by changes in the business that caused the original indexes to no longer be used.

In any case, these unused indexes should be cleared to reduce load. First, you must determine which indexes are not used. You can view the DMV sys. dm_db_index_usage_stats.

Index fragmentation

In the view of many people, index fragmentation may lead to the requirement to read more data pages. In fact, index fragmentation may also lead to the generation of excess log records, which is due to the cause of fragmentation.

The fragmentation is caused by the occurrence of page split. The simple explanation is that when a record is inserted and insufficient space leads to page split, this process is like this:

A new index is allocated and formatted.

Remove half of records from pages filled with data to new pages

Link the new page to the index structure

New records are inserted into the page.

All these operations generate a log record. As you can imagine, there are far more log records than those generated when you insert a record.

The first step to reduce the additional cost is to clear unused indexes. The purpose is to prevent them from generating page splitting. Therefore, we need to find the indexes that are split into fragments, the second step determines which fragmentation method is used to analyze the index to determine the degree of fragmentation. By using the system function sys. dm_db_index_physical_stats, you can detect all indexes in a specific index, table or index view, all indexes in the database, or fragments in all indexes in all databases. For partitioned indexes, sys. dm_db_index_physical_stats also provides shard information for each partition. The fragment calculation algorithm in SQL Server 2005 is more accurate than that in SQL Server 2000. Therefore, the shard value is higher. For example, in SQL Server 2000, if page 11 and page 13 of a table are in the same partition, and page 12 is not in the same partition, the table is not considered as a shard. However, to access these two pages, two physical I/O operations are required. Therefore, in SQL Server 2005, this table is counted as fragments. Index filling factor is used to re-create or re-organize the index, so that the null space in the index is reserved for subsequent inserted records. This reduces the occurrence of page splitting, this reduces the generation of additional log records. (See another article: discovering unused database indexes)

However, there is no free lunch in the world, and anything advantageous to one party may be harmful to the other. When the fill factor fillfactors is used, the page density will be reduced. Low page density will also bring some performance problems. Of course, too high will lead to page splitting, so this is a trade-off problem, for details, refer to your environment, such as OLTP or OLAP.

Summary

Reducing the write wait time of transaction logs does not always require upgrading your I/O subsystem. Using simple Index Analysis in the database can significantly reduce the generation of a large number of Transaction log records, this also reduces the write wait time.

Of course, this is only one aspect that affects transaction log performance. You can only find that you have a deeper understanding of the transaction log mechanism, and transaction log performance issues.

About the author

Jiang chuanhua has been engaged in database teaching, design, development and application management for a long time. He has more than 20 years of IT experience and deeply understands the principles of relational databases and the architecture of SQL Server. It is also active on Microsoft's major Forum websites.

We have been trying to stick to originality... please do not say anything, just quietly take it away.

I am original, you are original, our content world will be more exciting!