Tối ưu hóa lưu trữ cơ sở dữ liệu

  • 5 phút

Để tối ưu hóa lưu trữ cơ sở dữ liệu, bạn nên xem xét việc điền theo tỷ lệ và cấu hình tempdb.

Hiểu rõ hiệu suất I/O

Hiệu năng I/O có thể rất quan trọng đối với ứng dụng cơ sở dữ liệu. Azure SQL tóm tắt bạn từ vị trí tệp vật lý, nhưng có các phương pháp để đảm bảo bạn có được hiệu suất I/O mà bạn cần.

Đầu vào/đầu ra trên giây (IOPS) có thể quan trọng đối với ứng dụng của bạn. Hãy đảm bảo bạn đã chọn đúng bậc dịch vụ và vCores cho nhu cầu IOPS của mình. Hiểu cách đo lường IOPS cho truy vấn của bạn tại chỗ nếu bạn đang di chuyển sang Azure. Nếu bạn có hạn chế về IOPS, bạn có thể thấy chờ I/O dài. Trong mô hình mua vCore, bạn có thể mở rộng vCores hoặc chuyển sang Business Critical hoặc Hyperscale nếu bạn không có đủ IOPS. Đối với khối lượng công việc sản xuất, khi sử dụng DTU, chúng tôi khuyên bạn nên chuyển sang tầng Premium.

Độ trễ I/O là một thành phần quan trọng khác cho hiệu năng I/O. Để có độ trễ I/O nhanh hơn cho Cơ sở dữ liệu Azure SQL, hãy xem xét Cấp độ quan trọng của Doanh nghiệp hoặc Siêu cấp. Để có độ trễ I/O nhanh hơn cho Phiên bản được Quản lý của SQL, hãy di chuyển đến Business Critical hoặc tăng kích cỡ tệp hoặc số lượng tệp cho cơ sở dữ liệu. Cải thiện độ trễ nhật ký giao dịch có thể yêu cầu bạn phải sử dụng các giao dịch đa thống kê.

Tệp và nhóm tệp

Các chuyên gia SQL Server thường sử dụng tệp và nhóm tệp để cải thiện hiệu suất I/O thông qua vị trí tệp vật lý. Azure SQL không cho phép người dùng đặt tệp trên các hệ thống đĩa cụ thể. Tuy nhiên, Azure SQL có cam kết tài nguyên đối với hiệu suất I/O liên quan đến tỷ lệ, IOPS và độ trễ. Bằng cách này, việc tóm tắt người dùng từ vị trí tệp vật lý có thể mang lại lợi ích.

Cơ sở dữ liệu Azure SQL chỉ có một tệp cơ sở dữ liệu (Siêu cấp thường có một vài tệp) và kích cỡ tối đa được đặt cấu hình thông qua giao diện Azure. Không có chức năng để tạo thêm tệp.

Phiên bản được Quản lý của Azure SQL hỗ trợ thêm tệp cơ sở dữ liệu và định cấu hình kích cỡ, nhưng không hỗ trợ vị trí vật lý của tệp. Bạn có thể sử dụng số lượng tệp và kích cỡ tệp cho Phiên bản được Quản lý của SQL để cải thiện hiệu suất I/O. Ngoài ra, nhóm tệp do người dùng xác định được hỗ trợ cho Phiên bản được Quản lý SQL cho mục đích quản lý.

Mô tả tô theo tỷ lệ

Khi chèn 1 gigabyte dữ liệu vào cơ sở dữ liệu SQL Server với hai tệp dữ liệu, bạn có thể kỳ vọng mỗi tệp sẽ tăng khoảng 512 megabyte. Tuy nhiên, không phải lúc nào cũng xảy ra trường hợp này. SQL Server phân phối dữ liệu dựa trên kích cỡ của từng tệp. Ví dụ: nếu cả hai tệp dữ liệu đều có 2 gigabyte, dữ liệu sẽ được phân phối đều. Nhưng nếu một tệp là 10 gigabyte và tệp còn lại là 1 gigabyte, khoảng 900 MB sẽ đi vào tệp lớn hơn và 100 MB thành tệp nhỏ hơn. Hành vi này là phổ biến trong bất kỳ cơ sở dữ liệu, nhưng trong tempdb viết chuyên sâu, một mẫu viết không đồng đều có thể tạo ra một cổ chai trong tập tin lớn nhất, vì nó xử lý ghi nhiều hơn.

Cấu hình Tempdb trong SQL Server

SQL Server phát hiện số CPU sẵn dùng trong quá trình thiết lập và đặt cấu hình số lượng tệp thích hợp, tối đa tám tệp, thậm chí có thể chỉnh kích cỡ. Ngoài ra, các hành vi của cờ theo dõi 1117 và 1118 được tích hợp vào công cụ cơ sở dữ liệu, nhưng chỉ cho tempdb. Đối với khối lượng công việc tempdb-heavy, có thể hữu ích nếu tăng số lượng tệp tempdb vượt quá 8, phù hợp với số LƯỢNG CPU trên máy tính của bạn.

Bạn sử tempdb sử dụng cùng một cách cho cả SQL Server và Azure SQL. Tuy nhiên, lưu ý rằng khả năng đặt cấu hình tempdb của bạn là khác nhau, bao gồm vị trí của tệp, số lượng và kích cỡ tệp cũng như tùy tempdb cấu hình khác.

