Kiến trúc lưu trữ

Tài liệu này mô tả cách SQL Server giữ dữ liệu: từ tệp trên đĩa, qua filegroup, page và extent, đến đường ghi có nhật ký đi trước, rồi chiến lược sao lưu gắn với recovery model.

Phạm vi là Database Engine của SQL Server (kèm các khái niệm dùng chung với Azure SQL Database và Azure SQL Managed Instance ở mức lưu trữ logic). Các giới hạn số liệu dưới đây theo tài liệu dung lượng của SQL Server 2016 trở đi. Chỗ hành vi đổi theo phiên bản được ghi rõ.

1. Bản đồ phân cấp #

Một database có hai nhánh lưu trữ tách nhau.

Nhánh dữ liệu đi từ database xuống filegroup, rồi tệp dữ liệu, extent, page, và dòng. Nhánh nhật ký là một hoặc nhiều tệp log. Tệp log không nằm trong filegroup nào, và bên trong log không có page 8 KB. Log là một chuỗi bản ghi có độ dài thay đổi, chia thành các virtual log file (VLF).

flowchart TB
  DB[Database]
  FG[Filegroup]
  DF["Tệp dữ liệu .mdf / .ndf"]
  EXT["Extent — 8 page, 64 KB"]
  PG["Page — 8 KB"]
  ROW[Dòng dữ liệu]
  LF["Tệp log .ldf"]
  VLF[Chuỗi VLF và log record]

  DB --> FG
  DB --> LF
  FG --> DF
  DF --> EXT
  EXT --> PG
  PG --> ROW
  LF --> VLF

Đơn vị đọc và ghi dữ liệu trên đĩa là cả page. SQL Server không đọc một dòng lẻ từ tệp dữ liệu rồi bỏ phần còn lại của page.

Đuôi .mdf, .ndf, .ldf là quy ước. Engine nhận diện tệp bằng header bên trong. Vẫn đặt đúng đuôi này để người vận hành và công cụ nhận ra vai trò tệp ngay từ tên.

Các ví dụ phía dưới dùng một database bán hàng tên BanHang trên SQL Server 2019, recovery model FULL. Ổ đĩa trong ví dụ là các volume tách nhau. Nếu tất cả đường dẫn nằm trên cùng một ổ vật lý, cách chia filegroup không thêm băng thông.

Bảng trên là bố trí mục tiêu. Script CREATE DATABASE phía dưới dùng kích thước nhỏ hơn để chạy thử: data chính 512 MB, file FG_DATA thứ nhất 8 GB, archive 8 GB, log 4 GB. File FG_DATA thứ hai, thêm sau, là 200 GB. Bảng kết quả ngay sau script khớp với các lệnh đó, không khớp với cột mục tiêu.

2. Tệp vật lý #

Mỗi database có ít nhất một tệp dữ liệu chính và một tệp nhật ký.

Tệp dữ liệu chính #

Tệp chính thuộc filegroup PRIMARY. Trong tệp này có thông tin khởi động của database (boot page) và các bảng hệ thống. Một database có đúng một tệp dữ liệu chính. Tệp này không gỡ được chừng nào database còn tồn tại.

Tệp dữ liệu phụ #

Tệp phụ dùng khi cần thêm dung lượng, trải dữ liệu ra nhiều đĩa, hoặc tách dữ liệu sang một filegroup riêng. Tệp phụ có thể nằm trong PRIMARY hoặc trong filegroup do người quản trị tạo. Không có tệp phụ thì database vẫn chạy bình thường.

Tệp nhật ký #

Tệp log ghi lại các thao tác làm thay đổi dữ liệu, đủ để làm lại (redo) phần đã commit và hoàn tác (undo) phần chưa commit khi SQL Server khởi động lại sau sự cố. Đây là nền của tính bền trong ACID và của phục hồi theo thời điểm.

Có thể tạo nhiều tệp log, nhưng engine ghi đầy tệp này rồi mới sang tệp kế tiếp. Nhiều tệp log không chia tải ghi song song theo kiểu các tệp dữ liệu trong cùng một filegroup. Cách đặt có ích là một tệp log trên ổ dành riêng, cấp phát sẵn dung lượng, tách khỏi ổ dữ liệu để lần ghi log tuần tự không tranh I/O ngẫu nhiên với data file.

Tăng trưởng tệp #

Mỗi tệp có kích thước hiện tại và bước autogrowth. Trong một filegroup có nhiều tệp đều bật autogrowth, engine chỉ nới tệp khi mọi tệp trong nhóm đã đầy, và nới lần lượt từng tệp.

Với tệp dữ liệu, nếu tài khoản dịch vụ SQL Server có quyền Perform volume maintenance tasks, Windows cấp không gian mà không điền số 0 trước (instant file initialization). Tệp log luôn được điền số 0 khi nới, nên autogrowth của log làm giao dịch đang chờ ghi log phải dừng lại cho đến khi phần mới sẵn sàng.

Cấp phát log theo bước lớn và đều, ngay từ lúc tạo database. Autogrowth nhỏ lặp lại nhiều lần cắt log thành rất nhiều VLF. Quá nhiều VLF làm chậm recovery lúc khởi động, chậm log backup, và chậm các thao tác quét log.

Với thuật toán dùng từ SQL Server 2014 đến 2019, mỗi lần tạo hoặc nới log sinh số VLF theo kích thước của lần nới đó: đến 64 MB thì 4 VLF, trên 64 MB đến 1 GB thì 8 VLF, trên 1 GB thì 16 VLF. Từ SQL Server 2022, nếu bước nới nhỏ hơn 1/8 dung lượng log hiện tại thì lần nới đó chỉ thêm 1 VLF.

