Sử dụng sửa kế hoạch tự động để điều chỉnh truy vấn SQL

1. Giới thiệu

Đôi khi, các hoạt động điều chỉnh truy vấn có thể rất khó khăn và phức tạp trong SQL Server. Với SQL Server 2016, một tính năng mới Query Store đã được giới thiệu và tính năng này nắm bắt và giữ lại kế hoạch thực thi các truy vấn đã thực thi và thống kê thời gian chạy. Bằng cách này, chúng tôi bắt đầu xác định các vấn đề về hiệu suất truy vấn dễ dàng hơn.

Trong bước tiếp theo, chúng ta đã gặp một tính năng mới trong SQL Server 2017 và tên của tính năng này là Sửa kế hoạch tự động. Tính năng này tự động xác định các vấn đề của kế hoạch truy vấn và đề xuất một số khuyến nghị để khắc phục những vấn đề này. Nó cũng có thể tự động áp dụng các đề xuất điều chỉnh truy vấn được đề xuất.

2. Yêu cầu trước

Như chúng tôi đã nêu, chúng tôi cần bật tính năng Cửa hàng truy vấn để kích hoạt Chỉnh sửa kế hoạch tự động. Truy vấn sau sẽ kích hoạt Cửa hàng truy vấn cho bất kỳ cơ sở dữ liệu cụ thể nào.

ALTER DATABASE DatabaseName
SET QUERY_STORE = ON
    (
        OPERATION_MODE = READ_WRITE  
    , CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 5 )
    , DATA_FLUSH_INTERVAL_SECONDS = 900
    , MAX_STORAGE_SIZE_MB = 10
    , INTERVAL_LENGTH_MINUTES = 1
    );

Thứ nhất, chúng tôi không bật tính năng tự động sửa kế hoạch vì lý do này, chúng tôi sẽ tắt tính năng này thông qua truy vấn sau.

ALTER DATABASE DatabaseName
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF );

3. Hồi quy kế hoạch truy vấn trong SQL Server là gì?

Mục tiêu của trình tối ưu hóa truy vấn SQL Server là tạo kế hoạch thực thi tối ưu cho các truy vấn. Thống kê, chỉ mục, công cụ ước tính số lượng và một số yếu tố khác có tác động đến các kế hoạch thực thi đã tạo.

SQL Server lưu trữ các kế hoạch thực thi đã tạo trong bộ đệm ẩn kế hoạch và sử dụng lại các kế hoạch đã lưu trong bộ đệm ẩn cho lần thực thi tiếp theo của các truy vấn tương tự. Bằng cách này, trình tối ưu hóa truy vấn ngăn chặn việc tiêu thụ thời gian và CPU dư thừa để tạo ra một kế hoạch thực thi mới khi cùng một truy vấn được thực thi.

Tuy nhiên, vì một số lý do (tạo hoặc giảm chỉ mục, cập nhật thống kê, v.v.), SQL Server có thể từ bỏ việc sử dụng kế hoạch truy vấn được lưu trong bộ nhớ cache và biên dịch kế hoạch truy vấn mới. Kế hoạch truy vấn mới được tạo cho thấy hiệu suất kém hơn so với kế hoạch trước đó, do đó nó ảnh hưởng tiêu cực đến hiệu suất truy vấn.

Triệu chứng chính của vấn đề này là truy vấn của bạn đột nhiên chậm lại trong cơ sở dữ liệu, mặc dù không có thay đổi đáng chú ý nào. Tình huống này được gọi là hồi quy lựa chọn kế hoạch truy vấn .

4. Hồi quy kế hoạch truy vấn xảy ra như thế nào?

Như chúng ta đã đề cập trong phần trước, SQL Server lưu trữ các kế hoạch truy vấn trong bộ đệm để khi cùng một truy vấn được thực thi cho các tham số khác nhau, nó sẽ sử dụng cùng một kế hoạch truy vấn. Tuy nhiên, đối với một số truy vấn, kế hoạch tối ưu có thể khác nhau tùy thuộc vào các tham số được sử dụng. Hãy minh họa tình huống này bằng một ví dụ.

Đầu tiên, chúng ta sẽ tạo một bảng mẫu như hình dưới đây.

CREATE TABLE TestPlanRegresion (
Val int,
definition_ nvarchar(200),
INDEX IX_001_ColumnStore nonclustered columnstore (Val),
INDEX IX_002_NCluster(Val)

Bảng này bao gồm hai loại chỉ mục, loại đầu tiên là chỉ mục cột lưu trữ không phân cụm được lưu trữ dữ liệu theo kiểu cột khôn ngoan và loại còn lại là chỉ mục loại B-Tree cổ điển. Bước thứ hai, chúng tôi sẽ điền vào bảng một số dữ liệu không đồng nhất.

INSERT INTO  TestPlanRegresion(Val, definition_)
VALUES (1, 'First Row')
GO
INSERT INTO TestPlanRegresion(Val, definition_)
SELECT TOP 1000000 2 AS Val, o.name
from sys.objects, sys.all_columns o

Sau khi điền dữ liệu, chúng tôi sẽ cập nhật các chỉ mục.

ALTER INDEX  IX_002_NCluster ON dbo.TestPlanRegresion REBUILD
GO
ALTER INDEX IX_001_ColumnStore ON dbo.TestPlanRegresion
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)
GO
CHECKPOINT

