Memory grant, tràn tempdb và song song

Sort và Hash Match trong các báo cáo của BanHang xin bộ nhớ trước khi chạy, theo số dòng mà trình tối ưu ước lượng. Xin thiếu thì tràn xuống tempdb và chậm đi, xin thừa thì các câu khác phải xếp hàng chờ bộ nhớ. Đọc xong bạn hiểu MemoryGrantInfo và các cảnh báo trên kế hoạch, và viết được một test C# bắt spill trước khi lên production.

1. Toán tử nào cần bộ nhớ #

Sort, Hash Match và một số exchange cần bộ nhớ riêng ngoài buffer pool. Engine tính lượng xin trước khi chạy, theo số dòng ước lượng và độ rộng dòng, rồi giữ phần được cấp đến hết câu. Seek, scan và Stream Aggregate trả dòng ngay khi có và không cần grant.

• Sort là blocking và cần grant theo số dòng nhân độ rộng dòng. Top N Sort phục vụ TOP (n) ... ORDER BY và chỉ giữ n dòng tốt nhất, nên với n nhỏ nó cần ít bộ nhớ hơn Sort đầy đủ.
• Hash Match (join) dựng bảng băm từ phía build. Build vượt grant thì tràn xuống tempdb.
• Hash Match (Aggregate) giữ mỗi nhóm một mục, nên grant tính theo số nhóm ước lượng. Ước lượng 60.000 nhóm mà thực tế 200.000 nhóm thì tràn.
• Adaptive Join xin bộ nhớ như hash join kể cả khi lúc chạy chuyển sang nested loops.

Output List rộng ở phía dưới một Sort hay Hash Match làm grant lớn theo. Một cột nvarchar(200) lấy thừa mà đi qua Sort hay Hash Match là thêm bộ nhớ ở mọi lần chạy.

flowchart TB
  E["Biên dịch: ước lượng số dòng và độ rộng dòng"] --> R["Lúc chạy: xin RequestedMemory"]
  R --> W{"Còn chỗ cho grant?"}
  W -- "không" --> Q["Chờ RESOURCE_SEMAPHORE"]
  Q --> G
  W -- "có" --> G["Được cấp GrantedMemory, giữ đến hết câu"]
  G --> U{"Dữ liệu thực tế vừa grant?"}
  U -- "vừa" --> OK["Chạy trong bộ nhớ"]
  U -- "không vừa" --> S["Tràn xuống tempdb, cảnh báo spill"]

2. Đọc MemoryGrantInfo #

Nút gốc SELECT của kế hoạch có MemoryGrantInfo. Đây là kế hoạch sau khi sửa của báo cáo doanh thu tháng ở Đọc kế hoạch thực thi: tìm chỗ ước lượng lệch; các số tính bằng KB, là số minh họa:

<MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="34816"
                 RequiredMemory="6144" DesiredMemory="38912"
                 RequestedMemory="38912" GrantWaitTime="0"
                 GrantedMemory="38912" MaxUsedMemory="27648"
                 MaxQueryMemory="11010048" />

Đọc ba cặp:

• GrantedMemory so với MaxUsedMemory. Câu chạy thường xuyên mà dùng dưới một nửa là xin thừa: bộ nhớ đó không cho câu khác dùng, và khi tổng grant chạm trần, câu sau phải chờ với wait RESOURCE_SEMAPHORE. Ở đây dùng 27.648 trên 38.912 KB, khoảng 71%.
• GrantWaitTime khác 0: câu đã phải chờ bộ nhớ trước khi chạy.
• Grant thiếu: Sort hoặc Hash Match tràn xuống tempdb và có cảnh báo spill ở toán tử đó.

3. Khi grant thiếu: tràn tempdb #

Phía build của Hash Match trong báo cáo doanh thu tháng là 373.100 dòng DonHang, nên grant tính theo con số đó. Nếu ước lượng phía build thấp hơn thực tế nhiều lần, grant thiếu và kế hoạch thực tế có cảnh báo dạng "Operator used tempdb to spill data during execution with spill level 1 and 4 spilled thread(s)", kèm số page ghi và đọc lại từ tempdb.

Spill thường là hậu quả của một ước lượng thấp ở phía dưới toán tử. Cách sửa vì vậy là tìm chỗ ước lượng lệch đầu tiên, không phải tăng bộ nhớ cho server. Câu trong cache có tràn hay không xem ở cột last_spills của sys.dm_exec_query_stats, có từ SQL Server 2016 SP2.

Một trường hợp hay gặp là parameter sniffing: kế hoạch biên dịch cho một giá trị hiếm được dùng lại cho giá trị phổ biến. Thủ tục dbo.usp_KhachHang_TheoTrangThai ở Parameter sniffing và Query Store xin 6.144 KB cho 100.000 dòng rồi phải xử lý 9.000.000 dòng, và Hash Aggregate tràn. Mục 7 dưới đây đo lại đúng cơ chế đó trên LocalDB.

