SQL Server: Query plan forcing & Automatic tuning feature

Tính năng Auto-tuning được khuyên dùng, do đó nếu không cần quan tâm nhiều lý thuyết, hãy đến phần 2 (Automatic Tuning) để lấy script cấu hình cho SQLServer.

Plan forcing

Why need?

Từ SQLServer 2016 trở đi, chúng ta có thể sử dụng QueryStore để lưu trữ lại thông tin về lịch sử execute của các query: CPU time, wait time, lock, query plan… tương ứng. Và như khuyến nghị, đây là option đáng giá và must-configure ở SQLServer.

Với mỗi DB, trên một query có thể được SQLServer sử dụng nhiều query plan khác nhau khi execute. Điều này là do ở mỗi trường hợp, có thể đầu vào (params) khác nhau, mà SQLServer dựa vào statistics sẽ đưa ra các plan khác nhau. Có trường hợp plan được đưa ra là tốt, cũng có trường hợp ngược lại, plan đưa ra khiến query đột ngột chậm hẳn (có thể do nhiều nguyên nhân, ví dụ outdated statistics).

Một trong những nhiệm vụ của DBA là dựa trên log của các lần chậm này, đánh giá plan và xử lý các nguyên nhân khiến SQL Server nhầm lẫn, tạo ra plan này.

Và force SQLServer sử dụng plan tốt nhất cũng là một phương pháp đáng lưu ý.

How to force?

Để force 1 query sử dụng 1 plan, rất đơn giản:

EXEC sys.sp_query_store_force_plan @query_id 550, @plan_id 339;

Ta thấy, chỉ cần cung cấp query_id và plan_id để thực hiện. Sau khi cấu hình, mỗi khi query tương ứng query_id được biên dịch, optimizer của engine sẽ sử dụng plan tương ứng plan_id.

Vậy ta lấy đâu ra 2 tham số ở trên (query_id & plan_id)?

Thông thường quá trình tối ưu DB luôn bao gồm liệt kê các query chậm từ QueryStore. Tham khảo cách thức liệt kê ở bài viết này.

Như ảnh trên, ta có thể đánh giá nhanh rằng:

• Cùng 1 query 106408 nhưng có 2 plan 15404 và 26360.
• plan 15404 có vẻ tối ưu hơn, và có thể thực hiện force SQLServer sử dụng plan này.

Hoặc có thể sử dụng công cụ Tracked Queries của SSMS để xem chi tiết dựa trên đầu vào là query_id, như hình dưới đây

• Với Tracked Queries ta còn có thể xem chi tiết plan, từ đó có thêm thông tin để quyết định force.

Một số vấn đề khi áp dụng Plan forcing

Rõ ràng Plan forcing là một công cụ tối ưu rất hiệu quả, có tính thực tiễn cao, có thể hotfix trong các trường hợp khẩn cấp. Tuy nhiên ta có thể chỉ ra một số hạn chế như sau:

• Việc áp dụng như trên - EXEC sys.sp_query_store_force_plan ... - rất thủ công;
• Thời điểm track lại có thể các plan đã được clear khỏi QueryStore, do giới hạn về dung lượng lưu trữ, cấu hình rotate của QueryStore;
• Đôi khi cần nhiều plan hơn hoặc các plan phải được sử dụng nhiều lần hơn mới có thể đưa ra quyết định.

Và một tính năng (amazing) của SQLServer có thể giải quyết các vấn đề trên: Automatic tuning

Automatic Tuning

Why need?

Đây là một tính năng của SQLServer, dựa vào lịch sử query từ QueryStore để xem xét, đánh giá, đưa ra các chiến thuật tuning, verify lại các chiến thuật liên tục. Từ đó có thể tự động áp dụng các chiến thuật này để tối ưu các query.

Các tính năng chính của Automatic Tuning:

• Tự động force plan tốt nhất cho mỗi query;
• Tự động tạo các index missing cần thiết hay xóa các index dư thừa.

Automatic plan forcing

Tính năng liên quan đến create/drop index là một công việc phức tạp và nằm ngoài phạm vi bài viết này. Ở đây ta chỉ xét đến tính năng Automatic plan forcing.

SQLServer sẽ tự động force sử dụng 1 plan tối ưu nhất cho 1 query, như cách làm thủ công đã giới thiệu ở phần trên.

SQLServer cũng cung cấp 1 công cụ để gợi ý và diễn giải về các chiến thuật tuning:

SELECT ddtr.TYPE,
       ddtr.reason,
       ddtr.STATE,
       ddtr.score,
       ddtr.details
FROM sys.dm_db_tuning_recommendations AS ddtr
ORDER BY ddtr.score DESC;

• type: Chỉ ra đây là force plan hay index creation…
• reason: Chỉ rõ vì đang dùng sai plan nên CPU Time mất nhiều hơn. Cụ thể như ở ví dụ trên, nếu áp dụng sẽ giảm CPU Time từ 9379.98ms xuống 49.2ms (Thực sự ấn tượng!!)
• score: Điểm đánh giá, càng cao chứng tỏ áp dụng càng mang lại hiệu quả lớn;
• details: Các thông tin đến plan đang sử dụng, plan gợi ý force như: query_id, plan_id, execution_count, avg_cpu_time.

Áp dụng

Một số điểm lưu ý khi áp dụng Automatic Tuning:

• Mở rộng QueryStore để có thể lưu trữ nhiều thông tin hơn;
• Chỉ áp dụng Automatic cho Force Plan;
• Nên chạy query dưới đây ở thời điểm ít người dùng. Mặc dù quá trình execute rất nhanh, nhưng vì các thao tác như clear buffer, clear query store có thể ảnh hưởng đến các query đang chạy ở hệ thống Prod.

USE easyposbackoffice
GO

-- Nâng cấp Query Store về khả năng lưu trữ, tần suất tổng hợp statistics, tần suất ghi dữ liệu xuống disk
ALTER DATABASE CURRENT SET QUERY_STORE 
(
  OPERATION_MODE = READ_WRITE,
  DATA_FLUSH_INTERVAL_SECONDS = 600,
  MAX_STORAGE_SIZE_MB = 500,
  INTERVAL_LENGTH_MINUTES = 30
  );
GO

-- Clear buffer cache
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

-- Clear query store
ALTER DATABASE CURRENT SET QUERY_STORE CLEAR ALL;

-- Bật auto tuning, nhưng chỉ cho phép tự động force Plan tốt nhất cho mỗi query
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON ); 
GO

-- Verify đảm bảo desired_state = 1 (ON)
SELECT * FROM sys.database_automatic_tuning_options 

Tham khảo

https://learn.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver16

https://www.sqlshack.com/understanding-automatic-tuning-in-sql-server-2017/

Book: SQL Server 2022 Query Performance Tuning