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

Today, I would like to share with you the relevant knowledge of what the automatic growth of the database transaction log will degrade the performance. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge. So share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

First I create a new database for this demo. For this database, I don't need the default setting here. For transaction logs, I specify the automatic growth factor of 10GB. This is indeed a bad practice, but I only use it to show the side effects of this setting. Please do not use this misconfiguration in your production database!

-- Create a new database with 10 GB AutoGrowth for the TransactionLog CREATE DATABASE AutoGrowthTransactionLog ON PRIMARY (NAME = NumberAutoGrowthTransactionLogogical, FILENAME = NumberC:\ Program Files\ Microsoft SQL Server\ MSSQL10.MSSQLSERVER\ MSSQL\ DATA\ AutoGrowthTransactionLog.mdf', SIZE = 5120KB, FILEGROWTH = 1024KB) LOG ON (NAME = Noble AutoGrowthTransactionLogogical, FILENAME = NumberC:\ Program Files\ Microsoft SQL Server\ MSSQL10.MSSQLSERVER\ MSSQL\ DATA\ AutoGrowthTransactionLog_log.ldf', SIZE = 1024KB FILEGROWTH = 10240000KB-- 10 GB Auto Growth!) GO

The next step is to create 2 tables in the database. In the first table, I quickly populated my transaction log by inserting some logs. During the transaction log auto-growth phase, we insert a new record into the second table to prove that the transaction will be blocked by the auto-growth mechanism.

Create a new table, every records needs a page of 8kb CREATE TABLE Chunk (Col1 INT IDENTITY PRIMARY KEY, Col2 CHAR (8000)) GO-- Another simple table CREATE TABLE Foo (Bar INT NOT NULL) GO

Now that we have created the necessary database objects, I can populate the transaction log with new transactions that are not immediately committed:

-- Begin a new transaction, that blocks the 1st VLF in the Transaction Log BEGIN TRANSACTION INSERT INTO Chunk VALUES (REPLICATE ('x, 8000)) GO

Because we now have uncommitted transactions in progress, SQL Server cannot reuse that part of the transaction log, that is, the transaction log stored by this transaction. They may need to be rolled back. So now I populate the transaction log by inserting 66 other records through different sessions:

INSERT INTO AutoGrowthTransactionLog.dbo.Chunk VALUES (REPLICATE ('xtrees, 8000))

GO 66

* commit our transactions in * sessions:

COMMIT

This means that we have a nearly full transaction log in front of us, which we can verify through DBCC LOGINFO:

DBCC LOGINFO

Now when we insert records into the table, there is no free space for the transaction log, and SQL Server enters the automatic growth of the transaction log.

-- This statement will trigger the Auto Growth mechanism! INSERT INTO Chunk VALUES (REPLICATE ('xcow, 8000)) GO

During the auto-growth period, in order to monitor what happened, we can open a new session window in SSMS and try to insert another record in the second table-- table Foo:

-- This statement is now blocked by the Auto Growth mechanism.

INSERT INTO Foo VALUES (1)

GO

This SQL statement blocks because the transaction to be written to the transaction log is currently unavailable. To further analyze the blocking situation, you can open the third session window and execute the following two SQL statements:

-- Analyze the blocking situation SELECT wait_type, * FROM sys.dm_exec_requests WHERE session_id IN (54,55) SELECT wait_type, * FROM sys.dm_os_waiting_tasks WHERE session_id IN (54,55) GO

As you can see from the code, I tracked both sessions with two DMV sys.dm_exec_requests and sys.dm_os_waiting_tasks-- sessions that triggered auto-growth, and sessions that were blocked by the auto-growth mechanism. In this case, the session that triggers auto-growth is the so-called preemption wait type (Preemptive Wait Type)-PREEMPTIVE_OS_WRITEFILEGATHER. The preemptive wait type is the wait type returned by SQL Server when SQL Server executes a WIN32 API function outside the scheduling mechanism. The automatic growth here is done through the WIN32 API function of WriteFileGather.

The INSERT statement attempts to insert a new record into the Foo table and the LATCH_EX wait type appears. As you can see from the resource_description column in DMV sys.dm_os_waiting_tasks, you need to get a latch on the log manager of SQL Server. You can reconfirm lactch class for LOG_MANAGER by querying DMV sys.dm_os_latch_stats. You will see some waiting on that particular latch. That latch is acquired by the transaction, triggered by the automatic growth of the transaction log, and every other write transaction is blocked as long as the latch is acquired. Therefore, when there is a lot of waiting time on the system, this implies that there is currently an automatic growth problem to be dealt with in the transaction log.

These are all the contents of the article "Why does the automatic growth of database transaction logs degrade performance?" Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.