Page, dòng và extent

Ứng dụng đọc một đơn hàng, nhưng SQL Server luôn đọc cả page 8 KB chứa nó. Bảng hợp đồng có cột bản scan PDF vì vậy có thể biến một câu SELECT * theo khóa thành hơn một nghìn lần đọc page. Đọc xong, bạn tính được một page chứa bao nhiêu dòng, biết khi nào dữ liệu bị đẩy khỏi dòng, và viết endpoint .NET không kéo theo cột lớn.

1. Cấu trúc một page #

Bài trước, Kiến trúc lưu trữ: tệp và filegroup, dừng ở tệp dữ liệu. Bên trong tệp, mọi page dữ liệu dài 8.192 byte. Phần đầu là header 96 byte: số page, loại page, và metadata như object id, index id của đối tượng sở hữu page. Cuối page là slot array. Mỗi slot chiếm 2 byte và chứa độ lệch byte của một dòng tính từ đầu page.

Dòng được ghi từ sau header đi xuống. Slot array mọc từ cuối page đi lên. Sau nhiều lần sửa và xóa, dòng trên page có thể không nằm đúng thứ tự vật lý. Thứ tự logic do slot array giữ, nên khi đọc theo khóa của index, engine đi theo slot chứ không theo vị trí byte thô.

1 MB chứa 128 page. 1 GB chứa 131.072 page.

2. Một dòng đơn hàng chiếm bao nhiêu byte #

Lấy dòng DonHangId = 10042 của bảng dbo.DonHang, mọi cột độ dài cố định:

Công thức ước lượng của Microsoft cho heap hoặc clustered index chỉ có cột cố định:

Null bitmap = 2 + ((số cột + 7) / 8), chia nguyên    = 2 + 1 = 3 byte
Cỡ dòng     = dữ liệu cố định + null bitmap + 4       = 28 + 3 + 4 = 35 byte
Trên page   = cỡ dòng + 2 byte slot                   = 37 byte
Dòng / page = (8.192 − 96) / 37 = 8.096 / 37          = 218 dòng, dư 30 byte

Phần dư 8.096 − (218 × 37) = 30 byte không đủ cho dòng thứ 219. Page chứa đơn 10042, ở dạng đã đầy, trông như sau:

Một triệu đơn cùng hình dạng là khoảng 1.000.000 / 218 ≈ 4.588 page, tức khoảng 36 MB cho riêng clustered index, chưa tính IX_DonHang_KhachHang và page hệ thống. Đây là ước lượng theo công thức, để hình dung bậc kích thước trước khi đo bằng sys.dm_db_index_physical_stats.

Dòng thực tế có cột biến độ dài sẽ lớn hơn. GhiChu nvarchar(100) với 20 ký tự thêm khoảng 40 byte dữ liệu, cộng 2 byte đếm số cột biến và 2 byte offset, tức thêm khoảng 44 byte. Một page toàn những dòng như vậy chứa khoảng 99 dòng thay vì 218.

3. Dòng không vừa một page #

Phần dữ liệu và overhead nằm trong một dòng trên một page tối đa là 8.060 byte, không phải cả 8.192 byte. Phần còn lại thuộc header và slot array. Có ba cách dữ liệu nằm so với page gốc:

Mỗi cột varchar, nvarchar, varbinary thường vẫn tối đa 8.000 byte. Chỉ tổng nhiều cột mới được phép đẩy row-overflow. Cột độ dài cố định (char, nchar, int, …) cộng lại vẫn phải nằm trong 8.060 byte. Bảng dùng sparse column có giới hạn dòng 8.018 byte.

Row-overflow dịch chuyển khi UPDATE làm dòng dài ra hoặc ngắn lại, và truy vấn sắp xếp hoặc nối trên những dòng này phát sinh thêm I/O. Nếu phần lớn dòng thường xuyên tràn, tách cột sang bảng khác rẻ hơn để engine chuyển đi chuyển lại. Khóa clustered không được chứa giá trị nằm ở ROW_OVERFLOW_DATA: INSERT hoặc UPDATE đẩy khóa ra khỏi dòng sẽ thất bại.

Hợp đồng dài và file PDF #

Bảng hợp đồng có hai cột chữ dài và một bản scan:

CREATE TABLE dbo.HopDong (
    HopDongId int NOT NULL,
    TomTat varchar(5000) NOT NULL,
    DieuKhoan varchar(5000) NOT NULL,
    BanScan varbinary(max) NULL,
    CONSTRAINT PK_HopDong PRIMARY KEY CLUSTERED (HopDongId)
) ON FG_DATA;

