Computer >> Hướng Dẫn Máy Tính >  >> Lập Trình >> Cơ Sở Dữ Liệu

Quản lý cảnh báo áp lực bộ nhớ từ bảng tối ưu hóa bộ nhớ trong SQL Server

Microsoft SQL Server rất thông minh trong quản lý bộ nhớ, nhưng đôi khi cảnh báo áp lực bộ nhớ và các yêu cầu bộ nhớ của database engine dẫn đến lỗi.

Giới thiệu

Bài viết này thảo luận cách giải quyết một trong những tình huống ngoại lệ bạn có thể gặp do áp lực bộ nhớ trên SQL Server® 2019 (Enterprise Edition) do các bảng tối ưu hóa bộ nhớ (In-memory OLTP). Các bước tương tự áp dụng cho SQL Server 2014 và các phiên bản mới hơn.

Bạn có thể thấy các thông báo lỗi sau xuất hiện trên màn hình:

Message: MSSQL on Windows: Stolen Server Memory is too high Source: XXXXX\MSSQLSERVER Path: Not Present Alert description: SQL instance "MSSQLSERVER" Stolen Server Memory on computer "XXXXXXX.XXX.com" is too high.
Message: SQL Server Alert System: 'Severity 17' occurred on \\XXXXXXX DESCRIPTION: There is insufficient system memory in resource pool 'internal' to run this query.
Message: Disallowing page allocations for database 'InMemoryDB' due to insufficient memory in the resource pool 'default'. See 'https://go.microsoft.com/fwlink/?LinkId=510837' for more information.
Message: XTP failed page allocation due to memory pressure: FAIL_PAGE_ALLOCATION 32

Giải pháp

Thực hiện các bước sau để khắc phục sự cố:

Bước một: Kiểm tra tiêu thụ bộ nhớ trong SQL Buffer Pool

Đầu tiên, hãy kiểm tra mức tiêu thụ bộ nhớ trong SQL Buffer Pool.

Quản lý cảnh báo áp lực bộ nhớ từ bảng tối ưu hóa bộ nhớ trong SQL Server

Như hình ảnh cho thấy, database InMemoryDB chỉ tiêu thụ 0,017% buffer pool.

Bước hai: Kiểm tra OS Memory Clerks

Tiếp theo, kiểm tra OS Memory Clerks bằng lệnh T-SQL sau:

SELECT * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC;
Quản lý cảnh báo áp lực bộ nhớ từ bảng tối ưu hóa bộ nhớ trong SQL Server

Kết quả cho thấy tổng tiêu thụ của các consumer hàng đầu chiếm khoảng 80% tổng max server memory.

Kích thước bảng tối ưu hóa bộ nhớ cũng nhỏ hơn 2 GB (xem mục DB_ID_6 trong hình), do đó lý thuyết không nên có áp lực bộ nhớ.

Bước ba: Gắn database vào Resource Pool

Sau khi xem các liên kết Out of Memory (OOM) trong error log (https://go.microsoft.com/fwlink/?LinkId=510837), bạn cần gắn database có bảng tối ưu hóa bộ nhớ vào một resource pool. Đây là phương pháp tốt nhất cho các database như vậy. Làm theo các bước để tạo resource pool trong Resource Governor và gắn database.

Best practice khuyến nghị bảo vệ SQL Server khỏi việc bị tiêu thụ tài nguyên bởi một hoặc nhiều bảng tối ưu hóa bộ nhớ, đồng thời ngăn các consumer bộ nhớ khác chiếm dụng bộ nhớ cần thiết cho các bảng đó. Do đó, nên tạo resource pool riêng để quản lý tiêu thụ bộ nhớ cho database chứa bảng tối ưu hóa bộ nhớ.

Lưu ý khi thêm database vào resource pool:

  • Một database chỉ gắn được vào một resource pool.
  • Nhiều database có thể gắn vào cùng một pool.
  • Có thể gắn database không có bảng tối ưu hóa bộ nhớ vào resource pool, nhưng không có tác dụng.
  • Có thể tạo bảng tối ưu hóa bộ nhớ trong database sau khi đã gắn vào resource pool.

Các bước gắn vào resource pool

  1. Tạo Resource Pool với cấp phát bộ nhớ:

    USE [master]
GO
CREATE RESOURCE POOL [Admin_Pool] WITH
(min_cpu_percent=0, max_cpu_percent=100,
 min_memory_percent=15, max_memory_percent=15,
 cap_cpu_percent=100, AFFINITY SCHEDULER = AUTO,
 min_iops_per_volume=0, max_iops_per_volume=0)
GO

    Lưu ý: Để tránh tình trạng out-of-memory, giá trị min_memory_percent và max_memory_percent nên giống nhau.

    Trong trường hợp này, bảng tối ưu hóa bộ nhớ rất nhỏ, cấp 15% tổng bộ nhớ server cho resource pool. Đừng quên dùng các liên kết tham khảo để tính toán phần trăm bộ nhớ phù hợp cho môi trường của bạn.

  2. Xác minh resource pool và gắn database:

    EXEC sp_xtp_bind_db_resource_pool 'InMemoryDB', 'Admin_Pool'
GO

  3. Kiểm tra gắn kết trong sys.databases:

    SELECT d.database_id, d.name, d.resource_pool_id
FROM sys.databases d
GO
    Quản lý cảnh báo áp lực bộ nhớ từ bảng tối ưu hóa bộ nhớ trong SQL Server

  4. Khởi động lại database để gắn kết có hiệu lực:

    ALTER DATABASE DB_Name SET OFFLINE
GO
ALTER DATABASE DB_Name SET ONLINE
GO

Lưu ý: Nếu database nằm trong Always On, thực hiện các bước trên cả hai node, và thay vì Bước 4 (khởi động lại database), hãy thực hiện failover database sang instance secondary.

Kết luận

Trong trường hợp này, tất cả cảnh báo liên quan đến áp lực bộ nhớ đã dừng lại sau khi thêm các database có bảng tối ưu hóa bộ nhớ vào resource pool. Tôi đã theo dõi SQL Server error logs trong vài tuần và không thấy dấu hiệu nào của áp lực bộ nhớ. Các bước này giúp khắc phục áp lực bộ nhớ ở cấp độ database engine với downtime tối thiểu.

Sử dụng tab Feedback để bình luận hoặc đặt câu hỏi. Bạn cũng có thể bắt đầu cuộc trò chuyện với chúng tôi.