數據庫事務日志自動增長會降低性能的原因是什么

今天小編給大家分享一下數據庫事務日志自動增長會降低性能的原因是什么的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

首先我為這個演示創建一個新的數據庫。對于這個數據庫，這里我不用默認的設置，對于事務日志，我指定了10GB的自動增長系數。這個的確是個不好的做法，但我只是用它來展示這個設置的副作用。請不要在你的生產數據庫里使用這個錯誤配置！！！

-- Create a new database with 10 GB Auto Growth for the Transaction Log CREATE 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 -- 10 GB Auto Growth! ) GO

下一步里我在數據庫里創建2個表。第1個表我通過插入一些日志來快速填充我的事務日志。在事務日志自動增長階段，我們在第2個表里插入新的記錄來證明這個事務會被自動增長機制阻塞。

-- 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

現在我們已經創建了必須的數據庫對象，因次我可以通過新的沒有立即提交的事務來填充事務日志：

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

因為我們現在有了進行中，沒提交的事務，SQL Server不能重用那部分事務日志，即這個事務存儲的事務日志。它們有需要回滾的可能。因此現在我通過不同的會話插入66條其他記錄來填充事務日志：

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

***在***個會話里提交我們的事務：

COMMIT

這意味著在我們面前有一個幾乎滿的的事務日志，我們可以通過DBCC LOGINFO來驗證：

DBCC LOGINFO

現在當我們往表里插入兮的記錄時，事務日志已經沒有可用空間了，SQL Server進入事務日志的自動增長。

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

在自動增長期間的同時，為了監控發生了什么，我們可以在SSMS里打開新的一個會話窗口，嘗試在第2個表插入另外的記錄——表Foo：

-- This statement is now blocked by the Auto Growth mechanism.
INSERT INTO Foo VALUES (1)
GO

這個SQL 語句會阻塞，因為事務要寫入事務日志記錄的事務日志，當前不可用。為了進一步分析這個阻塞情形，你可以打開第3個會話窗口，執行下列2個SQL語句：

-- 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

從代碼里可以看到，我用2個DMV sys.dm_exec_requests 和  sys.dm_os_waiting_tasks對2個會話都進行了跟蹤——觸發自動增長的會話，和被自動增長機制阻塞的會話。在這里，觸發自動增長的會 話里有所謂的搶占等待類型（Preemptive Wait  Type）——PREEMPTIVE_OS_WRITEFILEGATHER。搶占等待類型是由SQL Server返回的等待類型，當SQL  Server 執行一個WIN32 API函數在調度機制之外時。這里自動增長是通過WriteFileGather的WIN32 API函數完成的。

INSERT語句嘗試在Foo表里插入新的記錄出現LATCH_EX等待類型。如你從DMV sys.dm_os_waiting_tasks  里的resource_description列所見，在SQL  Server的日志管理器上需要獲得閂鎖。你可以通過查詢DMV sys.dm_os_latch_stats 限制lactch  class為LOG_MANAGER再次確認。在那個特定閂鎖上你會看到一些等待。那個閂鎖是事務獲取的，由事務日志的自動增長觸發，只要這個閂鎖要獲 得，每個其他寫事務都會被阻塞。因此在系統上有大量等待時間時，這暗示這在事務日志里當前有自動增長問題需要處理。

以上就是“數據庫事務日志自動增長會降低性能的原因是什么”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。