SQL Server sử dụng tempdb cho các tác vụ khác nhau ngoài việc chỉ lưu trữ các bảng tạm thời do người dùng xác định. Nó được sử dụng cho các bảng công việc lưu trữ kết quả truy vấn trung gian, thao tác sắp xếp và kho phiên bản để lập phiên bản hàng, cùng với các mục đích khác. Do việc sử dụng rộng rãi này, điều quan trọng là phải đặt tempdb trên dung lượng lưu trữ độ trễ thấp nhất có sẵn và đặt cấu hình đúng cách các tệp dữ liệu của nó.

Các tệp cơ sở dữ liệu tempdb luôn được tự động lưu trữ trên ổ đĩa SSD cục bộ, vì vậy hiệu suất I/O không nên là sự cố.

Các chuyên gia SQL Server thường sử dụng nhiều tệp cơ sở dữ liệu để phân bổ phân vùng tempdb bảng. Đối với Cơ sở dữ liệu Azure SQL, số tệp được xác định tỷ lệ với số vCore (ví dụ: hai vCores bằng bốn tệp) với tối đa 16. Số lượng tệp không thể cấu hình thông qua T-SQL dựa trên tempdb, nhưng bạn có thể cấu hình tệp bằng cách thay đổi tùy chọn triển khai. Kích cỡ tối đa của tempdb được chia theo số vCores. Bạn nhận được 12 tệp với Phiên bản được Quản lý của SQL, độc lập với vCores.

Tùy chọn cơ MIXED_PAGE_ALLOCATION được đặt thành TẮTvà AUTOGROW_ALL_FILES được đặt thành BẬT. Bạn không thể cấu hình điều này, nhưng với SQL Server, đây là những mặc định được đề xuất.

Tính năng tối ưu hóa siêu dữ liệu tempdb được giới thiệu trong SQL Server 2019, tính năng này có thể làm giảm bớt sự chốt lại nặng nề, hiện không sẵn dùng trong Cơ sở dữ liệu Azure SQL hoặc Phiên bản được quản lý của Azure SQL.

Cấu hình cơ sở dữ liệu

Thông thường, bạn cấu hình một cơ sở dữ liệu với T-SQL ALTER DATABASE và ALTER DATABASE SCOPED CONFIGURATION các câu lệnh. Nhiều tùy chọn cấu hình cho hiệu suất có sẵn cho Azure SQL. Tham khảo liệu ALTER DATABASE và ALTER DATABASE SCOPED CONFIGURATION để biết sự khác biệt giữa SQL Server, Azure SQL Database và Azure SQL Managed Instance.

Trong Cơ sở dữ liệu Azure SQL, mô hình khôi phục mặc định phục hồi hoàn toàn, đảm bảo rằng cơ sở dữ liệu của bạn có thể đáp ứng các thỏa thuận cấp dịch vụ Azure (SLAs). Điều này có nghĩa là không hỗ trợ ghi nhật ký tối thiểu cho các thao tác hàng loạt, tempdbngoại trừ , trong đó cho phép ghi nhật ký tối thiểu.

Cấu hình MAXDOP

Mức độ song song tối đa (MAXDOP) có thể ảnh hưởng đến hiệu suất của các truy vấn riêng lẻ. Xử lý SQL Server và Azure SQL MAXDOP theo cùng một cách. Khi được MAXDOP đặt thành giá trị cao hơn, các chuỗi hội thoại song song hơn sẽ được sử dụng cho mỗi truy vấn, có khả năng tăng tốc thực thi truy vấn. Tuy nhiên, song song này đòi hỏi thêm tài nguyên bộ nhớ, có thể dẫn đến áp lực bộ nhớ và ảnh hưởng đến hiệu suất lưu trữ. Ví dụ, khi nén các nhóm hàng vào một columnstore, parallelism đòi hỏi nhiều bộ nhớ, có thể dẫn đến áp lực bộ nhớ và rowgroup cắt tỉa.

Ngược lại, việc đặt MAXDOP về giá trị thấp hơn có thể làm giảm áp lực bộ nhớ, cho phép hệ thống lưu trữ hoạt động hiệu quả hơn. Điều này rất quan trọng trong các môi trường có tài nguyên bộ nhớ hạn chế hoặc nhu cầu lưu trữ cao. Bằng cách đặt cấu hình cẩn thận MAXDOP, bạn có thể cân bằng hiệu suất truy vấn và hiệu quả lưu trữ, đảm bảo sử dụng tối ưu cả tài nguyên CPU và lưu trữ.

Bạn có thể đặt cấu hình MAXDOP trong Azure SQL, tương tự như SQL Server bằng cách sử dụng các kỹ thuật sau:

  • ALTER DATABASE SCOPED CONFIGURATION để đặt cấu MAXDOP hình được hỗ trợ cho Azure SQL.
  • Thủ tục lưu trữ sp_configure cho "mức độ song song tối đa" được hỗ trợ cho Phiên bản được quản lý của SQL.
  • MAXDOP gợi ý truy vấn được hỗ trợ đầy đủ.
  • Cấu hình với MAXDOP Thống đốc Tài nguyên được hỗ trợ cho Phiên bản được Quản lý SQL.