Định nghĩa : PAGELATCH_[xx] (trong đó [XX] là mode của latch như SH, EX,..) là wait type xảy ra khi một latch phải chờ trước khi nó có thể đặt trên một page ở trong memory (index pages hoặc data pages)
Trước khi đi sâu vào loại wait type này ta cùng đi qua một khái niệm cơ bản hơn là Latch
Latch
Latch là một cơ chế đồng bộ được sử dụng bởi sql server để đảm bảo tính nhất quán của cấu trúc vùng nhớ trong (in-memory) bao gồm : index pages, data pages và cấu trúc internal
Latch có nhiều mode nhưng hôm nay chúng ta tập trung vào 2 mode phổ biến nhất là SH và EX
- SH : SH mode đại diện cho một Shared latch, mode này được sử dụng khi thread thực hiện việc đọc page data và trước khi đọc cần đặt một latch với mode là SH trên page đó để bảo vệ page trong quá trình đọc dữ liệu, nhiều thread có thể dùng đặt latch với mode là SH trên cùng một page khi đọc page data
- EX : Exclusive latch, được sử dụng khi xảy ra chỉnh sửa resource, khi resource có Exclusive latch thì những thread khác muốn đặt SH mode hoặc EX mode cần chờ cho đến khi thread giữ Exclusive latch trên page được giải phóng
Ma trận tương tích của latch
| SH | EX | |
| SH | Yes | NO |
| EX | NO | NO |
Từ ma trận trên ta thấy :
- SH mode tương thích với SH mode có nghĩa là 2 thread có thể cùng đặt latch với mode là SH trên cùng một page
- SH mode không tương thích với EX mode
- Ví dụ : có 2 thread là thread1 và thread2, khi thread1 đã đặt latch có mode là SH trên một page và thread2 cần đặt một latch có mode bằng EX cũng trên page đó thì thread2 cần phải chờ latch được đặt bởi thread1 giải phóng trước khi đặt latch của nó trên page này
- EX mode không tương thích với EX mode
Để dễ hình dung chúng ta cùng đi qua ví dụ cụ thể sau
Trước tiên chúng ta tạo môi trường để test bằng script sau :
/*Tạo database*/
CREATE DATABASE PageLatchXX
GO
USE PageLatchXX
GO
/*Tạo bảng lưu thông tin cấu hình của hệ thống, đặc điểm của bảng setting này là số dòng thường ít nhưng tần suất cập nhật cao*/
CREATE TABLE dbo.Settings
(
SettingId INT
,SettingValue CHAR(10)
)
GO
CREATE INDEX CX_SettingId ON dbo.Settings (SettingId)
GO
/*khởi tạo dữ liệu cho bảng dbo.Settings*/
;WITH CTE AS
(
SELECT 1 AS Id
UNION ALL
SELECT Id + 1
FROM CTE
WHERE Id < 100
)
INSERT INTO dbo.Settings (SettingId, SettingValue)
SELECT Id, 'Value ' + CONVERT(VARCHAR(10), Id)
FROM CTE
OPTION (MAXRECURSION 0)
Sau khi khởi tạo dữ liệu xong ta sẽ có bảng dbo.Settings với 100 rows như hình bên dưới
Hình 1
Tiếp theo ta cùng kiểm tra cấu trúc của bảng này bằng câu lệnh DBCC IND
DBCC IND ('PageLatchXX', 'Settings', -1)
GO
Kết quả :
Hình 2
Từ kết quả ta thấy bảng dbo.Settings bao gồm 2 page: 1 IAM page (PageId = 312, FileId = 1) và 1 data Page (PageId = 304, FileId = 1)
Khi cần đọc một data page, đầu tiên sql server kiểm tra xem data page đó có trong memory hay không, nếu không có sql server sẽ load data page này từ disk vào memory, sau đó sử dụng data page này trong memory.
Vì PAGELATCH_[XX] chỉ liên quan đến việc cấp phát và giải phòng latch đối với page nằm trong memory, nên trước khi làm các thử nghiệm phía sau ta cần chắc chắn page chúng ta quan tâm (pageId = 304) đã được load vào memory bằng cách thực hiện những câu lệnh sau
/*Load PageId = 304 vào memory*/
SELECT *
FROM dbo.Settings
/*kiểm tra PageId = 304 có được load vào memory hay chưa */
SELECT *
FROM sys.dm_os_buffer_descriptors
WHERE file_id = 1 AND page_id = 304 AND database_id = DB_ID('PageLatchXX')
Kết quả :
Hình 3
Từ hình 3 ta thấy data page có pageid = 304 đã được load vào memory
Ở đây mình nói thêm về dữ liệu được trả ra từ sys.dm_os_buffer_descriptors :
Khi một page đọc từ disk, page đó được copy vào buffer pool và được lưu lại để có thể tái sử dụng, với mỗi page được lưu lại sẽ có một cấu trúc gọi là BUF (buffer) liên kết với page này và chứa thông tin của page, view sys.dm_os_buffer_descriptors trả ra thông tin của page trong buffer pool và những thông tin này được lấy từ BUF chứ không phải truy cập trực tiếp đến từng data page, để thấy rõ cấu trúc BUF hơn trong trường hợp của PageId = 304 ở trên, ta dùng câu lệnh sau :
DBCC TRACEON (3604)
GO
DBCC PAGE (PageLatchXX, 1, 304, 3)
GO
Hình 4
Phần tô đỏ ở Hình 4 cho ta biết thông tin mà BUF đang lưu trữ, một số thông tin cơ bản của BUF như :
- bpage : lưu địa chỉ của page mà nó trỏ đến, ở trên hình ta thấy bpage = 0x0000020BD2576000 đúng bằng địa chỉ của pageId = 304 là Page @0x0000020BD2576000
- bdbid : lưu databaseid, ở đây bdbid = 135 là databaseid của DB : PageLatchXX vừa được tạo và nó đúng bằng kết quả được trả ra từ view sys.dm_os_buffer_descriptors ở Hình 3
- bpageno: lưu thông tin page mà BUF liên kết, ở đây là fileid = 1 và pageid = 304
Với kết quả trả ra từ Hình 3, ta biết chắc chắn pageId = 304 đang nằm trong buffer pool, giờ chúng ta sẽ sử dụng 4 extended events sau đây để bắt thông tin của latch
Extended Events :
- latch_acquired : event này phát sinh khi thread đã đặt latch trên page
- latch_released : event này phát sinh khi thread đã giải phóng latch trên page đó
- latch_suspend_begin : event này phát sinh khi thread muốn đặt một latch trên page nhưng không đặt được vì trên page đó đang có một latch có mode không tương thích với mode mà thread muốn đặt, nên thread này cần chờ
- latch_suspend_end : event này phát sinh khi quá trình thread chờ để được đặt latch trên page kết thúc và thread này có thể đặt latch trên page đó
Script mình dùng để tạo Extended Event
CREATE EVENT SESSION [PageLatch] ON SERVER
ADD EVENT sqlserver.latch_acquired(
ACTION(sqlos.system_thread_id,sqlserver.session_id,sqlserver.sql_text)
WHERE ([package0].[equal_uint64]([page_id],(304)) AND [package0].[equal_uint64]([database_id],(135)))),
ADD EVENT sqlserver.latch_released(
ACTION(sqlserver.session_id)
WHERE ([package0].[equal_uint64]([page_id],(304)) AND [package0].[equal_uint64]([database_id],(135)))),
ADD EVENT sqlserver.latch_suspend_begin(
WHERE ([page_id]=(304) AND [database_id]=(135))),
ADD EVENT sqlserver.latch_suspend_end(
WHERE ([page_id]=(304) AND [database_id]=(135)))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
Trong scripts này mình filter theo databaseid = 135 và pageid = 304, bạn hãy chỉnh 2 số này phù hợp với thông tin trên máy tính của bạn
Ta bắt đầu chạy câu lệnh sau đây
SELECT *
FROM dbo.Settings
Kết quả trả ra từ Extended Event :
Hình 5
Ở kết quả Hình 5, ta thấy có 2 events phát sinh :
Dòng số 1 :
- Name là latch_acquired cho ta biết latch đã được đặt trên FileId = 1 và PageId = 304 và vì câu query thực hiện đọc dữ liệu nên Mode = SH và ở đây Shared_Count = 1 vì latch (có mode SH) đã được đặt trên Page (PageId = 304)
- Một điểm đáng lưu ý là cột Class có giá trị BUF, cho ta biết latch class là BUF, khi một thread cần đặt một latch trên page thực chất nó không trực tiếp lưu thông tin của latch trên page, thay vào đó thread đó sẽ truy cập vào buffer và thực hiện lưu thông tin latch cần tổ chức trên page ở BUF
Dòng số 2 :
- Name là latch_released, cho ta biết latch đặt trên FileId = 1, pageId = 304 đã giải phóng và vì vậy Shared Count = 0
Tiếp theo ta cùng thực hiện câu lệnh update để xem latch nào sẽ được tổ chức ở page
UPDATE dbo.Settings
SET SettingValue = 'Value 123'
WHERE SettingId = 1
Kết quả trả ra từ Extended Event :
Hình 6
Ở đây ta thấy có thêm 2 dòng (3) và (4)
Dòng số 3 :
- Name là latch_acquired cho ta biết latch đã được đặt trên fileid = 1 và pageid = 304 vì câu query thực hiện chỉnh sửa dữ liệu nên mode của latch là EX, đồng thời Exclusive_count có giá trị bằng 1
Dòng số 4 :
- Name là latch_released cho ta biết latch được đặt trên fileid = 1 và pageid = 304 đã được giải phóng nên Exclusive_count lúc này bằng 0
Như vậy chúng ta đã hiểu được cách đặt latch và giải phóng latch khi thực hiện một câu query update hoặc select, giờ chúng ta cùng đi qua ví dụ tiếp theo để biết điều gì sẽ xảy ra khi nhiều thread chỉnh sửa dữ liệu trên cùng một page
ý tưởng của phần demo này như sau : chúng ta có 1 script và sẽ dùng ứng SQLQueryStress tạo ra 100 threads để chạy script này, đây là nội dung của script
/*Script*/
DECLARE @Id INT = 1
WHILE 1 = 1
BEGIN
UPDATE PageLatchXX.dbo.Settings
SET SettingValue = CONVERT(VARCHAR(10), @id % 100000)
WHERE SettingId = (@@SPID % 100 + 1)
SET @Id += 1
END
Trong scripts này có một số điểm cần ta chú ý :
- Với mỗi thread sẽ có một SessionId liên kết với thread đó và sẽ không thay đổi cho đến khi thread thực hiện xong vì vậy giá trị @@SPID % 100 + 1 (@@SPID là SessionId) là không đổi cho từng thread, nói cách khác mỗi thread sẽ chỉ update giá trị SettingValue của một SettingId duy nhất. Điều mình mong muốn khi dùng ứng dụng SQLQueryStress để tạo ra 100 threads là 100 threads sẽ tương ứng với 100 sessionids khác nhau và các sessionids này có xu hướng có giá trị liên tiếp nhau vì vậy mỗi thread sẽ update một SettingId khác nhau từ đó không phát sinh hiện tượng blocking gây ra bởi nhiều thread đang giữ lock cùng một dòng, chúng ta sẽ dễ dàng quan sát ảnh hưởng của latch hơn
- Cột SettingValue có kiểu dữ liệu là CHAR(10) có độ dài không đổi, điều này đảm bảo cho dù 100 threads update liên tục giá trị mới vào thì toàn bộ data của bảng này vẫn chỉ được chứa trong một data page (pageid = 304), từ đó đảm bảo việc 100 threads sẽ phải đặt latch với mode là EX lên cùng một page, như vậy ta sẽ dễ dàng quan sát ảnh hưởng của latch
Để thực hiện ý tưởng trên ta cần cài đặt ở ứng dụng SQLQueryStress như sau
Điều chỉnh thông tin database cần kết nối :
Hình 7
Nhập câu query cần chạy và số thread thực thi câu query
Hình 8
Sau khi setting xong ta nhấn GO
Kết quả bắt được từ extended event như sau :
Hình 9
Latch được cấp phát và giải phóng liên tục nên như trên hình có tới 225908 Events bắt được, và ở đây ta cũng thấy xuất hiện một event mới là latch_suspend_begin
latch_suspend_begin : event này phát sinh khi thread muốn đặt một latch trên page nhưng không đặt được vì trên page đó đang có một latch có mode không tương thích với mode mà thread muốn đặt, nên thread này cần chờ
Ta phân tích kết quả ở khung được tô đỏ để hiểu thêm về event này :
- Thread liên kết với sessionid = 167 muốn đặt một latch lên FileId = 1, PageId = 304 với mode là EX nhưng ở đây ta thấy Exclusive_Count = 1, chứng tỏ page này đã được đặt một latch có mode là EX trước đó nên thread này phải chờ cho latch trên FileId = 1, PageId = 304 giải phóng
- Nhìn vào phần khung tô đỏ ta thấy rất nhiều thread đang chờ để được đặt latch lên FileId = 1, PageId = 304
Chạy tiếp câu query sau đây để xem thông tin của các request đang chờ
SELECT s.program_name, r.wait_resource,r.wait_type, r.session_id, r.status, t.text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) AS t
WHERE s.program_name = 'SQLQueryStress'
Hình 10
Ta thấy rất nhiều request đang chờ với wait_type = PAGELATCH_EX, wait_Resource = 135:1:304, từ thông tin này cho ta thấy request đang chờ để đặt latch với mode là EX lên resource có databaseid = 135, fileId = 1 và pageId = 304 và page này đã nằm trong memory nên xuất hiện thêm PAGELATCH ở tên wait type
Từ quá trình phân tích trên ta đã hiểu PAGELATCH_EX là gì và khi nào chúng xuất hiện
Nhưng làm cách nào để khắc phục loại wait type này hay nói cách khác làm cách nào để một thread không cần phải chờ thread khác để đặt latch trên page nó cần đặt ?
Đầu tiên ta quay lại với ví dụ ban đầu :
Nhìn lại ví dụ ban đầu ta thấy lý do gây nên loại wait_type : PAGELATCH_EX là do 100 threads cùng muốn đặt latch lên cùng pageId = 304, nhưng mỗi thread thực đặt latch trên page đó để update những SettingId khác nhau, và vì kích thước row quá nhỏ nên 100 SettingIds phải nằm trên cùng một page, vậy cách giải quyết ở đây là tách riêng mỗi SettingId ra một page như vậy 100 threads sẽ update thông tin của 100 SettingIds khác nhau, và mỗi SettingId lại nằm ở các data page khác nhau nên chúng không cần chờ để được đặt latch trên page nó cần update. Giải pháp mình đưa ra là tăng kích thước của row, cụ thể là đổi kiểu dữ liệu SettingValue từ CHAR(10) sang CHAR(8000)
Chúng ta cùng thực hiện lại demo với ý tưởng trên
DROP TABLE IF EXISTS dbo.Settings
GO
CREATE TABLE dbo.Settings
(
SettingId INT
,SettingValue CHAR(8000)
)
GO
CREATE INDEX CX_SettingId ON dbo.Settings (SettingId)
GO
/*khởi tạo dữ liệu cho bảng dbo.Settings*/
;WITH CTE AS
(
SELECT 1 AS Id
UNION ALL
SELECT Id + 1
FROM CTE
WHERE Id < 100
)
INSERT INTO dbo.Settings (SettingId, SettingValue)
SELECT Id, 'Value ' + CONVERT(VARCHAR(10), Id)
FROM CTE
OPTION (MAXRECURSION 0)
Kiểm tra lại data được lưu
DBCC IND ('PageLatchXX', 'Settings', -1)
Hình 11
Số page đã tăng lên 103 vì lúc này mỗi Settingid nằm hoàn toàn trong những data page khác nhau
cùng kiểm tra thông tin ở một page trong số đó (chọn page có page type = 1 vì những page này lưu data) ở đây mình chọn pageid = 306
DBCC TRACEON(3604)
GO
DBCC PAGE (PageLatchXX, 1, 306, 3)
GO
Kết quả :
Hình 12
Page chỉ có 1 rows (m_sloatcnt = 1), chứa data của settingId = 3
Chạy lại ví dụ update setting với 100 thread như ban đầu và thực hiện câu query bên dưới để kiểm tra xem có còn loại wait type trên không
SELECT s.program_name, r.wait_resource,r.wait_type, r.session_id, r.status, t.text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) AS t
WHERE s.program_name = 'SQLQueryStress'
Hình 12
đã không còn wait type = PAGELATCH_EX như lúc trước.
Một điểm cần lưu ý với cách giải quyết ở trên của mình nó bắt nguồn từ tính chất bảng dbo.Settings, vì bảng này chứa cấu hình cài đặt, nên đặt điểm của nó là có ít dòng và được update liên tục vì vậy việc tăng kích thước dòng từ CHAR(10) lên CHAR(8000) sẽ dẫn đến việc tăng kích thước lưu trữ bảng dbo.Settings nhưng không đáng kể
Ngoài cách trên ra còn rất nhiều cách để giải quyết như dùng hash partitioning, in-memory OLTP hoặc bật tính năng OPTIMIZE_FOR_SEQUENTIAL_KEY, tùy vào tính chất của vấn đề gây nên loại wait type = PAGELATCH_EX.
Tổng kết :
PAGELATCH_[xx] là wait type xảy ra khi một latch phải chờ trước khi nó có thể truy cập một page ở trong memory, có nhiều cách để giải quyết PAGELATCH_[xx] như tăng kích thước row, hash partitioning, in-memory.
Nguồn tham khảo :