SQL Server Performance Tuning - Ngăn paging bằng Lock Pages In Memory và Max Server Memory

Paging và Memory management

Quản lý bộ nhớ là một phần quan trọng trong quản trị database. Nhiều máy chủ database của các công ty nhỏ và vừa có thể gặp trường hợp bị phân trang bộ nhớ (paged out) dẫn đến hệ thống bị chậm. Nguyên nhân là thiếu RAM vật lý hoặc cấu hình chưa tối ưu.

Khi paging xảy ra, hệ điều hành và các ứng dụng sử dụng thêm bộ nhớ nhớ ảo lưu trên đĩa cứng với tốc độ chậm hơn nhiều so với RAM vật lý.

Kiểm tra virtual memory và paging

Để kiểm tra mức sử dụng virtual memory của hệ điều hành Windows, ta sử dụng lệnh:
wmic pagefile list

AllocatedBaseSize    CurrentUsage    Description         Name                   PeakUsage
10529                        1366                  C:\pagefile.sys    C:\pagefile.sys     1539



Chỉ số CurrentUsage chính là dung lượng sử dụng thực tế. Pagefile.sys chính là tên file của bộ nhớ ảo trên đĩa cứng. Trong ví dụ này, hệ điều hành phân bổ dung lượng bộ nhớ ảo 10529 MB, và đang sử dụng 1366 MB, mức sử dụng cao nhất 1539 MB.

Để kiểm tra khả năng SQL Server bị phân trang hay không, có nhiều cách để kiểm tra và khá phức tạp, tuy nhiên có một eventlog quan trọng có ID là 17890 thông báo trực tiếp vấn đề này, kiểm tra nhanh bằng lệnh powershell:

Get-WinEvent -FilterHashtable @{LogName="Application"; ID=17890}

Cách để chống paging

Cấu hình max server memory trên SQL Server để ngăn không cho SQL Server chiếm dụng bộ nhớ quá lớn. Mặc định, SQL Server có thể tiêu tốn lượng RAM quá mức dẫn đến hệ điều hành không đủ RAM vật lý nên phải sử dụng đến virtual memory trên đĩa cứng.

Cấu hình Lock pages in memory (LPIM) cho tài khoản dịch vụ SQL Server để ngăn hệ điều hành và các ứng dụng khác tranh chấp RAM vật lý với SQL Server, điều này đảm bảo SQL Server luôn được ưu tiên sử dụng RAM vật lý.

Cấu hình max server memory trên SQL Server

Giá trị mặc định của max server memory (MB) là 2,147,483,647 megabytes (MB).

Khuyến nghị chung là nên thiết lập ở mức 75% bộ nhớ. Tuy nhiên trong thực tế, chúng ta cần theo dõi hệ thống để có số liệu chính xác và điều chỉnh thiết lập.

Công thức tính:

• 1 GB of RAM for the OS
• 1 GB of RAM per every 4 GB of RAM installed (up to 16-GB RAM)
• 1 GB of RAM per every 8-GB RAM installed (above 16-GB RAM)

Việc thiết lập có thể thực hiện bằng giao diện hoặc bằng dòng lệnh, ví dụ thiết lập max server memory là 13GB:



sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 13312;
GO
RECONFIGURE;
GO

Cấu hình Lock pages in memory (LPIM)

Xác định service account của SQL Server bằng truy vấn:
SELECT service_account FROM sys.dm_server_services

Sau đó cấu hình Group Policy theo đường dẫn:

Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment\Lock pages in memory

Hiệu quả sau khi thay đổi cấu hình

Trước khi thay đổi cấu hình, sau khoảng 5 ngày thì hệ điều hành sử dụng 50% page file, sau 20 ngày sử dụng đến 80%.

Theo chu kỳ chưa đến 1 tháng, phần mềm kết nối vào database này để truy xuất thông tin bị chậm. Và thông thường, người quản trị hệ thống có thể khởi động lại máy chủ để tạm thời giải quyết vấn đề này.



Sau khi thay đổi cấu hình, hệ thống gần như không sử dụng page file dù cho RAM vật lý chỉ còn rất ít (1.17 GB / 17BG):

Truy vấn thông tin sử dụng bộ nhớ bằng SSMS:

--Windows OS memory
SELECT
(total_physical_memory_kb/1024) AS Total_OS_Memory_MB,
(available_physical_memory_kb/1024) AS Available_OS_Memory_MB
FROM sys.dm_os_sys_memory;

--SQL Server memory
SELECT
physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
page_fault_count AS sql_page_fault_count,
memory_utilization_percentage AS sql_memory_utilization_percentage,
process_physical_memory_low AS sql_process_physical_memory_low,
process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

--Windows OS pagefile
DECLARE @Result TABLE (WindowsPageFile NVARCHAR(4000));
INSERT INTO @Result
EXEC xp_cmdshell 'powershell -command "Get-CimInstance Win32_PageFileUsage | Select-Object Name,AllocatedBaseSize,CurrentUsage"';

SELECT WindowsPageFile
FROM @Result
WHERE WindowsPageFile IS NOT NULL;

Kết luận

Đối với các máy chủ database SQL Server, cơ chế quản lý bộ nhớ tự động giữa hệ điều hành Windows và SQL Server thường không tối ưu cho hiệu năng truy xuất database, hệ thống nhanh chóng sử dụng bổ nhớ ảo trên đĩa cứng gây ra tình trạng phần mềm hoạt động chậm chạp.

Bài viết này đã đề cập đến 2 cấu hình quan trọng là LPIM và max server  memory để ngăn paging xảy ra, một phần quan trọng trong hành trình quản trị và tối ưu hiệu suất của máy chủ cơ sở dữ liệu.