Một hợp đồng điền đủ 5.000 byte cho cả TomTat và DieuKhoan, tức 10.000 byte chữ, vượt 8.060. Lúc INSERT hoặc UPDATE làm dòng vượt ngưỡng, engine đẩy cột biến rộng nhất sang ROW_OVERFLOW_DATA và để lại con trỏ 24 byte. Hai cột bằng nhau thì engine chọn một cột để đẩy. Dòng gốc còn một cột 5.000 byte cộng con trỏ, dưới 8.060. Cột còn trong dòng đọc từ page gốc; cột đã bị đẩy cần thêm ít nhất một page overflow.

BanScan là PDF 2 MB, kiểu varbinary(max). Dòng gốc giữ con trỏ LOB 16 byte, còn 2 MB nằm ở LOB_DATA: khoảng 2.048 KB / 8 KB = 256 page, cộng các page trung gian của cây LOB. SELECT HopDongId, TomTat FROM dbo.HopDong WHERE HopDongId = 15 không đọc 256 page đó. SELECT BanScan thì có. Vì vậy cột file nhị phân lớn nên để riêng khỏi các câu liệt kê hợp đồng: SELECT * biến một lần tìm theo khóa thành hàng trăm lần đọc page. Mục 6 đo đúng điều này trên LocalDB.

Kiểm tra bảng nào đang có trang tràn:

SELECT
    OBJECT_SCHEMA_NAME(object_id) AS schema_name,
    OBJECT_NAME(object_id) AS table_name,
    index_id,
    partition_number,
    alloc_unit_type_desc,
    page_count,
    record_count
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'SAMPLED')
WHERE alloc_unit_type_desc IN ('ROW_OVERFLOW_DATA', 'LOB_DATA')
  AND page_count > 0;

Từ SQL Server 2019, sys.dm_db_page_info đọc header của một page khi biết số file và số page. Đây là hàm được hỗ trợ. DBCC PAGE vẫn gặp trong bài cũ nhưng không được bảo đảm tương thích.

4. Extent #

Một extent là 8 page vật lý liên tiếp, 64 KB. Mọi page thuộc đúng một extent.

Từ SQL Server 2016, database người dùng và tempdb cấp uniform extent, trừ 8 page đầu của một chuỗi IAM. master, msdb, và model vẫn dùng hành vi cũ: đối tượng mới lấy page từ mixed extent cho đến khi đủ 8 page, sau đó chuyển sang uniform. Tùy chọn database MIXED_PAGE_ALLOCATION điều khiển việc này trên database người dùng; mặc định OFF, tức dùng uniform extent. Trace flag 1118 không còn tác dụng từ SQL Server 2016 vì hành vi đó đã là mặc định.

Ngoại lệ 8 page đầu có nghĩa cụ thể như sau. Bảng dbo.DonHang vừa tạo, insert dòng đầu tiên: page dữ liệu đó có thể lấy từ mixed extent, dùng chung extent với đối tượng khác, nên bảng ở mức 1 page chiếm 8 KB chứ không phải 64 KB. Khi đối tượng đã có 8 page và cần page thứ 9, lần cấp sau là cả một uniform extent: 8 page, 64 KB, thuộc riêng đối tượng. Bảy page còn lại của extent đó để trống cho đến khi dòng tiếp theo lấp vào. Bật MIXED_PAGE_ALLOCATION (ON) thì database người dùng trở về kiểu cấp mixed cho đến khi đối tượng đủ 8 page, giống master.

5. Page quản lý cấp phát và allocation unit #

Ngoài page chứa dòng, tệp dữ liệu có các page ghi sổ việc cấp phát:

Trong tệp dữ liệu, page 0 là file header, page 1 là PFS đầu tiên, page 2 và 3 là cặp GAM/SGAM đầu tiên. Các page PFS, GAM, SGAM, DCM, BCM lặp lại theo chu kỳ khi tệp lớn lên. IAM được cấp khi allocation unit cần, không đứng ở số page cố định. Bit trên GAM bằng 1 nghĩa là extent đang trống, bằng 0 nghĩa là đã được cấp.

Mỗi partition của heap hoặc index có ít nhất một allocation unit IN_ROW_DATA. Khi phát sinh dữ liệu tràn hoặc LOB, partition đó thêm ROW_OVERFLOW_DATA hoặc LOB_DATA. IAM của từng allocation unit ghi extent nào thuộc về nó trong từng khoảng 4 GB của từng tệp.