Bây giờ chúng ta kích hoạt kế hoạch thực thi thực tế và chạy truy vấn.

EXECUTE sp_executesql @stmt = N'SELECT COUNT(*) FROM TestPlanRegresion WHERE
    Val = @Val', @params = N'@Val int', @Val = 2

Trong kế hoạch truy vấn này, trình tối ưu hóa truy vấn SQL Server đã quyết định sử dụng chỉ mục columnstore để truy cập vào một triệu hàng và điều này rất hợp lý cho việc điều chỉnh truy vấn. Sau đó, hoạt động đếm hàng được thực hiện bởi toán tử tổng hợp khớp băm. Chúng tôi bắt gặp tổng hợp đối sánh băm khi công cụ lưu trữ đang xử lý lượng dữ liệu khổng lồ chưa được sắp xếp. Toán tử vô hướng tính toán chuyển đổi kết quả của toán tử tổng hợp luồng thành một số nguyên vì hàm COUNT trả về kiểu dữ liệu là một số nguyên. Hiểu đúng kế hoạch thực thi là rất quan trọng để điều chỉnh truy vấn.

Bây giờ, chúng ta sẽ thực hiện cùng một truy vấn 500 lần để kho lưu trữ truy vấn có thể nắm bắt được dữ liệu cần thiết.

DECLARE @Counter AS INT=1
WHILE @Counter <=500
BEGIN
EXECUTE sp_executesql @stmt = N'SELECT COUNT(*) FROM TestPlanRegresion WHERE Val = @Val', @params = N'@Val int', @Val = 2
SET @Counter = @Counter+1
END

Bây giờ, chúng tôi sẽ xóa kế hoạch của truy vấn trước đó khỏi bộ nhớ cache của kế hoạch. Để xóa một kế hoạch thực thi cụ thể khỏi bộ nhớ cache của kế hoạch, trước tiên cần phải xác định kế hoạch truy vấn được gạch dưới. Để tìm ra kế hoạch truy vấn, chúng ta có thể sử dụng truy vấn sau.

SELECT cp.plan_handle,
    cp.objtype,
    cp.usecounts,
    DB_NAME(st.dbid) AS [DatabaseName],
    st.text
FROM sys.dm_exec_cached_plans AS cp
  CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text LIKE '%SELECT COUNT(*) FROM TestPlanRegresion WHERE Val = @Val%';

Chúng tôi sẽ xóa kế hoạch truy vấn đã lưu trong bộ nhớ cache.

DBCC FREEPROCCACHE (0x06000D00CC71E53710F8CBC54001000001000000000000000000000000000000000000000000000000000000);

Hãy thực hiện cùng một truy vấn với một tham số khác và phân tích kế hoạch thực thi.

EXECUTE sp_executesql @stmt = N'SELECT COUNT(*) FROM TestPlanRegresion WHERE  Val = @Val', @params = N'@Val int', @Val = 1

Như chúng ta có thể thấy trong kế hoạch thực thi thực sự của truy vấn này, nó đã sử dụng toán tử tìm kiếm chỉ mục và nó đã thực hiện một vị từ tìm kiếm. Lý do là dữ liệu cột Val được lưu trữ trong cấu trúc chỉ mục không phân cụm vì định nghĩa chỉ mục IX_002_NCluster nên công cụ lưu trữ SQL Server có thể truy cập dữ liệu đã lọc trực tiếp thông qua sử dụng cấu trúc chỉ mục B-tree. Toán tử tổng hợp luồng thực hiện các hàm tổng hợp (SUM, COUNT, AGV, MIN và MAX) trong các truy vấn cho truy vấn này mà nó đã hoàn thành hoạt động đếm.

Khi chúng tôi thực hiện truy vấn với tham số đầu tiên, trình tối ưu hóa truy vấn quyết định sử dụng một kế hoạch thực thi khác và không hiệu quả cho truy vấn này.

EXECUTE sp_executesql @stmt = N'SELECT COUNT(*) FROM TestPlanRegresion WHERE  Val = @Val', @params = N'@Val int', @Val = 2

Chi tiết nhà điều hành được chọn có thể cung cấp một số chi tiết về tình huống này. Trong thuộc tính danh sách tham số của toán tử select, chúng ta có thể tìm ra một số chi tiết thú vị. Truy vấn đã thực thi được biên dịch cho các tham số khác nhau và trình tối ưu hóa truy vấn đã chọn kế hoạch truy vấn này.

Vấn đề này được gọi là hồi quy lựa chọn kế hoạch hoặc hồi quy kế hoạch.

5. Đề xuất sửa kế hoạch tự động của SQL Server

Tính năng Sửa kế hoạch Tự động theo dõi các vấn đề hồi quy kế hoạch truy vấn xảy ra trong cơ sở dữ liệu. Khi phát hiện một truy vấn hồi quy, nó sẽ tự động buộc truy vấn sử dụng phương án hiệu quả thay vì sử dụng phương án kém hơn. Bây giờ chúng tôi thực hiện một cuộc trình diễn về tính năng này.