Ví dụ xấu trên SQL Server 2019: tạo log 64 MB, FILEGROWTH = 64MB, rồi để autogrowth đưa log lên 64 GB. Có 1.024 lần cấp 64 MB, mỗi lần 4 VLF, tổng khoảng 4.096 VLF. Khởi động sau sự cố phải mở hàng nghìn VLF trước khi redo.

Nếu tạo log 8 GB ngay từ đầu, như cột mục tiêu của BanHang: lần tạo lớn hơn 1 GB nên ra khoảng 16 VLF, mỗi VLF khoảng 512 MB. FILEGROWTH = 1GB đúng bằng 1 GB nên mỗi lần nới thêm, đến SQL Server 2019, thêm 8 VLF. Log lên 16 GB thì còn khoảng 80 VLF, không phải hàng nghìn. Script tạo database phía dưới để log 4 GB cho máy thử. 4 GB vẫn lớn hơn 1 GB nên cũng ra khoảng 16 VLF, mỗi VLF khoảng 256 MB.

Nới log 1 GB luôn ghi số 0 trên toàn bộ phần mới. Giao dịch đang COMMIT phải chờ lần ghi đó xong. Trên đĩa chậm, một lần nới 1 GB có thể giữ commit trong nhiều giây. Nới data file 1 GB, khi đã bật instant file initialization, gần như chỉ cập nhật kích thước tệp.

Đếm VLF từ SQL Server 2017 (và SQL Server 2016 SP2):

SELECT
    COUNT(*) AS vlf_count,
    SUM(vlf_size_mb) AS log_mb,
    SUM(CASE WHEN vlf_active = 1 THEN 1 ELSE 0 END) AS active_vlf_count
FROM sys.dm_db_log_info(DB_ID(N'BanHang'));

Nếu vlf_count lên vài nghìn, tạo lại log ở kích thước đủ dùng trong cửa sổ bảo trì thay vì tiếp tục nới từng bước nhỏ.

3. Filegroup #

Filegroup là lớp logic gom các tệp dữ liệu. Bảng, chỉ mục, và partition được tạo trên một filegroup, không được tạo trực tiếp “trên một file”.

Ba quy tắc thiết kế:

• Một tệp chỉ thuộc một database và một filegroup.
• Tệp log không thuộc filegroup nào.
• Đối tượng hệ thống nằm ở PRIMARY, nên filegroup này cần còn chỗ và cần nằm trong mọi chiến lược sao lưu toàn bộ database.

Khi một filegroup có nhiều tệp, engine cấp phát theo tỷ lệ chỗ trống (proportional fill). Tệp còn trống gấp đôi tệp kia sẽ nhận khoảng gấp đôi lượng extent mới. Các tệp vì vậy tiến tới mức đầy tương đương nhau, thay vì nhồi một tệp đến cạn rồi mới sang tệp sau.

Với FG_DATA của BanHang, giả sử BanHang_data_01 còn trống 100 GB và BanHang_data_02 còn trống 200 GB. Một đợt ghi 30 GB dữ liệu mới được chia khoảng 10 GB vào tệp thứ nhất và 20 GB vào tệp thứ hai. Sau đợt ghi, cả hai tệp vẫn còn trống theo tỷ lệ gần 1:2.

Khi cả hai tệp đều đầy, engine không nới đồng thời. Nó nới tệp đứng trước trong filegroup thêm đúng một bước FILEGROWTH của tệp đó, ghi vào phần vừa nới, và chỉ nới tệp tiếp theo khi tệp vừa nới lại đầy. Trong script này, file thứ nhất nới 1 GB, file thứ hai nới 8 GB. Một insert lớn vì vậy có thể dừng một nhịp để chờ nới đĩa, rồi chạy tiếp.

Ứng dụng thực tế của filegroup là đặt phần hay đọc ghi lên ổ nhanh và phần lưu trữ lâu lên ổ dung lượng lớn:

• PRIMARY: metadata và các bảng nhỏ dùng chung.
• FG_DATA trên SSD: bảng giao dịch hiện tại.
• FG_INDEX trên SSD: chỉ mục nặng, tách I/O khỏi heap hoặc clustered index khi cần đo và chứng minh lợi ích.
• FG_ARCHIVE trên HDD: partition lịch sử, có thể để read-only.

Tách filegroup chỉ có lợi khi đường đĩa phía dưới thực sự khác nhau. Nhiều filegroup trên cùng một ổ vật lý không tạo thêm băng thông.

Filegroup còn là đơn vị của backup và restore từng phần. Có thể sao lưu riêng filegroup đọc-ghi, và với recovery model FULL có thể restore từng file hoặc từng page. Phần sao lưu bên dưới nói điều kiện để việc đó khôi phục được.

Ví dụ tạo database có filegroup dữ liệu và filegroup lưu trữ. Đường dẫn phải tồn tại trước khi chạy.

CREATE DATABASE BanHang
ON PRIMARY (
    NAME = N'BanHang_data',
    FILENAME = N'D:\SqlData\BanHang.mdf',
    SIZE = 512MB,
    FILEGROWTH = 256MB
),
FILEGROUP FG_DATA (
    NAME = N'BanHang_data_01',
    FILENAME = N'E:\SqlData\BanHang_data_01.ndf',
    SIZE = 8GB,
    FILEGROWTH = 1GB
),
FILEGROUP FG_ARCHIVE (
    NAME = N'BanHang_archive_01',
    FILENAME = N'F:\SqlData\BanHang_archive_01.ndf',
    SIZE = 8GB,
    FILEGROWTH = 1GB
)
LOG ON (
    NAME = N'BanHang_log',
    FILENAME = N'L:\SqlLog\BanHang_log.ldf',
    SIZE = 4GB,
    FILEGROWTH = 1GB
);
GO

