(Được đăng lần đầu trên DBA.StackExchange.com nhưng đã đóng, hy vọng sẽ phù hợp hơn ở đây.)
Alexander và những dự phòng Kinh khủng, Kinh khủng, Không tốt, Rất tệ...
Thiết lập:
Tôi có một cơ sở tại chỗ Phiên bản tiêu chuẩn SQL Server 2016 ví dụ chạy trên một máy ảo từ VMware.
@@Phiên bản:
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64)
Ngày 19 tháng 3 năm 2021 19:41:38 Bản quyền (c) Microsoft Corporation Standard
Phiên bản (64-bit) trên Windows Server 2016 Datacenter 10.0 (Bản dựng
14393: ) (Nhà ảo thuật)
Bản thân máy chủ hiện được phân bổ 8 bộ xử lý ảo, có 32GB bộ nhớ, và tất cả đĩa là NVMe mà có được xung quanh 1 GB/giây I/O. Bản thân cơ sở dữ liệu nằm trên ổ G: và các bản sao lưu được lưu trữ riêng trên ổ P:. Tổng kích thước trên tất cả các cơ sở dữ liệu là khoảng 500 GB (trước khi được nén vào các tệp sao lưu).
Kế hoạch bảo trì chạy mỗi đêm một lần (khoảng 10:30 tối) để thực hiện sao lưu toàn bộ mọi cơ sở dữ liệu trên máy chủ. Không có gì khác thường đang chạy trên máy chủ, cũng như không có bất kỳ thứ gì khác đang chạy vào thời điểm đó nói riêng. Power Plan tắt máy chủ được đặt thành "Cân bằng" (và "Tắt đĩa cứng sau" được đặt thành 0 phút hay còn gọi là không bao giờ tắt).
Chuyện gì đã xảy ra:
Trong khoảng một năm qua, tổng thời gian chạy cho công việc lập kế hoạch bảo trì mất khoảng 15 phút tổng số để hoàn thành. Kể từ tuần trước, nó đã tăng vọt lên gấp khoảng 40 lần, khoảng 15 giờ hoàn thành.
Điều duy nhất tôi biết về việc thay đổi vào cùng ngày kế hoạch bảo trì bị chậm lại là các bản cập nhật Windows sau đây đã được cài đặt trên máy trước khi kế hoạch bảo trì chạy:
- KB890830
- KB5004752
- KB5005043
- VMWare - SCSIAd CHƯƠNG - 1.3.17.0
- VMWare - Hiển thị - 8.17.2.14
Chúng tôi cũng có một phiên bản SQL Server được cung cấp tương tự khác trên một máy ảo khác đã trải qua các bản cập nhật Windows tương tự và sau đó cũng gặp phải các bản sao lưu chậm hơn sau đó. Nghĩ rằng các bản cập nhật Windows là nguyên nhân trực tiếp, chúng tôi đã khôi phục chúng hoàn toàn và dù sao thì kế hoạch bảo trì bản sao lưu vẫn chạy rất chậm. Thật kỳ lạ, việc khôi phục các bản sao lưu cho một cơ sở dữ liệu nhất định diễn ra rất nhanh và sử dụng gần như toàn bộ 1 GB/giây I/O trên NVMe.
Những điều tôi đã thử:
Khi sử dụng sp_whoisactive của Adam Mechanic, tôi đã xác định rằng Loại chờ cuối cùng của các quy trình sao lưu luôn là dấu hiệu cho thấy vấn đề về hiệu suất của đĩa.tôi luôn thấy BỘ ĐỆM DỰ PHÒNG và SAO LƯU các loại chờ đợi, ngoài ASYNC_IO_COMPLETION:
Khi nhìn vào Trình giám sát tài nguyên trên chính máy chủ, trong quá trình sao lưu, phần I/O của đĩa cho thấy tổng I/O được sử dụng chỉ khoảng 14 MB/giây (mức lớn nhất tôi từng thấy kể từ khi sự cố này xảy ra là 30 MB/giây):
Sau khi tình cờ thấy điều này hữu ích Bài viết của Brent Ozar về việc sử dụng DiskSpd, tôi đã thử tự chạy nó với các tham số tương tự (chỉ giảm số lượng luồng xuống 8 vì tôi có 8 bộ xử lý ảo trên máy chủ và đặt tốc độ ghi thành 50%). Đây là lệnh chính xác diskspd.exe -b2M -d60 -o32 -h -L -t8 -W -w50 "C:\Users\...\Desktop\Microsoft DiskSpd\Test\LargeFile.txt". Tôi đã sử dụng một tệp văn bản mà tôi đã tạo theo cách thủ công có dung lượng dưới 1 GB. Tôi tin rằng I/O mà nó đo được có vẻ ổn, nhưng độ trễ của đĩa đang hiển thị một số con số lố bịch:
Kết quả DiskSpd dường như không thể tin được. Sau khi đọc thêm, tôi tình cờ thấy một truy vấn từ Paul Randall trả về số liệu độ trễ của đĩa trên mỗi cơ sở dữ liệu. Đây là kết quả:
Độ trễ ghi tồi tệ nhất là 63 mili giây và Độ trễ đọc tồi tệ nhất là 6 mili giây, do đó, đó dường như là một sự khác biệt lớn so với DiskSpd và dường như không đủ khủng khiếp để trở thành nguyên nhân gốc rễ của vấn đề của tôi. Kiểm tra chéo mọi thứ hơn nữa, tôi đã chạy một vài bộ đếm PerfMon trên chính máy chủ, mỗi bài viết này của Microsoft, và đây là kết quả:
Không có gì bất thường ở đây, giá trị tối đa của tất cả các bộ đếm mà tôi đo được là 0,007 (mà tôi tin là mili giây?). Cuối cùng, tôi đã yêu cầu nhóm Cơ sở hạ tầng của mình kiểm tra các số liệu về độ trễ của đĩa mà VMWare đã ghi trong quá trình sao lưu và đây là kết quả:
Có vẻ như trong trường hợp tệ nhất, độ trễ tăng đột biến khoảng 200 mili giây vào khoảng nửa đêm và tốc độ I/O cao nhất là 600 KB/giây (điều mà tôi không thực sự hiểu vì Trình theo dõi tài nguyên cho thấy rằng các bản sao lưu đang sử dụng ít nhất khoảng 14 MB/giây của I/O).
Những thứ khác tôi đã thử:
Tôi vừa thử khôi phục một trong những cơ sở dữ liệu lớn hơn (khoảng 250 GB) và chỉ mất tổng cộng khoảng 8 phút để khôi phục. Sau đó, tôi đã thử chạy DBCC KIỂM TRADB trên đó và mất tổng cộng 16 phút để chạy (không chắc điều này có bình thường không) nhưng Trình giám sát tài nguyên cho thấy các sự cố I/O tương tự (I/O nhiều nhất mà nó từng sử dụng là 100 MB/s), không có gì khác đang chạy:
Đây là kết quả sp_whoisactive khi tôi chạy lần đầu DBCC KIỂM TRADB và sau đó sau khi hoàn thành 5%, hãy thông báo Thời gian còn lại ước tính tăng khoảng 5 phút ngay cả khi đã hoàn thành 5%.
Bắt đầu:
Hoàn thành 5%:
Tôi đoán điều này là bình thường vì nó chỉ là ước tính và 16 phút dường như không quá tệ đối với cơ sở dữ liệu 250 GB (mặc dù tôi không chắc điều đó có bình thường không) nhưng một lần nữa, I/O chỉ đạt tối đa ở khoảng 10% khả năng của ổ đĩa, không có gì khác đang chạy trên máy chủ hoặc phiên bản SQL.
Đây là những kết quả của DBCC KIỂM TRADB, không có lỗi nào được báo cáo.
Tôi cũng đã gặp phải các vấn đề chậm chạp kỳ lạ với CO LẠI chỉ huy. tôi chỉ cố gắng để CO LẠI cơ sở dữ liệu có 5% dung lượng để giải phóng (khoảng 14 GB). Chỉ mất khoảng 1 phút để nó hoàn thành 90% công việc CO LẠI:
Khoảng 5 phút sau, và nó vẫn bị kẹt ở cùng một phần trăm hoàn thành và Sao lưu nhật ký giao dịch của tôi (thường hoàn thành sau 1-2 giây) đã bị tranh cãi trong khoảng 30 giây:
15 phút sau và CO LẠI vừa kết thúc, trong khi Bản sao lưu nhật ký giao dịch vẫn đang được tranh cãi trong khoảng 6 phút và chỉ mới hoàn thành 50%. Tôi tin rằng họ ngay lập tức hoàn thành ngay sau đó kể từ khi CO LẠI đã kết thúc. Toàn bộ thời gian Trình giám sát tài nguyên cho thấy I/O vẫn hoạt động:
Sau đó, tôi gặp lỗi với CO LẠI lệnh khi nó kết thúc:
tôi đã thử lại CO LẠI một lần nữa và nó dẫn đến kết quả chính xác như trên.
Sau đó, tôi đã thử viết kịch bản sao lưu T-SQL theo cách thủ công vào một tệp trên ổ P: và nó chạy chậm giống như công việc sao lưu kế hoạch bảo trì:
Tôi đã hủy nó sau khoảng 3 phút và nó ngay lập tức quay trở lại.
Tóm lược:
Thật trùng hợp, công việc lập kế hoạch bảo trì sao lưu chậm hơn khoảng 40 lần (từ 15 phút đến 15 giờ) mỗi đêm, ngay sau khi cài đặt các bản cập nhật Windows. Việc khôi phục các bản cập nhật Windows đó không khắc phục được sự cố. Các kiểu chờ của SQL Server, Trình giám sát tài nguyên và Microsoft DiskSpd cho biết sự cố đĩa (cụ thể là I/O), nhưng tất cả các phép đo khác từ truy vấn của Paul Randall, Nhật ký PerfMon và VMWare đều không báo cáo bất kỳ sự cố nào với đĩa. Việc khôi phục các bản sao lưu cho một cơ sở dữ liệu cụ thể diễn ra nhanh chóng và sử dụng gần như toàn bộ I/O 1 GB/giây. Tôi đang gãi đầu...