Khi heap cần chỗ cho dòng mới, engine tìm extent qua IAM rồi tìm page còn trống qua PFS. PFS ghi mức đầy theo bậc (trống, đến 50%, 51–80%, 81–95%, 96–100%). Không thấy page đủ chỗ thì engine cấp thêm extent.

B-tree không dựa vào PFS để chọn điểm chèn: điểm chèn do giá trị khóa quyết định. Với clustered index trên (NgayTao, DonHangId), đơn mới trong ngày thường có NgayTao lớn nhất nên rơi vào page cuối bên phải của cây. Page cuối đang có 218 dòng thì hết chỗ: engine cấp page mới, viết dòng mới vào đó, và ghi log cho lần cấp page lẫn dòng mới.

Chèn vào giữa cây, chẳng hạn khóa là uniqueidentifier sinh bằng NEWID(), làm đầy một page ở giữa rồi tách đôi: khoảng một nửa dòng chuyển sang page mới. Một lần tách ghi nhiều log hơn một lần thêm vào page cuối, và để lại hai page đầy khoảng một nửa.

6. Áp dụng trong .NET #

Màn hình danh sách hợp đồng của BanHang chỉ cần mã và tóm tắt. Nếu nó đọc qua một entity EF Core ánh xạ đủ bốn cột, mỗi dòng kéo theo cả BanScan. Đo trên LocalDB SQL Server 2019 với đúng hợp đồng 15 ở mục 3 (hai cột chữ 5.000 byte, PDF 2 MB):

262 page LOB_DATA khớp ước lượng 256 page dữ liệu cộng page trung gian của cây LOB. Cột bị đẩy sang overflow ở lần chạy này là DieuKhoan. Ba câu đọc cùng một dòng:

lob logical reads đếm số lần đọc page, không đếm số page khác nhau. Cột 2 MB được gửi về theo từng đoạn, và mỗi đoạn có thể phải đi lại từ page gốc qua page trung gian của cây LOB, nên 1.047 lớn hơn 262 page của cả bảng.

Số lần đọc lấy từ SET STATISTICS IO, số byte từ SqlConnection.RetrieveStatistics(), chạy trên Intel Core Ultra 5 125U, Windows 11. Bài học cho API: câu liệt kê chỉ chọn cột cần (projection Select trong EF Core, hoặc cột liệt kê rõ trong Dapper và SqlClient), còn file tải riêng qua một endpoint stream:

app.MapGet("/hop-dong/{id:int}/ban-scan", async (int id, HttpContext http) =>
{
    await using var cn = new SqlConnection(Db);
    await cn.OpenAsync();
    var cmd = new SqlCommand("SELECT BanScan FROM dbo.HopDong WHERE HopDongId = @id", cn);
    cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
    await using var r = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess);
    if (!await r.ReadAsync() || await r.IsDBNullAsync(0)) return Results.NotFound();
    http.Response.ContentType = "application/pdf";
    await using var lob = r.GetStream(0);              // đọc dần từ LOB_DATA
    await lob.CopyToAsync(http.Response.Body);          // ghi dần ra response
    return Results.Empty;
});

CommandBehavior.SequentialAccess là điều kiện để SqlClient stream giá trị qua mạng. Thiếu nó, ReadAsync nạp cả giá trị vào bộ nhớ trước, theo tài liệu streaming của SqlClient. Bản đầy đủ dưới tạo bảng trên LocalDB, ghi hợp đồng 15, in hai bảng số trên, rồi tải PDF qua endpoint và so từng byte với bản ghi. Đầu file có #:property PublishAot=false vì file-based app của .NET 10 bật PublishAot mặc định, và khi đó JSON dựa trên reflection mà Results.Ok dùng bị tắt.

#:sdk Microsoft.NET.Sdk.Web
#:package Microsoft.Data.SqlClient@7.1.1
#:property PublishAot=false

// dotnet run HopDongPdf.cs   (.NET 10, LocalDB SQL Server 2019)
// Tạo dbo.HopDong như bài, ghi hợp đồng 15 với hai cột chữ 5.000 byte và bản scan 2 MB,
// đếm page theo allocation unit, so số lần đọc của ba câu SELECT, rồi tải PDF qua endpoint stream.
using System.Data;
using Microsoft.Data.SqlClient;