1. Chúng tôi kích hoạt tính năng Chỉnh sửa kế hoạch tự động và xóa dữ liệu Cửa hàng truy vấn

ALTER DATABASE DatabaseName SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
            ALTER DATABASE DatabaseName SET QUERY_STORE CLEAR;

     2.  Chúng tôi xóa tất cả bộ đệm ẩn của kế hoạch truy vấn được trình tối ưu hóa truy vấn biên dịch và lưu trữ. Lệnh này có thể ảnh hưởng đến hiệu suất cơ sở dữ liệu của bạn, vì lý do này, không sử dụng nó trong cơ sở dữ liệu sản xuất

DBCC FREEPROCCACHE

Mẹo: Trong khi viết bài này, tôi đã gặp sự cố với bộ đệm ẩn kế hoạch truy vấn của mình. SQL Server đặt lại bộ đệm ẩn kế hoạch truy vấn trong một thời gian ngắn mà không có bất kỳ lý do hợp lý nào. Nói chung, sự cố này liên quan đến thuộc tính Bật khóa trang trong bộ nhớ (LPIM). Trong SQL Server 2019, chúng ta có thể tìm ra thuộc tính này trong Trình quản lý cấu hình SQL Server. Trong tab nâng cao của SQL Server 2019, chúng tôi có thể bật thuộc tính Khóa trang trong bộ nhớ như được hiển thị bên dưới:

3. Chúng tôi thực hiện truy vấn sau 500 lần để Cửa hàng truy vấn có thể nắm bắt số liệu thống kê của một truy vấn

DECLARE @Counter AS INT=1
    WHILE @Counter <=500
    BEGIN
    EXECUTE sp_executesql @stmt = N'SELECT COUNT(*) FROM TestPlanRegresion WHERE  Val = @Val', @params = N'@Val int', @Val = 2
    SET @Counter =@Counter+1
    END

4. Chúng tôi xóa bộ nhớ cache của kế hoạch và thực hiện cùng một truy vấn với một tham số khác và sau đó chúng tôi thực thi nó với cùng một tham số 500 lần. Vì vậy, truy vấn bắt đầu sử dụng kế hoạch truy vấn phụ tối ưu

DBCC FREEPROCCACHE
    GO
    EXECUTE sp_executesql @stmt = N'SELECT COUNT(*) FROM TestPlanRegresion WHERE  Val = @Val', @params = N'@Val int', @Val = 1
    DECLARE @Counter AS INT=1
    WHILE @Counter <=500
    BEGIN
    EXECUTE sp_executesql @stmt = N'SELECT COUNT(*) FROM TestPlanRegresion WHERE  Val = @Val', @params = N'@Val int', @Val = 2
    SET @Counter =@Counter+1
    END

Chế độ xem quản lý động sys.dm_db_tuning_recommendations trả về các vấn đề điều chỉnh truy vấn đã xác định và trạng thái của các đề xuất này.

SELECT
	reason,
	score,
	JSON_VALUE(state, '$.currentValue') state,
	JSON_VALUE(state, '$.reason') state_transition_reason,
    script = JSON_VALUE(details, '$.implementationDetails.script'),
	[current plan_id],
	[recommended plan_id],
	is_revertable_action,type
    FROM sys.dm_db_tuning_recommendations
	CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            [current plan_id] int '$.regressedPlanId',
            [recommended plan_id] int '$.recommendedPlanId',
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) as planForceDetails;

Đối với phần trình diễn của chúng tôi, SQL Server đã buộc phải sử dụng một kế hoạch truy vấn bao gồm một chỉ mục cột lưu trữ và công cụ dữ liệu đã bắt đầu sử dụng nó thay vì kế hoạch phụ tối ưu. Trong cột lý do , tại sao vấn đề điều chỉnh truy vấn này được áp dụng cho truy vấn này. Trạng thái Xác minh xác định rằng đề xuất này đã được áp dụng tự động bởi tính năng tự động sửa kế hoạch và nó vẫn chờ quá trình xác minh để so sánh hiệu suất của kế hoạch bắt buộc với kế hoạch hồi quy. Tình huống này có thể báo cáo bằng cách sử dụng Truy vấn có kế hoạch cưỡng bức.

6. Kết luận

Trong bài viết này, chúng tôi đã khám phá tính năng Chỉnh sửa kế hoạch tự động và chúng tôi đã tìm hiểu cách nó có thể giúp chúng tôi trong các hoạt động điều chỉnh truy vấn. Tính năng này có thể rất hữu ích để khắc phục các vấn đề hồi quy kế hoạch và có thể giúp giải quyết các vấn đề đánh hơi tham số. Mặt khác, chúng tôi có thể tắt tính năng này và áp dụng các đề xuất điều chỉnh theo cách thủ công.

Chúng tôi chuyên cung cấp những khoá học về Phân tích dữ liệu, đăng ký ngay để nhận được tư vấn chi tiết lộ trình dành riêng cho bạn nhé!