ALTER DATABASE BanHang MODIFY FILEGROUP FG_DATA DEFAULT;
GO

Sau lệnh này, bảng tạo mới mà không ghi ON [filegroup] sẽ vào FG_DATA. Bảng hệ thống vẫn ở PRIMARY.

CREATE TABLE dbo.DonHang (
    DonHangId bigint NOT NULL,
    NgayTao datetime2(0) NOT NULL,
    KhachHangId int NOT NULL,
    CONSTRAINT PK_DonHang PRIMARY KEY CLUSTERED (DonHangId)
) ON FG_DATA;
GO

CREATE INDEX IX_DonHang_NgayTao
    ON dbo.DonHang (NgayTao)
    ON FG_DATA;
GO

Chuyển một bảng đã có sang filegroup khác bằng cách tạo lại clustered index trên filegroup đích. Thao tác này ghi lại toàn bộ dữ liệu bảng, cần chỗ trống và nên nằm trong cửa sổ bảo trì.

CREATE UNIQUE CLUSTERED INDEX PK_DonHang
    ON dbo.DonHang (DonHangId)
    WITH (DROP_EXISTING = ON, ONLINE = ON)
    ON FG_ARCHIVE;

ONLINE = ON cần phiên bản có hỗ trợ online index rebuild. Trên phiên bản không có tính năng này, bỏ mệnh đề ONLINE.

Thêm tệp thứ hai vào FG_DATA khi muốn proportional fill. Thư mục G:\SqlData phải có sẵn.

ALTER DATABASE BanHang
ADD FILE (
    NAME = N'BanHang_data_02',
    FILENAME = N'G:\SqlData\BanHang_data_02.ndf',
    SIZE = 200GB,
    FILEGROWTH = 8GB
) TO FILEGROUP FG_DATA;

Xem file và filegroup hiện tại:

SELECT
    df.name AS logical_name,
    df.physical_name,
    fg.name AS filegroup_name,
    df.type_desc,
    df.size * 8 / 1024 AS size_mb,
    df.growth AS growth_value,
    df.is_percent_growth
FROM sys.database_files AS df
LEFT JOIN sys.filegroups AS fg
    ON df.data_space_id = fg.data_space_id
ORDER BY df.type, df.file_id;

Kết quả đúng hình dạng sau. Cột filegroup_name của tệp log trống, vì log không gia nhập filegroup nào. size trong sys.database_files đếm bằng page 8 KB, nên size * 8 / 1024 ra MB.

Đơn hàng mới rơi vào đâu #

Bảng ở ví dụ trên chưa phân vùng: mọi dòng dbo.DonHang nằm trên FG_DATA, rồi proportional fill chia giữa E: và G:. Khi bảng lớn và có phần lịch sử ít khi sửa, đặt sẵn partition để dòng mới và dòng cũ không chung filegroup.

Mốc RANGE RIGHT dưới đây chia bốn vùng: trước 2025, năm 2025, năm 2026, và từ 2027 trở đi. Khóa clustered phải chứa cột phân vùng. Phần còn lại của chương dùng bảng này thay cho bảng tối giản vừa tạo.

DROP TABLE IF EXISTS dbo.DonHang;
GO

CREATE PARTITION FUNCTION pf_DonHang_Ngay (datetime2(0))
AS RANGE RIGHT FOR VALUES (
    CAST('20250101' AS datetime2(0)),
    CAST('20260101' AS datetime2(0)),
    CAST('20270101' AS datetime2(0))
);
GO

CREATE PARTITION SCHEME ps_DonHang_Ngay
AS PARTITION pf_DonHang_Ngay
TO (FG_ARCHIVE, FG_ARCHIVE, FG_DATA, FG_DATA);
GO

CREATE TABLE dbo.DonHang (
    DonHangId bigint NOT NULL,
    NgayTao datetime2(0) NOT NULL,
    KhachHangId int NOT NULL,
    TrangThai tinyint NOT NULL,
    TongTien decimal(18, 2) NOT NULL,
    CONSTRAINT PK_DonHang PRIMARY KEY CLUSTERED (NgayTao, DonHangId)
) ON ps_DonHang_Ngay (NgayTao);
GO

CREATE INDEX IX_DonHang_KhachHang
    ON dbo.DonHang (KhachHangId, NgayTao)
    ON ps_DonHang_Ngay (NgayTao);
GO

Ba dòng cụ thể:

Câu lệnh sau chỉ đụng partition năm 2026, tức các tệp của FG_DATA:

SELECT DonHangId, TongTien
FROM dbo.DonHang
WHERE NgayTao >= CAST('20261001' AS datetime2(0))
  AND NgayTao < CAST('20261003' AS datetime2(0));

Dòng năm 2024 trên F: không nằm trong phạm vi này. Muốn phần lịch sử ngừng đổi, sau khi đã chuyển hết năm cũ sang FG_ARCHIVE, đặt filegroup đó read-only. Extent trên đó ngừng bị đánh dấu trong DCM, nên differential backup các ngày sau không phải đọc lại cả kho lưu trữ.

ALTER DATABASE BanHang MODIFY FILEGROUP FG_ARCHIVE READ_ONLY;

