"Go" Why the automatic growth of transaction logs will degrade your performance

In this article, I want to discuss in detail why you want to avoid the autogrow operation (auto growth operations) on the transaction log (Transaction log). Many running database servers, for transaction logs, use the default log file size and autogrow settings. People sometimes rely on the auto-growth mechanism because they are just working properly. Of course, if it works, you don't have to pay much attention to it, but soon you'll find that there are problems.

It is not always a good idea to rely solely on the automatic growth mechanism of the transaction log. First, it causes serious log fragmentation, which can have a significant negative impact during SQL Server startup when a crash recovery (Crash Recovery) is performed on your database. In addition, writing transactions in your database requires waiting, as long as the transaction log triggers the autogrow mechanism.

When the automatic growth mechanism of the transaction log occurs, SQL Server always initializes a new block of 0, which is appended to the end of the file. This and whether your instance of SQL Server is initialized with an instant file (Instant file initialization) privilege--The transaction log is always 0 initialized. The reason for this is very obvious: When SQL Server has completed wrapping processing (wrap-around) of the transaction log in the past, crash Recovery (Crash Recovery) needs to know where to stop.

0 The problem with initialization is that it takes more time (depending on your automatic growth rate and your storage speed). During this time, no other transaction can write transaction logs to the transaction log. There will be blocking caused by the latch on the transaction log manager. So your write transactions go into a pending state (until they get the required latches), they wait, wait, wait, until your transaction log automatically grows to completion. Let's use a simple example to illustrate the next.

First, I create a new database for this demo. For this database, I do not use the default settings here, for the transaction log, I have specified a 10GB auto-growth factor. This is really a bad idea, but I just use it to show the side effects of this setting. Please do not use this error configuration in your production database!!!

--Create A new database with ten GB Auto growth for the Transaction logcreate database Autogrowthtransactionlog on PRIMARY (    NAME = n ' autogrowthtransactionlog ',     FILENAME = n ' C:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\data\autogrowthtransactionlog.mdf ',    SIZE = 5120KB,     filegrowth = 1024KB) LOG on (    NAME = N ' Autogrowthtransactionlog_log ',    FILENAME = N ' C:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\data\autogrowthtransactionlog_log.ldf ',    SIZE = 1024KB,    filegrowth = 10240000KB--Ten GB Auto growth!) GO

In the next step I'll create 2 tables in the database. 1th Table I quickly populate my transaction log by inserting some logs. In the automatic growth phase of the transaction log, we insert a new record in the 2nd table to prove that the transaction is blocked by the autogrow mechanism.

--Create A new table, every records needs a page of 8kbCREATE table Chunk (    Col1 INT IDENTITY PRIMARY KEY,    Col2 CH AR (8000)) go--another simple tablecreate 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 logbegin Transactioninsert into Chunk VALUES (repli CATE (' x ', 8000)) GO

Because we now have an ongoing, uncommitted transaction, SQL Server cannot reuse that part of the transaction log, which is the transaction log for the transaction store. They have the potential to roll back. So now I'm populating the transaction log with a different session by inserting 66 additional records:

INSERT into AutoGrowthTransactionLog.dbo.Chunk VALUES (REPLICATE (' x ', 8000)) GO 66

Finally, we commit our transaction in the first session:

COMMIT

This means that there is an almost full transaction log in front of us, which we can verify through DBCC LOGINFO:

DBCC Loginfo

Now when we insert a record into the table, the transaction log no longer has free space, and SQL Server enters the transaction log autogrow.

--This statement'll trigger the Auto growth mechanism! INSERT into Chunk VALUES (REPLICATE (' x ', 8000)) GO

During the auto-growth period, in order to monitor what happened, we could open a new session window in SSMs and try inserting another record in the 2nd table-table Foo:

--This statement are now blocked by the Auto growth mechanism. INSERT into Foo VALUES (1) GO

This SQL statement is blocked because the transaction log that is being written to the transaction log is not currently available. To further analyze this blocking scenario, you can open a 3rd session window and execute the following 2 SQL statements:

--Analyze The blocking Situationselect wait_type, * from Sys.dm_exec_requestswhere session_id in (si, si) SELECT wait_type , * from Sys.dm_os_waiting_taskswhere session_id in (SI, a) GO

(Well, my machine test failed ..........) ）

As you can see from the code, I tracked 2 sessions with 2 DMV sys.dm_exec_requests and Sys.dm_os_waiting_tasks-triggering an autogrow session, and a session that was blocked by the auto-growth mechanism. Here, there are so-called preemption wait types (preemptive wait type)--preemptive_os_writefilegather in the session that triggers autogrow. The preemption wait type is the wait type returned by SQL Server when SQL Server executes a WIN32 API function outside of the scheduling mechanism. The autogrow here is done through WriteFileGather's WIN32 API function.

The INSERT statement attempts to insert a new record in the Foo table when the LATCH_EX wait type appears. As you can see from the resource_description column in the DMV sys.dm_os_waiting_tasks, you need to obtain a latch on the log manager of SQL Server. You can check the DMV Sys.dm_os_latch_stats limit Lactch class for Log_manager again. On that particular latch you will see some waiting. The latch is a transaction-acquired, triggered by the automatic growth of the transaction log, and every other write transaction is blocked as long as the latch is to be obtained. So there is a lot of waiting time on the system, which implies that there is currently an automatic growth problem in the transaction log that needs to be addressed.

Hopefully I've used this log to convince you that the automatic growth mechanism that relies on transaction logging is not the best solution. With this simple example, you can see that every write transaction that is blocked by an autogrow operation in your database will be blocked, which will certainly hurt your database's throughput and scalability.

"Go" Why the automatic growth of transaction logs will degrade your performance