MS SQL Server - Tăng bộ nhớ được cấp phát

Question

Vấn đề của tôi là SQL Server mất nhiều thời gian để tăng mức sử dụng bộ nhớ của nó đối với các trường hợp có RAM trị giá TB, trong khi chúng tôi nhận được sự chờ đợi không liên tục của MEMORY_ALLOCATION_EXT làm chậm quá trình xử lý của chúng tôi cho đến khi SQL Server đạt đến bộ nhớ tối đa.

Chúng tôi có Phiên bản cụm chuyển đổi dự phòng (FCI) của SQL Server 2019 Enterprise Edition với bộ nhớ đáng giá hàng terabyte trên các nút. Trong các trường hợp sử dụng thông thường, chúng tôi chỉ cho phép 1 phiên bản SQL Server trên mỗi nút và do đó chúng tôi đặt bộ nhớ máy chủ tối đa gần ~ 85% bộ nhớ trên nút, nhưng chúng tôi cũng đặt bộ nhớ máy chủ tối thiểu tương đối thấp đề phòng trường hợp máy chủ SQL bị lỗi đến một nút khác và cần được đưa trực tuyến với dung lượng bộ nhớ được giảm thiểu để đi khập khiễng.

• Tôi khá biết rằng nếu tôi đặt bộ nhớ tối thiểu lên cao hơn thì SQL Server sẽ tiêu thụ bộ nhớ cùng một lúc. Hóa ra SQL Server không cấp phát bộ nhớ tối thiểu khi khởi động.
• Tôi biết rằng SQL Server sẽ tự động tiêu thụ nhiều bộ nhớ hơn từ Hệ điều hành khi nó cần và cuối cùng nó sẽ đạt đến bộ nhớ máy chủ tối đa.
• Tôi biết rằng việc chạy một truy vấn lớn hoặc DBCC checkdb kéo nhiều dữ liệu sẽ buộc SQL Server tiêu tốn nhiều bộ nhớ hơn từ Hệ điều hành.

Có cách nào khác để buộc SQL Server tăng mức sử dụng bộ nhớ của nó một cách nhanh chóng không?

Answer 1

Tôi khá biết rằng nếu tôi đặt bộ nhớ tối thiểu lên cao hơn thì SQL Server sẽ tiêu thụ bộ nhớ cùng một lúc.

Không, điều này không chính xác, bộ nhớ máy chủ tối thiểu không liên quan gì đến tốc độ SQL Server sẽ sử dụng nó nếu được đặt. Bộ nhớ máy chủ tối thiểu biểu thị lượng bộ nhớ tối thiểu, nếu được tiêu thụ, sau đó SQL Server sẽ không giải phóng bộ nhớ dưới giá trị này trong điều kiện bình thường nếu được yêu cầu làm như vậy. Khi đạt đến giá trị máy chủ tối thiểu, SQL Server sẽ không cắt bộ nhớ đệm và giải phóng bộ nhớ dưới giá trị này.

Tôi biết rằng SQL Server sẽ tự động tiêu thụ nhiều bộ nhớ hơn từ Hệ điều hành khi nó cần và cuối cùng nó sẽ đạt đến bộ nhớ máy chủ tối đa.

Đúng vậy, chỉ cần thêm SQl Server trong một số điều kiện nhất định cũng có thể tiêu thụ bộ nhớ lớn hơn giá trị được chỉ định trong bộ nhớ máy chủ tối đa. Bạn nên đọc Hướng dẫn quản lý bộ nhớ

Có cách nào khác để buộc SQL Server tăng mức sử dụng bộ nhớ của nó một cách nhanh chóng không?

Có một cách khác buộc SQL Server phải dự trữ nhiều bộ nhớ hơn trong khi khởi động và điều này được gọi là " trang lớn ". Đây là các tính năng của phiên bản doanh nghiệp và cho phép SQL Server nhanh chóng dự trữ một số bộ nhớ trống trong quá trình khởi động, tài khoản dịch vụ SQL Server cũng phải có các trang bị khóa trong đặc quyền bộ nhớ vì việc phân bổ các trang lớn được thực hiện bởi chức năng VirtualAlloc () của Windows OS và OS nên có hơn 8 GB RAM, nếu các điều kiện này được đáp ứng sẽ tự động bật Phân bổ trang lớn. Sau khi được kích hoạt, nó sẽ xuất hiện như bên dưới trong errorlog

2009-06-04 12:21:08.16 Server      Large Page Extensions enabled.
2009-06-04 12:21:08.16 Server      Large Page Granularity: 2097152
2009-06-04 12:21:08.21 Server      Large Page Allocated: 32MB

Kích thước trang bình thường của bộ nhớ là 4 KB trên hệ thống X64 nhưng khi kích hoạt trang lớn, kích thước sẽ là 2 MB. Cũng lưu ý rằng LargePageSupport được kích hoạt và sử dụng bởi công cụ ngay cả khi bạn không bật cờ theo dõi 834. Nhưng không có nhiều bộ nhớ được sử dụng cho việc này và bộ nhớ vùng đệm sẽ không được sử dụng trừ khi cờ theo dõi 834 được bật. Xem blog đã chia sẻ.

Answer 2

SQL Server sẽ không sử dụng bộ nhớ cho vùng đệm (công dụng lớn nhất của nó) cho đến khi nó cần tải các trang từ bộ nhớ vì chúng được tham chiếu. Trừ khi bạn đang làm điều gì đó kỳ quặc để làm cho việc phân bổ bộ nhớ chậm lại ^[†] thì sự chậm trễ của bạn khó có thể xảy ra ngoài việc đọc dữ liệu mà nó đang được cấp phát để lưu giữ. Tôi nghi ngờ rằng cùng lúc với các lần chờ cấp phát bộ nhớ đó, bạn có các lần chờ liên quan đến IO phù hợp.

Để buộc máy chủ SQL tải mọi thứ vào bộ nhớ và do đó để cấp phát bộ nhớ nếu có sẵn và cần thiết, hãy truy cập những thứ cần được tải. Sau khi khởi động phiên bản SQL của bạn, hãy chạy khối lượng công việc chỉ đọc để tập hợp làm việc chung bình thường của ứng dụng của bạn được tải vào vùng đệm.

Đừng chỉ SELECT * FROM HugeTablevì điều này có khả năng tải số dư dữ liệu kém (không phải tập hợp làm việc cốt lõi của ứng dụng), vì vậy tập hợp làm việc cốt lõi vẫn cần được tải từ đĩa / mạng vào lần truy cập tiếp theo để bạn sẽ có cùng IO sự chậm trễ. Ngoài ra, nếu HugeTableSQL Server thực sự lớn có thể thấy rằng nó sẽ không phù hợp với tất cả và dù sao cũng không bận tâm đến việc giữ một lượng lớn nó trong bộ nhớ [‡].

^{[†] Chạy trong một máy ảo trên một máy chủ quá tải, vì vậy những gì trông giống như RAM thực thực sự là các trang trên đĩa ?!}

^{[‡] Trong những trường hợp bình thường, bạn không muốn quét bảng / chỉ mục bất ngờ trên một đối tượng lớn để loại bỏ mọi thứ khác khỏi bộ nhớ. Giới hạn về số lượng được giữ từ một đối tượng cho một truy vấn là tối ưu hóa để cố gắng ngăn điều này xảy ra).}