Filegroup read-only không nhận INSERT, UPDATE, DELETE. Muốn nạp thêm dữ liệu cũ, chuyển lại READ_WRITE, nạp, rồi khóa lại. Kịch bản xóa nhầm ở mục sao lưu giả định bước này chưa chạy: FG_ARCHIVE vẫn ghi được, nên lệnh xóa đơn cũ mới commit. Nếu filegroup đã read-only, chính lệnh xóa đó bị từ chối và không có gì phải restore.

4. Page, extent, và allocation unit #

Cấu trúc một page #

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. Dòng trên page có thể không nằm đúng thứ tự vật lý sau nhiều lần sửa và xóa. Thứ tự logic do slot array giữ, nên khi đọc theo khóa của index, engine đi theo slot chứ không đi theo vị trí byte thô.

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

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

Lấy dòng DonHangId = 10042, mọi cột độ dài cố định:

Công thức ước lượng của Microsoft cho một heap hoặc clustered index chỉ có cột cố định: cỡ dòng = dữ liệu cố định + null bitmap + 4 byte header. Null bitmap = 2 + ((số cột + 7) / 8), chia nguyên. Năm cột ra 2 + 1 = 3 byte. Cỡ dòng = 28 + 3 + 4 = 35 byte. Thêm 2 byte của slot array thì mỗi dòng chiếm 37 byte trên page.

Phần dành cho dòng là 8.192 − 96 = 8.096 byte. Số dòng tối đa trên một page = 8096 / 37 = 218 dòng. Phần dư 8.096 − (218 × 37) = 30 byte, không đủ một dòng thứ 219.

Page chứa đơn 10042, ở dạng đã đầy, trông như sau:

218 dòng kiểu này là khoảng 8 KB. 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 chỉ mục IX_DonHang_KhachHang và chưa tính page hệ thống. Đây là ước lượng theo công thức, dùng để 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ó thêm 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.

Các loại page #

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 ở một số page cố định.

Bit trên GAM bằng 1 nghĩa là extent đang trống. Bit bằng 0 nghĩa là extent đã được cấp.

PFS ghi mức đầy theo bậc (trống, đến 50%, 51–80%, 81–95%, 96–100%), chủ yếu để tìm chỗ cho heap và trang LOB. B-tree không dựa vào mức trống này để chọn điểm chèn: điểm chèn do giá trị khóa quyết định. Hết chỗ thì page tách, khoảng một nửa dữ liệu sang page mới.

Giới hạn một dòng #

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.

Ba cách dữ liệu vượt khỏ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 kiểu độ dài cố định (char, nchar, int, …) cộng lại vẫn phải nằm trong 8.060 byte.

Row-overflow dịch chuyển khi UPDATE làm dòng dài ra hoặc ngắn lại. 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 thì rẻ hơn là để engine chuyển đi chuyển lại.

Khóa của clustered index không được chứa giá trị đang nằm ở ROW_OVERFLOW_DATA. INSERT hoặc UPDATE đẩy khóa clustered ra khỏi dòng sẽ thất bại.

Bảng dùng sparse column có giới hạn dòng 8.018 byte.

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

Bảng hợp đồng có hai cột chữ dài:

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. Riêng phần chữ đã 10.000 byte, 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 trên dòng gốc. Hai cột bằng nhau thì engine chọn một cột để đẩy. Sau bước đó dòng gốc còn một cột 5.000 byte cộng con trỏ, nằm 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). Giá trị không nằm trong 8.060 byte, nên dòng gốc giữ con trỏ LOB 16 byte. Phầ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. Kéo SELECT * trên bảng này biến một lần tìm theo khóa thành hàng trăm lần đọc page.

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 đã 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.

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 là 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ủa chuỗi IAM 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. Bảng ở mức 1 page thì phần dữ liệu là 8 KB, không phải một uniform extent 64 KB riêng. 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.