const string Master = @"Server=(localdb)\MSSQLLocalDB;Database=master;Integrated Security=true;TrustServerCertificate=true";
const string Db = @"Server=(localdb)\MSSQLLocalDB;Database=Kumeo_luutru;Integrated Security=true;TrustServerCertificate=true";

await Exec(Master, "IF DB_ID('Kumeo_luutru') IS NOT NULL BEGIN ALTER DATABASE Kumeo_luutru SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE Kumeo_luutru; END; CREATE DATABASE Kumeo_luutru;");
await Exec(Db, """
    CREATE TABLE dbo.HopDong (
        HopDongId int NOT NULL,
        TomTat varchar(5000) NOT NULL,
        DieuKhoan varchar(5000) NOT NULL,
        BanScan varbinary(max) NULL,
        CONSTRAINT PK_HopDong PRIMARY KEY CLUSTERED (HopDongId)
    );
    """);

var pdf = new byte[2 * 1024 * 1024];
new Random(42).NextBytes(pdf);
await using (var cn = new SqlConnection(Db))
{
    await cn.OpenAsync();
    var ins = new SqlCommand("INSERT dbo.HopDong VALUES (15, REPLICATE('T', 5000), REPLICATE('D', 5000), @pdf)", cn);
    ins.Parameters.Add("@pdf", SqlDbType.VarBinary, -1).Value = pdf;
    await ins.ExecuteNonQueryAsync();

    // Page của từng allocation unit.
    var stats = new SqlCommand("""
        SELECT alloc_unit_type_desc, SUM(page_count)
        FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.HopDong'), NULL, NULL, 'DETAILED')
        GROUP BY alloc_unit_type_desc
        ORDER BY CHARINDEX(LEFT(alloc_unit_type_desc, 2), 'INROLO');
        """, cn);
    await using (var r = await stats.ExecuteReaderAsync())
        while (await r.ReadAsync()) Console.WriteLine($"{r.GetString(0),-18} {r.GetInt64(1),4} page");

    // Số lần đọc page (SET STATISTICS IO) và số byte về tới ứng dụng (SqlConnection statistics).
    var io = "";
    cn.InfoMessage += (_, e) =>
    {
        var m = System.Text.RegularExpressions.Regex.Match(e.Message, @" logical reads (\d+),.* lob logical reads (\d+),");
        if (m.Success) io = $"logical reads {m.Groups[1].Value}, lob logical reads {m.Groups[2].Value}";
    };
    await new SqlCommand("SET STATISTICS IO ON;", cn).ExecuteNonQueryAsync();
    cn.StatisticsEnabled = true;
    foreach (var cot in new[] { "HopDongId, TomTat", "HopDongId, TomTat, DieuKhoan", "*" })
    {
        cn.ResetStatistics();
        await new SqlCommand($"SELECT {cot} FROM dbo.HopDong WHERE HopDongId = 15", cn).ExecuteNonQueryAsync();
        Console.WriteLine($"SELECT {cot,-28} {io}, {cn.RetrieveStatistics()["BytesReceived"]:N0} byte nhận");
    }
}

// Endpoint: danh sách không kéo BanScan; tải PDF bằng stream, không nạp 2 MB vào một mảng.
var builder = WebApplication.CreateBuilder(args);
builder.Logging.ClearProviders();
var app = builder.Build();

app.MapGet("/hop-dong", async () =>
{
    await using var cn = new SqlConnection(Db);
    await cn.OpenAsync();
    var cmd = new SqlCommand("SELECT HopDongId, TomTat FROM dbo.HopDong ORDER BY HopDongId", cn);
    var list = new List<object>();
    await using var r = await cmd.ExecuteReaderAsync();
    while (await r.ReadAsync()) list.Add(new { id = r.GetInt32(0), tomTat = r.GetString(1)[..20] });
    return Results.Ok(list);
});

app.MapGet("/hop-dong/{id:int}/ban-scan", async (int id, HttpContext http) =>
{
    await using var cn = new SqlConnection(Db);
    await cn.OpenAsync();
    var cmd = new SqlCommand("SELECT BanScan FROM dbo.HopDong WHERE HopDongId = @id", cn);
    cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
    await using var r = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess);
    if (!await r.ReadAsync() || await r.IsDBNullAsync(0)) return Results.NotFound();
    http.Response.ContentType = "application/pdf";
    await using var lob = r.GetStream(0);              // đọc dần từ LOB_DATA
    await lob.CopyToAsync(http.Response.Body);          // ghi dần ra response
    return Results.Empty;
});