4. Memory grant feedback #

Từ SQL Server 2019 (compatibility level 150, Enterprise), row mode memory grant feedback thêm LastRequestedMemory và IsMemoryGrantFeedbackAdjusted vào kế hoạch. Giá trị của thuộc tính sau cho biết engine đã chỉnh grant theo các lần chạy trước hay chưa:

Câu có nhu cầu bộ nhớ dao động mạnh giữa các lần chạy, như thủ tục bị gọi xen kẽ với giá trị hiếm và giá trị phổ biến, sẽ thấy No: Feedback Disabled. Feedback không áp dụng cho câu có OPTION (RECOMPILE), vì kế hoạch không ở lại cache.

5. Cảnh báo trên kế hoạch #

Cảnh báo hiện thành dấu chấm than vàng trên toán tử hoặc trên nút gốc. Có loại sinh lúc biên dịch, nên nằm cả trong kế hoạch của cache. Có loại chỉ sinh lúc chạy, nên chỉ có trong kế hoạch thực tế.

Với PlanAffectingConvert, collation của BanHang là collation Windows (Vietnamese_100_CI_AS). So cột varchar với hằng hoặc tham số nvarchar, ví dụ WHERE MaSanPham = N'SP-00042', làm cột bị đổi sang nvarchar. Engine vẫn seek được nhờ tính khoảng qua hàm GetRangeThroughConvert, nhưng ước lượng kém đi và kế hoạch có cảnh báo Cardinality Estimate. Với collation SQL kiểu SQL_Latin1_General_CP1_CI_AS, cùng câu đó thành scan.

Gợi ý chỉ mục (green text "Missing Index", phần tử MissingIndexes) không phải cảnh báo. Nó là gợi ý cho đúng một câu, không xét chỉ mục đã có và không xét chi phí ghi. Đối chiếu với Chỉ mục lọc, cái giá khi ghi và chỉ mục thừa trước khi tạo.

Truy vấn dưới đây liệt kê câu trong cache có cảnh báo lúc biên dịch, xếp theo logical reads. Nó đọc XML của mọi kế hoạch đang lưu, nên chạy ngoài giờ cao điểm.

WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (50)
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS reads_moi_lan,
    qs.last_spills,
    qp.query_plan.exist(N'//Warnings/PlanAffectingConvert') AS co_convert_implicit,
    qp.query_plan.exist(N'//Warnings/@NoJoinPredicate') AS thieu_dieu_kien_noi,
    qp.query_plan.exist(N'//Warnings/@UnmatchedIndexes') AS chi_muc_loc_khong_khop,
    qp.query_plan.exist(N'//MissingIndexes') AS co_goi_y_chi_muc,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.dbid = DB_ID(N'BanHang')
ORDER BY qs.total_logical_reads DESC;

6. Song song #

Nút gốc có Degree of Parallelism. Kế hoạch không song song có thể kèm NonParallelPlanReason giải thích vì sao. Máy minh họa đặt MAXDOP 4, nên kế hoạch song song chạy tối đa 4 thread cho mỗi vùng song song.

Toán tử Parallelism có ba dạng. Distribute Streams chia một luồng thành nhiều. Repartition Streams chia lại dòng giữa các thread theo hash hoặc round robin. Gather Streams gom về một luồng và có thể giữ thứ tự.

Trong kế hoạch song song, bấm vào toán tử rồi xem số dòng theo từng thread. Một thread nhận 90% số dòng là lệch tải: câu chạy chậm như kế hoạch một luồng mà vẫn tốn chi phí điều phối. Thread 0 là thread điều phối và thường có 0 dòng ở vùng song song. Wait CXPACKET, CXCONSUMER đi kèm mọi kế hoạch song song và tự nó không phải lỗi; lệch tải giữa các thread mới là thứ cần tìm.

7. Áp dụng trong .NET: bắt spill trong test tích hợp #

Spill chỉ hiện trong kế hoạch thực tế, nên không thấy được bằng cách đọc cache hay xem kế hoạch ước lượng. Từ code C# lấy được kế hoạch thực tế: khi kết nối bật SET STATISTICS XML ON, sau mỗi result set SQL Server trả thêm một result set một cột chứa XML kế hoạch thực tế của câu vừa chạy. Test tích hợp đọc XML đó và đánh giá grant.