MIXED_PAGE_ALLOCATION bật lại (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. Mặc định OFF giữ uniform extent cho phần cấp phát ngoài 8 page đầu của chuỗi IAM.

Allocation unit #

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. Khi không thấy page đủ chỗ trong các extent hiện có, engine cấp thêm extent.

Với clustered index trên (NgayTao, DonHangId), điểm chèn do khóa quyết định, không do PFS. Đơ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.

5. Đường ghi dữ liệu #

Sửa một dòng không có nghĩa là ghi ngay page đó xuống .mdf. Đường đi gồm bộ nhớ, nhật ký, rồi mới tới tệp dữ liệu.

Buffer pool #

Page dữ liệu được đọc vào buffer pool trong RAM. Sửa dòng là một lần ghi logic trên page trong bộ nhớ. Page bị sửa được đánh dấu dirty. Nhiều lần sửa có thể dồn trên cùng một dirty page trước khi page đó được ghi vật lý ra đĩa.

Write-ahead logging #

Mỗi lần sửa tạo log record trong log cache. Quy tắc write-ahead logging: log record của lần sửa phải đã nằm trên đĩa trước khi dirty page tương ứng được đưa ra khỏi buffer pool và ghi xuống tệp dữ liệu.

Khi giao dịch COMMIT, engine làm cứng log của giao dịch đó xuống .ldf rồi mới báo thành công cho client. Dirty page của dữ liệu có thể vẫn nằm trong RAM sau commit. Mất điện lúc này không mất giao dịch đã commit, vì recovery đọc log và làm lại thay đổi trên page dữ liệu.

Thứ tự đúng của một UPDATE đã commit:

1. Page dữ liệu nằm trong buffer pool, hoặc được đọc lên nếu chưa có.
2. Log record được ghi vào log cache.
3. Page trong bộ nhớ bị sửa và trở thành dirty page.
4. Lúc COMMIT, log record được flush xuống .ldf.
5. Client nhận kết quả thành công.
6. Sau đó checkpoint, lazy writer, hoặc eager writer mới ghi dirty page xuống .mdf hoặc .ndf.

Bước 6 được phép xảy ra trước commit nếu log của lần sửa đó đã ở trên đĩa. Điều WAL cấm là ghi dirty page khi log tương ứng chưa bền.

Sửa đơn 10042, theo từng thời điểm #

Đơn 10042 tạo lúc 11:58 ngày 2026-10-02, TongTien = 1500000.00, nằm trên FG_DATA. Lúc 12:04 ứng dụng chạy:

BEGIN TRAN;

UPDATE dbo.DonHang
SET TongTien = 1750000.00
WHERE DonHangId = 10042
  AND NgayTao = CAST('2026-10-02T11:58:00' AS datetime2(0));

-- Chưa COMMIT. Page trong RAM đã là 1.750.000 và được đánh dấu dirty.
-- Log record đang ở log cache. Tệp .ndf trên E: hoặc G: vẫn là 1.500.000.
-- Giao dịch khác ở READ COMMITTED vẫn đọc thấy 1.500.000.

COMMIT;

-- Log record đã được ghi xuống L:\SqlLog\BanHang_log.ldf.
-- Client nhận thành công. Phiên khác đọc thấy 1.750.000 từ buffer pool.
-- Tệp .ndf có thể vẫn đang chứa 1.500.000 cho đến checkpoint hoặc lazy writer.

Ba cách mất điện ngay sau đó:

Trường hợp thứ ba là lý do commit có thể trả về trước khi .ndf đổi. Độ bền nằm ở L:\SqlLog\BanHang_log.ldf, không nằm ở việc page dữ liệu đã xuống E: hay chưa.

Nếu phiên giữ BEGIN TRAN từ 09:00 và không commit, các log backup sau 09:00 vẫn chạy nhưng, khi ADR tắt, không giải phóng được VLF cũ hơn giao dịch đó. Log đầy dần dù lịch backup đều. DBCC OPENTRAN('BanHang') cho thấy giao dịch cũ nhất và thời điểm nó bắt đầu. ADR, mặc định tắt trên bản tự cài, đổi cách cắt log này. Chương kỹ thuật nói riêng phần đó.

Ai đẩy dirty page xuống đĩa #

Checkpoint tự chạy theo mục tiêu thời gian recovery, theo lượng log phát sinh, và khi có thay đổi cấu trúc như thêm hoặc gỡ tệp. Người quản trị có thể gọi CHECKPOINT. Database tạo trên SQL Server 2016 trở đi dùng indirect checkpoint theo mặc định: engine dàn việc ghi dirty page để thời gian recovery mục tiêu gần với TARGET_RECOVERY_TIME.

Checkpoint ghi dirty page và ghi một mốc vào log. Việc đánh dấu phần log cũ là tái sử dụng được thì phụ thuộc recovery model. Trong SIMPLE, phần log không còn cần cho recovery được giải phóng quanh thời điểm checkpoint. Trong FULL và BULK_LOGGED, checkpoint không giải phóng phần log đó; log backup mới làm việc này.

Recovery lúc khởi động #

Sau sự cố, recovery đi từ checkpoint gần nhất:

• Redo: áp lại log record của thay đổi đã ghi log nhưng dirty page chưa kịp xuống đĩa.
• Undo: hoàn tác giao dịch chưa commit.

Thời gian recovery tỷ lệ với lượng log phải redo và undo. Giữ log nhỏ bằng log backup đều, và giữ mục tiêu recovery hợp lý, trực tiếp rút ngắn lúc SQL Server mở lại database sau sự cố.

6. Recovery model #

Recovery model quyết định log được giữ đến khi nào và bản sao lưu nào có nghĩa.

BULK_LOGGED là biến thể của FULL cho những lúc nạp dữ liệu lớn. Thao tác bulk đủ điều kiện được ghi log tối thiểu, nên nhanh hơn và log nhỏ hơn, nhưng bản log backup phải kéo theo các extent đã đổi (đọc từ BCM) để vẫn restore được. Trong khoảng có bulk-logged change, không restore về một giây nằm giữa khoảng đó.

Đổi từ SIMPLE sang FULL hoặc BULK_LOGGED chưa mở chuỗi log. Hãy lấy một full backup ngay sau khi đổi. Bản full đó là mốc đầu. Log backup lấy sau mốc này mới tạo được chuỗi khôi phục theo thời điểm.

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = N'BanHang';

ALTER DATABASE BanHang SET RECOVERY FULL;

Với database giao dịch, model làm việc là FULL, kèm lịch log backup. SIMPLE phù hợp database có thể nạp lại, hoặc nơi chấp nhận mất phần phát sinh từ bản full hay differential gần nhất.

Log trong FULL tăng cho đến khi có log backup. Full backup không cắt log. Differential backup cũng không cắt log.

Cùng một sự cố lúc 14:00 ngày 2026-10-02, ba cách cấu hình cho ba kết cục khác nhau. Mốc sao lưu dùng chung: full lúc 22:00 hôm trước, differential lúc 12:00 cùng ngày.

15 phút ở hàng thứ hai chính là RPO của lịch log backup. Muốn RPO là 5 phút thì log backup cách nhau 5 phút, và ổ log vẫn phải sống sót độc lập với ổ dữ liệu nếu muốn hàng thứ ba.

7. Sao lưu và khôi phục #

Ba bản sao lưu cốt lõi #

Chuỗi log độc lập với lịch full và differential. Một log backup lấy lúc 20:00 vẫn chứa log từ log backup lúc 16:00, kể cả khi ở giữa có một full backup lúc 18:00.

Ngoài ba loại trên còn có bản dùng trong vận hành:

• Copy-only full không trở thành gốc mới của differential. Dùng khi cần một bản mang đi mà không làm gãy lịch differential đang chạy.
• Copy-only log không đẩy điểm cắt log. Các log backup thường vẫn nối tiếp nhau. Bản copy-only dùng khi cần một bản log mang đi mà lịch cắt log đang chạy vẫn giữ nguyên.
• File và filegroup backup sao lưu một phần database.
• Tail-log backup lấy nốt log chưa sao lưu ngay trước khi restore, để không mất phần giao dịch sau log backup cuối.

Lịch tham khảo #

Với database ở recovery model FULL, một lịch khởi đầu thường là:

• Full mỗi ngày hoặc mỗi tuần, tùy kích thước và cửa sổ backup.
• Differential vài lần trong ngày nếu full lớn và lượng đổi trong ngày nhiều.
• Log backup từ 5 đến 15 phút một lần. Khoảng này xấp xỉ lượng dữ liệu chấp nhận mất khi mất cả máy lẫn log (RPO).

RPO là lượng dữ liệu chấp nhận mất. RPO 15 phút nghĩa là log backup cách nhau không quá 15 phút, và tệp log nên nằm trên lưu trữ chịu lỗi vì phần log chưa backup sẽ mất nếu đĩa log hỏng.

RTO là thời gian chấp nhận ngừng. RTO quyết định full và differential phải restore xong trong bao lâu, nên ảnh hưởng tới kích thước bản backup, tốc độ đĩa backup, và việc có cần filegroup read-only để khỏi restore phần lịch sử hay không.

Minh họa kích thước, không phải số đo của một máy cụ thể. BanHang dữ liệu 500 GB. Đêm 2026-10-01 full backup nén còn khoảng vài trăm GB tùy dữ liệu có nén sẵn hay không. Trong ngày 2026-10-02 chỉ 8 GB extent đổi so với full. Differential đọc DCM và bản backup xấp xỉ phần đổi đó, không xấp xỉ 500 GB. Nếu mỗi ngày đổi thêm khoảng 8 GB và không lấy full mới, differential ngày thứ sáu mang phần lớn những extent đã đổi từ ngày một đến ngày sáu. Khi differential phình tới mức restore nó chẳng nhanh hơn restore một chuỗi log dài, lấy full mới và bắt đầu differential từ mốc đó.

Lệnh backup #

Đặt tên tệp có ngày giờ. Bật CHECKSUM để ghi checksum và để lần restore sau kiểm tra được. COMPRESSION giảm dung lượng và thường giảm cả thời gian nếu nút cổ chai là đường ghi backup.

BACKUP DATABASE BanHang
TO DISK = N'B:\Backup\BanHang_full_20261001_2200.bak'
WITH
    INIT,
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
GO

BACKUP DATABASE BanHang
TO DISK = N'B:\Backup\BanHang_diff_20261002_1200.bak'
WITH
    DIFFERENTIAL,
    INIT,
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
GO

BACKUP LOG BanHang
TO DISK = N'B:\Backup\BanHang_log_20261002_1215.trn'
WITH
    INIT,
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
GO

INIT ghi đè các backup set trong đúng tệp đích, giữ media header nếu còn tương thích. FORMAT tạo media header mới và xóa nội dung backup cũ trên tệp đó. Chỉ dùng FORMAT khi cố ý bỏ toàn bộ bản đang nằm trong tệp đích.

Một tệp .bak có thể chứa nhiều backup set nếu không dùng INIT. Restore lúc đó phải chỉ đúng FILE = n. Với vận hành, mỗi lần backup một tệp riêng, tên có thời điểm, dễ kiểm kê hơn là nhiều set chồng trong một file.

Thứ tự restore #

Khôi phục về cuối chuỗi, database đang ở FULL:

1. Tail-log backup nếu tệp log còn đọc được. Tail-log để lại database ở trạng thái restoring (WITH NORECOVERY) khi mục tiêu là thay thế chính database đó.
2. Full backup gần nhất, WITH NORECOVERY.
3. Differential mới nhất dựa trên full đó, nếu có, WITH NORECOVERY.
4. Mọi log backup sau mốc của differential (hoặc sau full nếu không dùng differential), theo đúng thứ tự, không được đứt LSN.
5. Log backup cuối cùng WITH RECOVERY để mở database. Thêm STOPAT khi cần dừng ở một thời điểm.

BACKUP LOG BanHang
TO DISK = N'B:\Backup\BanHang_tail.trn'
WITH NORECOVERY, INIT, CHECKSUM;
GO

RESTORE DATABASE BanHang
FROM DISK = N'B:\Backup\BanHang_full_20261001_2200.bak'
WITH NORECOVERY, REPLACE, CHECKSUM, STATS = 10;
GO

RESTORE DATABASE BanHang
FROM DISK = N'B:\Backup\BanHang_diff_20261002_1200.bak'
WITH NORECOVERY, CHECKSUM, STATS = 10;
GO

RESTORE LOG BanHang
FROM DISK = N'B:\Backup\BanHang_log_20261002_1215.trn'
WITH NORECOVERY, CHECKSUM, STATS = 10;
GO

RESTORE LOG BanHang
FROM DISK = N'B:\Backup\BanHang_tail.trn'
WITH RECOVERY, CHECKSUM, STATS = 10;
GO

REPLACE chỉ nằm ở lệnh full đầu tiên, vì database BanHang vẫn còn trên máy sau tail-log. STOPAT lấy giờ đồng hồ của máy SQL Server, không phải giờ UTC.

Về một thời điểm, log cuối dùng STOPAT và RECOVERY:

RESTORE LOG BanHang
FROM DISK = N'B:\Backup\BanHang_log_20261002_1215.trn'
WITH RECOVERY,
     STOPAT = '2026-10-02T12:07:00',
     CHECKSUM,
     STATS = 10;

NORECOVERY giữ database đóng để còn áp được bản tiếp theo. RECOVERY chạy undo và đưa database online. Gọi RECOVERY quá sớm thì không áp thêm log được nữa. Lúc đó phải restore lại từ full.

Trong SIMPLE, chuỗi dừng ở full cộng differential mới nhất. Không có log backup và không có STOPAT.

Xóa nhầm đơn cũ lúc 12:07 #

Bối cảnh: recovery model FULL. Full lúc 22:00 ngày 2026-10-01. Log backup mỗi 15 phút, bản cuối lúc 11:45 ngày 2026-10-02. Differential chạy từ 12:00 đến 12:05. Lúc 12:07 một phiên chạy:

DELETE FROM dbo.DonHang
WHERE NgayTao < CAST('20260101' AS datetime2(0));

Lệnh xóa toàn bộ đơn trước năm 2026 và đã COMMIT. Người trực phát hiện lúc 12:10, trước kỳ log backup 12:15. Phần xóa đang nằm trong log đang mở trên L:, chưa có trong file .trn nào.

Việc đầu tiên là tail-log, để giữ phần log từ sau bản 11:45 đến 12:10, gồm cả lệnh xóa. NORECOVERY đưa BanHang sang trạng thái restoring.

BACKUP LOG BanHang
TO DISK = N'B:\Backup\BanHang_tail_20261002_1210.trn'
WITH NORECOVERY, INIT, COMPRESSION, CHECKSUM;

Chuỗi áp vào là full, differential 12:05, rồi đúng một file tail-log. Bản tail-log bắt đầu từ mốc sau log 11:45, nên nó phủ cả LSN cuối của differential (khoảng 12:05) lẫn lệnh xóa lúc 12:07. STOPAT dừng trước lệnh xóa. Không áp lại hàng chục file log từ 22:15 đến 11:45: differential đã mang dữ liệu tới 12:05.

RESTORE DATABASE BanHang
FROM DISK = N'B:\Backup\BanHang_full_20261001_2200.bak'
WITH NORECOVERY, REPLACE, CHECKSUM, STATS = 10;
GO

RESTORE DATABASE BanHang
FROM DISK = N'B:\Backup\BanHang_diff_20261002_1200.bak'
WITH NORECOVERY, CHECKSUM, STATS = 10;
GO

RESTORE LOG BanHang
FROM DISK = N'B:\Backup\BanHang_tail_20261002_1210.trn'
WITH RECOVERY,
     STOPAT = '2026-10-02T12:06:00',
     CHECKSUM,
     STATS = 10;
GO

Sau lệnh cuối, đơn 10042 với TongTien = 1750000.00 (đã commit lúc 12:04) còn. Các đơn trước năm 2026 cũng còn. Giao dịch commit sau 12:06:00, gồm lệnh DELETE lúc 12:07, không còn trong database đã mở. STOPAT dùng giờ của máy SQL Server.

Đường dài, nếu không có differential: full 22:00, rồi từng file log từ 22:15 ngày 01/10 đến tail-log, vẫn STOPAT ở 12:06 trên file cuối. Kết quả dữ liệu giống đường ngắn. Số file phải áp nhiều hơn và RTO dài hơn. Đó là lý do lấy differential giữa ngày.

Ba lỗi hay gặp với đúng bộ file này:

Hỏng ổ dữ liệu, ổ log còn #

14:00 cùng ngày, volume E: và G: hỏng. L: vẫn đọc được. Log backup cuối là 13:45. Phần đơn từ 13:45 đến 14:00 chỉ có trên log đang mở.

BACKUP LOG BanHang
TO DISK = N'B:\Backup\BanHang_tail_20261002_1400.trn'
WITH NORECOVERY, INIT, COMPRESSION, CHECKSUM;

Rồi full 22:00, differential mới nhất, mọi log backup sau differential theo đúng giờ, và tail-log cuối cùng với RECOVERY, không STOPAT. Giao dịch đã commit đến sát 14:00 được redo. Phần chưa commit bị undo. Đây là hàng thứ ba trong bảng so sánh recovery model.

Mất cả máy #

Bản backup chỉ còn trên B:, là máy khác. Log backup cuối 13:45, không lấy được tail-log. Restore full, differential, và log đến file 13:45, file cuối WITH RECOVERY. Đơn insert và commit từ 13:45 đến 14:00 không còn ở đâu để lấy lại. Khoảng mất đúng chu kỳ log backup.

Việc cần làm cùng lịch backup #

• Restore thử lên máy khác theo lịch. Bản backup chưa restore thành công thì chưa biết là dùng được.
• RESTORE VERIFYONLY FROM DISK = N'...' WITH CHECKSUM kiểm tra checksum và cấu trúc bản backup, không thay cho một lần restore thật.
• Giữ bản backup trên máy khác và trên một bản offline hoặc bất biến. Đĩa backup nằm cùng máy SQL Server mất cùng lúc với dữ liệu.
• Quyền thư mục backup tách khỏi quyền người dùng ứng dụng. Tài khoản dịch vụ SQL Server cần ghi được nơi backup và đọc được lúc restore.
• Theo dõi lần backup cuối trong msdb.dbo.backupset và cảnh báo khi full hoặc log backup trễ hơn ngưỡng RPO.

SELECT
    database_name,
    type, -- D full, I differential, L log
    backup_start_date,
    backup_finish_date,
    compressed_backup_size / 1024 / 1024 AS compressed_mb
FROM msdb.dbo.backupset
WHERE database_name = N'BanHang'
ORDER BY backup_start_date DESC;

8. Cách các tầng này ăn khớp #

Lấy đúng lần COMMIT lúc 12:04 của đơn 10042, TongTien từ 1.500.000 lên 1.750.000:

1. Dòng 35 byte nằm trên một data page 8 KB trong clustered index (NgayTao, DonHangId). Page đó thuộc partition năm 2026, allocation unit IN_ROW_DATA.
2. Partition năm 2026 nằm trên FG_DATA. Proportional fill đã đặt page này trong E:\SqlData\BanHang_data_01.ndf hoặc G:\SqlData\BanHang_data_02.ndf.
3. Log record được flush xuống L:\SqlLog\BanHang_log.ldf trước khi COMMIT trả về. Lúc này file .ndf có thể chưa đổi.
4. DCM của tệp dữ liệu bật bit của extent chứa page đó. Differential đang chạy từ 12:00 đến 12:05 kết thúc sau commit 12:04, nên lần sửa này nằm trong bản differential đó. Commit sau 12:05 chỉ có trong log, cho đến differential hoặc full kế tiếp.
5. Checkpoint sau đó ghi dirty page xuống .ndf.
6. Log backup 12:15 (hoặc tail-log nếu sự cố đến trước 12:15) copy phần log này ra B: và, nếu không còn giao dịch cũ hơn giữ VLF, cho phép tái sử dụng đoạn log đó.
7. Full backup đêm 2026-10-01 là mốc gốc. Không có file full đó thì differential 12:00 và mọi file .trn không mở được một lần restore hoàn chỉnh.

Thiếu bước 6, tệp log trên L: phình đến khi đầy và đơn hàng mới không commit được. Thiếu bản full trên B:, sự cố mất E: và G: không có mốc để áp log.

9. Những chỗ bản tóm tắt ngắn thường lệch #

Các điểm dưới đây là giới hạn đúng, dùng khi đọc lại ghi chú ngắn hoặc bài giới thiệu.

• Giới hạn dòng trên một page là 8.060 byte. Page vẫn là 8.192 byte.
• Dòng có thể lớn hơn 8.060 byte nhờ row-overflow và LOB. Hai cơ chế này khác nhau: row-overflow dùng con trỏ 24 byte và allocation unit ROW_OVERFLOW_DATA; LOB dùng con trỏ 16 byte và LOB_DATA.
• Tệp log không có page 8 KB và không thuộc filegroup.
• Nhiều tệp log được ghi lần lượt, không song song. Song song theo tỷ lệ chỗ trống là việc của các tệp dữ liệu trong cùng filegroup.
• Từ SQL Server 2016, database người dùng cấp uniform extent theo mặc định.
• Full backup không cắt transaction log. Database FULL cần log backup để log ngừng tăng.
• Differential bám vào full backup gần nhất. Full copy-only không đổi mốc đó.
• WITH FORMAT trên một tệp backup đã có dữ liệu xóa media header và các bản đang nằm trong tệp đó.
• Đặt bảng “nóng” lên filegroup SSD chỉ có tác dụng khi filegroup đó thực sự nằm trên ổ khác với phần còn lại.

10. Khung để thêm engine khác #

Chương lưu trữ của engine tiếp theo nên trả lời cùng bảng này. Cột SQL Server là mốc so sánh.

Khi viết chương PostgreSQL, MySQL, hoặc Oracle, giữ nguyên các hàng của bảng. Điền cơ chế tương ứng của engine đó (ví dụ WAL và checkpoint của PostgreSQL, redo log và tablespace của Oracle, redo log và tablespace của InnoDB). Phần lệnh và giới hạn số liệu để trong chương riêng của từng engine, không trộn vào chương SQL Server này.

Bảng đã điền cột PostgreSQL nằm ở PostgreSQL — Kiến trúc lưu trữ, cùng các mốc 12:04 và 12:07 của chương này.

Đọc tiếp #

• Kỹ thuật thường dùng: nén trang, columnstore, SWITCH partition, ADR, TDE, Query Store và Availability Group gắn với kiến trúc này.
• Kiểu dữ liệu, collation và khóa chính: vì sao dòng DonHang là 35 byte chứ không phải 47, và cách giữ DonHangId duy nhất trên bảng đã phân vùng.
• Chỉ mục, thống kê và cách dữ liệu được tìm: các page ở mục 4 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.
• Transaction, khóa và mức isolation: giao dịch mở từ 09:00 ở mục 5 nhìn từ phía khóa và blocking.

Nguồn #

• Pages and extents architecture guide
• Database files and filegroups
• Write pages in the Database Engine
• The transaction log
• Recovery models
• Full, differential, and transaction log backups
• Maximum capacity specifications