app.Urls.Add("http://127.0.0.1:5462");
await app.StartAsync();
using (var http = new HttpClient { BaseAddress = new Uri("http://127.0.0.1:5462") })
{
    Console.WriteLine($"GET /hop-dong: {await http.GetStringAsync("/hop-dong")}");
    var bytes = await http.GetByteArrayAsync("/hop-dong/15/ban-scan");
    Console.WriteLine($"GET /hop-dong/15/ban-scan: {bytes.Length:N0} byte, khớp bản ghi: {bytes.AsSpan().SequenceEqual(pdf)}");
}
await app.StopAsync();

SqlConnection.ClearAllPools();
await Exec(Master, "ALTER DATABASE Kumeo_luutru SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE Kumeo_luutru;");

static async Task Exec(string cs, string sql)
{
    await using var cn = new SqlConnection(cs);
    await cn.OpenAsync();
    await new SqlCommand(sql, cn).ExecuteNonQueryAsync();
}

IN_ROW_DATA           1 page
ROW_OVERFLOW_DATA     1 page
LOB_DATA            262 page
SELECT HopDongId, TomTat            logical reads 2, lob logical reads 0, 5,694 byte nhận
SELECT HopDongId, TomTat, DieuKhoan logical reads 2, lob logical reads 1, 10,737 byte nhận
SELECT *                            logical reads 2, lob logical reads 1047, 2,111,083 byte nhận
GET /hop-dong: [{"id":15,"tomTat":"TTTTTTTTTTTTTTTTTTTT"}]
GET /hop-dong/15/ban-scan: 2,097,152 byte, khớp bản ghi: True

Những chỗ hay hiểu sai #

• “Page 8 KB thì một dòng được dài 8 KB.” Sai: giới hạn dòng là 8.060 byte; header và slot array chiếm phần còn lại của 8.192 byte.
• “Row-overflow và LOB là một cơ chế.” Sai: row-overflow dùng con trỏ 24 byte và ROW_OVERFLOW_DATA; LOB dùng con trỏ 16 byte và LOB_DATA.
• “Bảng mới luôn chiếm ngay một extent 64 KB.” Sai: 8 page đầu của chuỗi IAM vẫn có thể lấy từ mixed extent; từ SQL Server 2016, phần sau mới là uniform extent theo mặc định.
• “SELECT * chỉ tốn thêm băng thông mạng.” Sai: với cột LOB, nó còn tốn hàng trăm đến hàng nghìn lần đọc page phía server.

Kết luận #

Kích thước dòng quyết định một page chứa bao nhiêu đơn, còn vị trí của cột lớn quyết định một câu đọc tốn vài lần đọc page hay hơn một nghìn. Ứng dụng .NET kiểm soát phần thứ hai bằng cách chọn cột.

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

• Liệt kê bằng projection (Select(h => new { h.HopDongId, h.TomTat })) hoặc một entity riêng không có cột varbinary(max); không trả entity đầy đủ cho màn hình danh sách.
• Tải file lớn bằng ExecuteReaderAsync(CommandBehavior.SequentialAccess) và GetStream, copy thẳng vào Response.Body.
• Trước khi tạo bảng lớn, ước lượng cỡ dòng và số dòng mỗi page bằng công thức ở mục 2, rồi đo lại bằng sys.dm_db_index_physical_stats.
• Đo lob logical reads của các truy vấn nóng bằng SET STATISTICS IO khi bảng có cột max.
• Không dùng Guid.NewGuid() làm khóa clustered của bảng ghi nhiều; khóa tăng dần ghi vào page cuối thay vì tách page ở giữa cây.

Đọc tiếp #

• Kiến trúc lưu trữ: tệp và filegroup: bài trước trong series, các tệp và filegroup chứa những page này.
• Write-ahead log, VLF và recovery model: bài tiếp theo, đường đi của một lần sửa page qua log.
• Chỉ mục B-tree, seek, scan và key lookup: các page xếp thành cây B-tree ra sao, và một lần tìm đơn tốn bao nhiêu lần đọc.
• Kiểu dữ liệu: byte trên page và kiểu cho tiền: vì sao dòng DonHang là 35 byte chứ không phải 47.

Nguồn #

• Pages and extents architecture guide
• Estimate the size of a clustered index
• Maximum capacity specifications
• SqlClient streaming support