await new SqlCommand("SET STATISTICS XML ON;", conn).ExecuteNonQueryAsync();
XDocument? plan = null;
await using (var r = await cmd.ExecuteReaderAsync())
{
    do
    {
        var laKeHoach = r.FieldCount == 1 && r.GetName(0).Contains("Showplan");
        while (await r.ReadAsync())
            if (laKeHoach) plan = XDocument.Parse(r.GetString(0));
    } while (await r.NextResultAsync());
}
await new SqlCommand("SET STATISTICS XML OFF;", conn).ExecuteNonQueryAsync();

// GrantedMemory, MaxUsedMemory tính bằng KB
var grant = plan!.Descendants(Ns + "MemoryGrantInfo").First();
var ghiTempdb = plan.Descendants(Ns + "HashSpillDetails")
    .Concat(plan.Descendants(Ns + "SortSpillDetails"))
    .Sum(e => (long)e.Attribute("WritesToTempDb")!);
// Test: ghiTempdb == 0, và MaxUsedMemory không dưới một nửa GrantedMemory

Ns là namespace http://schemas.microsoft.com/sqlserver/2004/07/showplan. Chương trình chạy thủ tục dbo.usp_KhachHang_TheoTrangThai theo hai thứ tự: biên dịch cho trạng thái 1 (1% đơn) rồi chạy với 4 (90% đơn), và ngược lại. Kết quả bằng dotnet run KeHoachThucTe.cs trên .NET 10.0.12, LocalDB SQL Server 2019 (Express, kế hoạch một luồng), database thử Kumeo_kehoach dựng theo Kế hoạch thực thi và plan cache:

Lần chạy với 4 bằng kế hoạch của 1 dùng 1.352 trên 1.432 KB, ghi 832 page xuống tempdb và chậm hơn hẳn lần chạy với kế hoạch biên dịch cho 4. Hai lần dùng kế hoạch của 4 không tràn nhưng dùng dưới một nửa grant, nên test cũng đánh dấu. Số tuyệt đối nhỏ vì database thử chỉ có 1/10 số đơn; trên máy minh họa của bài parameter sniffing cùng cơ chế cho 6.144 KB và 24.576 KB. LocalDB là bản Express: không có memory grant feedback, kế hoạch chạy một luồng. Cột ms dao động giữa các lần chạy; grant và số page tràn thì lặp lại y hệt.

#:package Microsoft.Data.SqlClient@7.1.1

using System.Data;
using System.Xml.Linq;
using Microsoft.Data.SqlClient;

const string Cs = @"Server=(localdb)\MSSQLLocalDB;Database=Kumeo_kehoach;Integrated Security=true;TrustServerCertificate=true";
await using var conn = new SqlConnection(Cs);
await conn.OpenAsync();
await new SqlCommand("""
    CREATE OR ALTER PROCEDURE dbo.usp_KhachHang_TheoTrangThai @TrangThai tinyint AS
    SET NOCOUNT ON;
    SELECT TOP (20) KhachHangId, COUNT_BIG(*) AS SoDon, SUM(TongTien) AS TongGiaTri
    FROM dbo.DonHang WHERE TrangThai = @TrangThai
    GROUP BY KhachHangId ORDER BY SUM(TongTien) DESC;
    """, conn).ExecuteNonQueryAsync();
Console.WriteLine($".NET {Environment.Version}, SQL Server {conn.ServerVersion} (LocalDB)");
Console.WriteLine($"{"Chạy",-5} {"Biên dịch",-10} {"Granted KB",11} {"MaxUsed KB",11} {"Ghi tempdb",11} {"ms",6}  Đánh giá");

foreach (var (bienDich, chay) in new[] { (1, 4), (4, 1) })
{
    await new SqlCommand("EXEC sys.sp_recompile N'dbo.usp_KhachHang_TheoTrangThai';", conn).ExecuteNonQueryAsync();
    foreach (var trangThai in new[] { bienDich, chay })
    {
        var cmd = new SqlCommand("dbo.usp_KhachHang_TheoTrangThai", conn) { CommandType = CommandType.StoredProcedure };
        cmd.Parameters.Add("@TrangThai", SqlDbType.TinyInt).Value = (byte)trangThai;
        var kh = await KeHoachThucTe.ChayAsync(conn, cmd);
        Console.WriteLine($"{kh.GiaTriChay,-5} {kh.GiaTriBienDich,-10} {kh.GrantedKb,11} {kh.MaxUsedKb,11} {kh.TrangGhiTempdb,11} {kh.ElapsedMs,6}  {kh.DanhGia()}");
    }
}