Tài liệu tham khảo:

Enable the Lock pages in memory option (Windows)

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver15

MSSQLSERVER_17890

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-17890-database-engine-error?view=sql-server-ver16

Server memory configuration options

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver16

SQL Server Design Considerations

https://learn.microsoft.com/en-us/system-center/scom/plan-sqlserver-design?view=sc-om-2022

máy chủ

danh từ: người dọn ăn, người giao banh

Smile PMS: Tăng hiệu suất quản trị bằng truy vấn SQL Server

Smile PMS là một bộ phần mềm quản lý khách sạn nổi tiếng ở Việt Nam. Trong quá trình vận hành, IT khách sạn có thể gặp khó khăn trong một số đầu việc như sửa lỗi hoặc kiểm soát phân quyền.

Smile PMS sử dụng SQL Server database, do đó, nếu có kỹ năng về quản lý SQL Server thì bạn sẽ tiết kiệm được rất nhiều thời gian trong quản trị hệ thống và hỗ trợ vận hành phần mềm.

Khuyến cáo: Làm việc trực tiếp với database rất nguy hiểm nếu có sai sót, do đó IT nên restore các bản backup để làm việc trên môi trường thử nghiệm.

Sau đây là một số tình huống và hướng dẫn xử lý, cập nhật thời điểm 03/2025:

1. Tìm lỗi nhập liệu

Lỗi vì nhập liệu quá dài vượt quá phạm vi cho phép là lỗi rất phiền toái, rất hay xảy ra đối với Smile, đặc biệt là thời điểm sáng sớm, làm IT bị réo tên khi còn chưa mở mắt. Nguyên nhân là phía Smile lập trình không tốt, không có lồng điều kiện xác thực đầu vào để ngăn người dùng nhập quá kí tự, và Smile cũng thường đặt số kí tự cho phép khá nhỏ.

Phần note cho từng folio trước đây được thiết lập tối đa 760 kí tự rất hay bị lỗi, gần đây họ đã tăng lên 1500 kí tự đã thoải mái hơn trước.

Tuy nhiên, các trường khác có thể gặp lỗi như FirstName, LastName. Sau đây là các ví dụ để tìm các trường hay gặp lỗi nhập liệu vượt quá kí tự:

Tìm các folio có trường vượt quá số kí tự cho phép có ngày Arrival từ 08/03/2025 trở đi:

Đối với khách lẻ:

SELECT
FolioNum,ArrivalDate,DepartureDate,RoomCode,Notice,ConfirmNum,ReservationClerkID,LastName,FirstName
FROM SMILE_FO.dbo.Folio
WHERE ArrivalDate >= '2025-03-08'
AND (LEN(Notice) >= '1500'
OR LEN(FirstName) >= '100'
OR LEN(LastName) >= '100'
);

Đối với khách đoàn:

SELECT GroupCode,GroupName,FirstArrivalDate,PrivateRemark,CompanyName,CreateClerk
FROM SMILE_FO.dbo.GroupFolio
WHERE FirstArrivalDate >= '2025-03-08'
AND (Len(GroupName) >= '100'
OR Len(PrivateRemark) >= '1000'
);

2. Truy vấn các sai sót trong phân quyền

Vì việc phân quyền trên giao diện đồ họa của Smile hơi rườm rà và khó nhìn, do đó IT có thể xảy ra sai sót khi tiến hành phân quyền. Nhiều khi bảng phân quyền có sự thay đổi, hoặc IT mới vào thay IT cũ, việc rà soát lại các phân quyền là công việc thường xuyên phải làm.

Hiển thị phân quyền của một user:

SELECT *
FROM [SMILE_FO].[dbo].[ClerkPermission]
WHERE ClerkID = 'username'

Danh sách user (chỉ ID) đang hoạt động được phân các quyền cụ thể:

SELECT *
FROM [SMILE_FO].[dbo].[ClerkPermission]
WHERE FunctionID in ('ROverBookAll', 'CHTransactionAudit')
AND ClerkID IN (
SELECT [ClerkID]
FROM [SMILE_FO].[dbo].[Clerk]
WHERE DisabledFlag='0'
)

Lệnh trên sẽ liệt kê những user đang có quyền ROverBookAll hoặc CHTransactionAudit.

Danh sách user (hiển thị thêm LastName) đang hoạt động được phân các quyền cụ thể:

SELECT ClerkPermission.*, Clerk.LastName
FROM [SMILE_FO].[dbo].[ClerkPermission]
JOIN [SMILE_FO].[dbo].[Clerk]
ON ClerkPermission.ClerkID = Clerk.ClerkID
WHERE ClerkPermission.FunctionID in ('ROverBookAll', 'OverBookType')
AND Clerk.DisabledFlag='0'

hoặc

SELECT cp.*, c.LastName
FROM [SMILE_FO].[dbo].[ClerkPermission] cp
JOIN [SMILE_FO].[dbo].[Clerk] c
ON cp.ClerkID = c.ClerkID
WHERE cp.FunctionID in ('FDFutureAvailability', 'MONightAudit')
AND c.DisabledFlag='0'

Lịch sử thay đổi phân quyền của một user:

SELECT *
FROM [SMILE_FO].[dbo].[ClerkChanges]
WHERE ClerkID = 'username'

Lời kết: Từ những truy vấn trên, chúng ta có thể thực hiện các truy vấn khác tùy nhu cầu công việc, và sửa đổi lại thông tin trong database nếu có đủ quyền hạn và kỹ năng để đảm bảo an toàn cho dữ liệu.