1️⃣ Partition Table là gì?
- Là cách chia dữ liệu 1 bảng lớn thành nhiều phần (partition) dựa theo giá trị cột
- Từ người dùng → vẫn là 1 bảng
- Từ DB engine → chia nhỏ ở storage
→ Hiệu năng tăng vì truy vấn chỉ đọc đúng partition cần thiết
2️⃣ Vì sao cần Partition?
Dùng bullet dễ đọc:
- Bảng log to dần → đọc chậm => Đọc đúng partition theo ngày/tháng
- Xóa dữ liệu cũ rất chậm => Chỉ cần switch/drop partition
- Index quá nặng => Index theo partition → rebuild từng phần
- Backup/Restore lâu => Chỉ backup partition mới
3️⃣ Khi nào nên dùng Partition?
✅ Dữ liệu > 10–20 triệu dòng
✅ Có cột phân loại thời gian (CreatedDate)
✅ Truy vấn theo range: ngày, tháng
⛔ Không nên dùng nếu:
- Bảng nhỏ
- Truy vấn không có điều kiện lọc theo cột partition
4️⃣ Cách hoạt động của Partition trong SQL Server
Giải thích 3 khái niệm:
- Partition Function => Xác định điểm phân chia (range)
- Partition Scheme => Tạo “maps” partition → filegroup
- Partitioned Table => Bảng thực tế lưu dữ liệu
5️⃣ Ví dụ thực tế — Partition theo ngày
Bước 1: xem bảng có partition chưa:
SELECT
t.name AS TableName, i.name AS IndexName, i.index_id,
ps.name AS PartitionScheme, pf.name AS PartitionFunction,
p.partition_number, p.rows
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id <= 1 -- CI/heap
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id = i.index_id
WHERE t.name = 'FlightRequestLog'
ORDER BY p.partition_number;
Các bạn để ý thấy PartitionScheme null là chưa có schema liên quan để bảng này.
Bước 2: (Re)Create Partition Function (RANGE RIGHT) nếu chưa có:
=> tạo biên tới hôm nay là đủ, ngày mai sẽ do proc split thêm.
IF NOT EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'PF_FlightRequestByDay')
BEGIN
DECLARE @d0 date = DATEADD(day, -8, CAST(GETDATE() AS date));
-- seed 30 boundary mỗi ngày từ d0+1 .. today
DECLARE @sql nvarchar(max) = N'CREATE PARTITION FUNCTION PF_FlightRequestByDay (date) AS RANGE RIGHT FOR VALUES (';
;WITH D AS (
SELECT DATEADD(day, 1, @d0) AS d
UNION ALL
SELECT DATEADD(day, 1, d) FROM D WHERE d < CAST(GETDATE() AS date)
)
SELECT @sql += STRING_AGG('''' + CONVERT(nvarchar(10), d, 23) + '''', ',') WITHIN GROUP (ORDER BY d)
FROM D OPTION (MAXRECURSION 0);
SET @sql += N');';
EXEC sp_executesql @sql;
END;
GO
Bước 3: (Re)Create Partition Scheme nếu chưa có :
=> sau này có thể map theo nhiều filegroup để trải I/O.
IF NOT EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = 'PS_FlightRequestByDay')
BEGIN
CREATE PARTITION SCHEME PS_FlightRequestByDay
AS PARTITION PF_FlightRequestByDay
ALL TO ([PRIMARY]); -- tạm thời đặt hết vào PRIMARY
END;
GO
Bước 4: mình sẽ thêm cột PartitionDate để đánh partition theo thằng này:
=> Nên sửa/siết ràng buộc NULL cho CreatedDate để tránh rơi vào partition “lạ”
IF COL_LENGTH('dbo.FlightRequestLog','PartitionDate') IS NULL
BEGIN
ALTER TABLE dbo.FlightRequestLog
ADD PartitionDate AS CAST(CreatedDate AS date) PERSISTED;
END
GO
Bước 5: mình chuyển cluster index về để nhường do partition(Nếu đang là heap hoặc CI khác, drop/rebuild CI):
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.FlightRequestLog') AND name = 'CX_FlightRequestLog')
BEGIN
DROP INDEX CX_FlightRequestLog ON dbo.FlightRequestLog WITH (ONLINE = OFF);
END
Bước 6: tạo cluster index cho cặp partition và id
CREATE UNIQUE CLUSTERED INDEX CX_FlightRequestLog
ON dbo.FlightRequestLog (PartitionDate, Id)
ON PS_FlightRequestByDay (PartitionDate);
GO
Bước 7: Nếu có các index khác thì phải Tạo NCI dạng partitioned (aligned với PF/PS hiện tại):
Trước đó mình có index này:
CREATE NONCLUSTERED INDEX [IX_FlightRequestLog_Date_Code] ON [dbo].[FlightRequestLog]
(
[CreatedDate] DESC,
[SystemCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
Giờ mình sẽ xóa và tạo lại: => PartitionDate phải nằm trong KEY của NCI (không chỉ “ON PS(PartitionDate)”) để aligned 100%
DROP INDEX IF EXISTS IX_FlightRequestLog_Date_Code ON dbo.FlightRequestLog;
CREATE NONCLUSTERED INDEX IX_FlightRequestLog_Date_Code
ON dbo.FlightRequestLog
(
PartitionDate ASC, -- bắt buộc có trong KEY
CreatedDate DESC,
SystemCode ASC
)
ON PS_FlightRequestByDay (PartitionDate);
Bước 8: mình sẽ tạo 1 bảng CI giống bảng chính (không partition ở stage, nhưng key & column order phải identical
- Schema identical tuyệt đối: thứ tự cột, kiểu, nullability, computed (PERSISTED), collation, compression, set options.
- Tạo CI giống hệt
CREATE TABLE [dbo].[FlightRequestLog_Stage](
...
...
[PartitionDate] AS CAST([CreatedDate] AS DATE) PERSISTED
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
Tạo lại cluster index giống bảng chính:
CREATE UNIQUE CLUSTERED INDEX CX_FlightRequestLog_Stage
ON dbo.FlightRequestLog_Stage (PartitionDate, Id);
GO
Giờ bạn kiểm tra object này đáng lẻ phải khác null:
SELECT OBJECT_ID('dbo.FlightRequestLog_Stage') AS StageObjectId;
Bước 10: kiểm tra
- Boundary sau sweep. Ở bước trước đó chúng ta đã tạo partition cho 7 ngày trước và ngày hiện tại tổng cộng là 8 ngày.
SELECT prv.boundary_id, CONVERT(date, prv.value) AS BoundaryDate
FROM sys.partition_functions pf
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE pf.name = 'PF_FlightRequestByDay'
ORDER BY prv.boundary_id;
- Số rows theo partition:(test nha nên hơi ít data hehe)
SELECT p.partition_number, SUM(p.rows) AS rows
FROM sys.partitions p
JOIN sys.tables t ON t.object_id = p.object_id
JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE t.name = 'FlightRequestLog' AND i.index_id = 1
GROUP BY p.partition_number
ORDER BY p.partition_number;
- À còn nếu bạn muốn xem tổng bao nhiêu partition và tổng row thì xem cái query này nha:
SELECT i.index_id,
COUNT(DISTINCT p.partition_number) AS partitions,
SUM(p.rows) AS rows
FROM sys.indexes i
JOIN sys.objects o ON o.object_id=i.object_id AND o.name='FlightRequestLog'
JOIN sys.partitions p ON p.object_id=i.object_id AND p.index_id=i.index_id
WHERE i.index_id IN (0,1)
GROUP BY i.index_id;
Bước 11: bước này quan trọng mình sẽ viết 1 proc để làm nhiệm vụ: truncate partition n ngày trước đó, tạo partition cho ngày mới, merge stage, switch.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create OR ALTER PROC [dbo].[usp_FlightRequestLog_Sweep]
@KeepDays int = 5
AS
BEGIN
SET NOCOUNT ON;
DECLARE @today date = CAST(GETDATE() AS date);
DECLARE @tomorrow date = DATEADD(day, 1, @today);
DECLARE @dropDay date = DATEADD(day, -(@KeepDays + 1), @today);
DECLARE @dayAfterDrop date = DATEADD(day, 1, @dropDay);
/* 1) Đảm bảo có biên cho NGÀY MAI (để nhận dữ liệu mới) */
IF NOT EXISTS (
SELECT 1
FROM sys.partition_functions pf
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE pf.name = 'PF_FlightRequestByDay'
AND CONVERT(date, prv.value) = @tomorrow
)
ALTER PARTITION FUNCTION PF_FlightRequestByDay()
SPLIT RANGE (@tomorrow);
/* 1b) Đảm bảo có biên cho @dropDay+1 (rất quan trọng cho SWITCH của @dropDay) */
IF NOT EXISTS (
SELECT 1
FROM sys.partition_functions pf
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE pf.name = 'PF_FlightRequestByDay'
AND CONVERT(date, prv.value) = @dayAfterDrop
)
ALTER PARTITION FUNCTION PF_FlightRequestByDay()
SPLIT RANGE (@dayAfterDrop);
/* 2) SWITCH OUT partition @dropDay -> _Stage */
DECLARE @part int = $PARTITION.PF_FlightRequestByDay(@dropDay);
-- drop CHECK cũ, tạo CHECK mới theo khoảng [@dropDay, @dropDay+1)
IF EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_FlightRequestLog_Stage_Day')
ALTER TABLE dbo.FlightRequestLog_Stage DROP CONSTRAINT CK_FlightRequestLog_Stage_Day;
DECLARE @d0 nvarchar(10) = CONVERT(nvarchar(10), @dropDay, 23); -- 'YYYY-MM-DD'
DECLARE @d1 nvarchar(10) = CONVERT(nvarchar(10), @dayAfterDrop, 23); -- 'YYYY-MM-DD'
DECLARE @sql nvarchar(max) =
N'ALTER TABLE dbo.FlightRequestLog_Stage WITH CHECK '+
N'ADD CONSTRAINT CK_FlightRequestLog_Stage_Day '+
N'CHECK (PartitionDate >= '''+@d0+N''' AND PartitionDate < '''+@d1+N''');';
EXEC sp_executesql @sql;
-- SWITCH (nguồn/đích phải có index & schema align)
ALTER TABLE dbo.FlightRequestLog
SWITCH PARTITION @part
TO dbo.FlightRequestLog_Stage;
/* 3) Xoá nhanh + gộp biên cũ */
TRUNCATE TABLE dbo.FlightRequestLog_Stage;
ALTER PARTITION FUNCTION PF_FlightRequestByDay()
MERGE RANGE (@dropDay);
END
Bạn có thể bỏ câu lệnh này lên rồi chạy thôi:
EXEC dbo.usp_FlightRequestLog_Sweep @KeepDays = 5;
Bước 12: Ủa bạn nghĩ xong rồi à? Chưa đâu, mình cũng k rảnh để ngày nào cũng vào chạy => ok tạo job hàng ngày thôi let's go.(vào sql agent tạo nha)
Bước 13: Kiểm chứng hiệu năng và giám sát định kỳ
Sau khi bật partition, phải chứng minh rằng SQL Server đã dùng Partition Elimination → truy vấn chỉ quét đúng partition.
Bạn kiểm tra bằng:
SET STATISTICS IO ON;
SELECT COUNT(*)
FROM dbo.FlightRequestLog
WHERE PartitionDate = CAST(GETDATE() AS date);
SET STATISTICS IO OFF;
Kết quả chuẩn:
- Logical Reads giảm rất mạnh ✅
- Không quét toàn bảng ❌
Bước 14: Checklist vận hành lâu dài:
- Partition luôn có partition trống bên phải? => Có job Split ngày mai ✅
- Stage luôn empty trước SWITCH? => TRUNCATE mỗi lần chạy ✅
- Statistics tối ưu? => UPDATE STATISTICS hàng tuần
- I/O filegroup tối ưu? => Cân nhắc phân FG theo tháng/quý
- Log growth an toàn? => Giám sát khi SWITCH/MERGE tạo log nhiều
- Backup/Restore partition cũ? => Có thể archive FG cũ để tiết kiệm storage
Vậy là ok rồi đó. Chúc bạn thành công nha
✅ Phần Kết: Tóm lược “Chuẩn Partition thực chiến”
Partition đúng =
🔹 đúng cột (date)
🔹 đúng cấu trúc CI/NCI (aligned)
🔹 đúng thứ tự SPLIT → SWITCH → TRUNCATE → MERGE
🔹 đúng automation (Agent Job)
🔹 có giám sát & kiểm chứng
Nếu làm đủ →
✅ Hệ thống luôn gọn nhẹ & nhanh
✅ Quản lý log cũ như “xoá file” (TRUNCATE partition)
✅ Tăng trưởng data không còn là nỗi lo 🚀