// Chạy một lệnh với SET STATISTICS XML ON, đọc kế hoạch thực tế ở result set cuối.
sealed record KeHoachThucTe(string GiaTriBienDich, string GiaTriChay, long GrantedKb, long MaxUsedKb,
                            long TrangGhiTempdb, long ElapsedMs)
{
    static readonly XNamespace Ns = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";

    public string DanhGia() =>
        TrangGhiTempdb > 0 ? "TRÀN tempdb"
        : GrantedKb > 0 && MaxUsedKb * 2 < GrantedKb ? "xin thừa (dùng dưới một nửa)"
        : "ổn";

    public static async Task<KeHoachThucTe> ChayAsync(SqlConnection conn, SqlCommand cmd)
    {
        await new SqlCommand("SET STATISTICS XML ON;", conn).ExecuteNonQueryAsync();
        XDocument? plan = null;
        await using (var r = await cmd.ExecuteReaderAsync())
        {
            do
            {
                var laKeHoach = r.FieldCount == 1 && r.GetName(0).Contains("Showplan");
                while (await r.ReadAsync())
                    if (laKeHoach) plan = XDocument.Parse(r.GetString(0));
            } while (await r.NextResultAsync());
        }
        await new SqlCommand("SET STATISTICS XML OFF;", conn).ExecuteNonQueryAsync();

        var grant = plan!.Descendants(Ns + "MemoryGrantInfo").FirstOrDefault();
        var thamSo = plan.Descendants(Ns + "ParameterList").Elements(Ns + "ColumnReference").FirstOrDefault();
        long So(XElement? e, string ten) => long.TryParse(e?.Attribute(ten)?.Value, out var v) ? v : 0;
        return new KeHoachThucTe(
            thamSo?.Attribute("ParameterCompiledValue")?.Value ?? "",
            thamSo?.Attribute("ParameterRuntimeValue")?.Value ?? "",
            So(grant, "GrantedMemory"),
            So(grant, "MaxUsedMemory"),
            plan.Descendants(Ns + "HashSpillDetails").Concat(plan.Descendants(Ns + "SortSpillDetails"))
                .Sum(e => So(e, "WritesToTempDb")),
            So(plan.Descendants(Ns + "QueryTimeStats").FirstOrDefault(), "ElapsedTime"));
    }
}

Những chỗ hay hiểu sai #

• "Grant lớn hơn thì an toàn hơn." Grant được giữ đến hết câu. Tổng grant chạm trần thì câu sau chờ RESOURCE_SEMAPHORE, dù bộ nhớ đã cấp đang bỏ trống.
• "Kế hoạch trong cache không có cảnh báo spill, vậy câu không tràn." Spill là cảnh báo lúc chạy, chỉ có trong kế hoạch thực tế. Xem last_spills hoặc lấy kế hoạch thực tế.
• "Spill thì tăng max server memory." Spill thường là hậu quả của ước lượng thấp. Sửa chỗ ước lượng lệch, grant đúng theo.
• "Gợi ý Missing Index là chỉ mục nên tạo." Đó là gợi ý cho một câu, không xét chỉ mục đã có và chi phí ghi.
• "Wait CXPACKET cao là lỗi." Nó đi kèm mọi kế hoạch song song. Thứ cần tìm là lệch tải giữa các thread.

Kết luận #

Memory grant là lời hứa bộ nhớ dựa trên ước lượng: ước lượng thấp thì tràn, ước lượng cao thì giữ thừa. Cả hai đọc được trong MemoryGrantInfo và cảnh báo của kế hoạch thực tế, và kiểm được tự động từ code.

Trong dự án .NET của bạn:

• Viết test tích hợp cho báo cáo nặng: bật SET STATISTICS XML ON, đọc result set kế hoạch, parse bằng XDocument, fail khi có HashSpillDetails hoặc SortSpillDetails.
• Chạy test đó với nhiều giá trị tham số, gồm giá trị hiếm và giá trị phổ biến nhất, theo cả hai thứ tự.
• Trên production, theo dõi last_spills, last_grant_kb, last_used_grant_kb trong sys.dm_exec_query_stats cho các câu gắn TagWith.
• Bỏ khỏi projection các cột rộng mà báo cáo không dùng, nhất là cột đi qua Sort hay Hash Match.

Đọc tiếp #

• Bài trước: Toán tử và Key Lookup.
• Bài sau: Parameter sniffing và Query Store, vì sao cùng một thủ tục lúc xin thiếu lúc xin thừa.
• Vì sao không tạo theo gợi ý Missing Index: Chỉ mục lọc, cái giá khi ghi và chỉ mục thừa.
• Thống kê, nguồn của số dòng ước lượng: Thống kê, histogram và ngưỡng tự cập nhật.
• Vì sao tham số nvarchar so với cột varchar sinh CONVERT_IMPLICIT: Ngày giờ, múi giờ và kiểu tham số.

Nguồn #

• Memory grant feedback
• Intelligent query processing details
• sys.dm_exec_query_stats
• SET STATISTICS XML
• Showplan XML schema, SQL Server 2019
• Editions and supported features of SQL Server 2019